How to get MySQL to use available system resources or find a "real problem"?

Question

This is a MySQL 5.0.26 server running on SuSE Enterprise 10. This may be a Serverfault issue.

The web user interface that uses these specific requests (below) sometimes shows 30+ , even up to 120+ seconds , to generate pages,

During development, when queries are executed alone, they take up to 20 seconds at the first start (without a query cache), but anywhere from 2 to 7 seconds after that - I assume that due to the fact that the involved tables and indexes were placed in a ram.

From what I can tell, the longest load time is caused by Read / Update Lock . These are MyISAM tables. So it looks like a long update is coming, followed by 7-second steam requests, and they just add up. And I'm fine with this explanation.

What I don't like is that MySQL doesn't seem to be using hardware on , and although the bottleneck is the database, I can't understand why.

I would say, “add more hardware to it,” but we did it and did not seem to change the situation. Looking at the "top" in the slowest times never shows a significant CPU or memory load with mysqld , as if the server has no problems at all - but then why are the requests taking so long?

How can I get MySQL to use shit from this equipment or find out what I'm doing wrong?

Additional information:

On the "Memory Memory" tab in the "MySQL Administrator" (for Windows), the buffer buffer is used less than 1 / 8th, so all indexes must be in RAM. I can provide a screen shot of any graphs that might help.

So desperate to fix this problem. Suffice it to say that outdated code "generates" these requests, and they are pretty much stuck as they are. I have tried every combination of indexes in the respective tables, but any suggestions are welcome.

Here's the current Create Table statement from development (the “experimental” key I added seems to help a little, just for an example query):

 CREATE TABLE `registration_task` ( `id` varchar(36) NOT NULL default '', `date_entered` datetime NOT NULL default '0000-00-00 00:00:00', `date_modified` datetime NOT NULL default '0000-00-00 00:00:00', `assigned_user_id` varchar(36) default NULL, `modified_user_id` varchar(36) default NULL, `created_by` varchar(36) default NULL, `name` varchar(80) NOT NULL default '', `status` varchar(255) default NULL, `date_due` date default NULL, `time_due` time default NULL, `date_start` date default NULL, `time_start` time default NULL, `parent_id` varchar(36) NOT NULL default '', `priority` varchar(255) NOT NULL default '9', `description` text, `order_number` int(11) default '1', `task_number` int(11) default NULL, `depends_on_id` varchar(36) default NULL, `milestone_flag` varchar(255) default NULL, `estimated_effort` int(11) default NULL, `actual_effort` int(11) default NULL, `utilization` int(11) default '100', `percent_complete` int(11) default '0', `deleted` tinyint(1) NOT NULL default '0', `wf_task_id` varchar(36) default '0', `reg_field` varchar(8) default '', `date_offset` int(11) default '0', `date_source` varchar(10) default '', `date_completed` date default '0000-00-00', `completed_id` varchar(36) default NULL, `original_name` varchar(80) default NULL, PRIMARY KEY (`id`), KEY `idx_reg_task_p` (`deleted`,`parent_id`), KEY `By_Assignee` (`assigned_user_id`,`deleted`), KEY `status_assignee` (`status`,`deleted`), KEY `experimental` (`deleted`,`status`,`assigned_user_id`,`parent_id`,`date_due`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 

And one of such ridiculous questions:

 SELECT users.user_name assigned_user_name, registration.FIELD001 parent_name, registration_task.status status, registration_task.date_modified date_modified, registration_task.date_due date_due, registration.FIELD240 assigned_wf, if(LENGTH(registration_task.description)>0,1,0) has_description, registration_task.* FROM registration_task LEFT JOIN users ON registration_task.assigned_user_id=users.id LEFT JOIN registration ON registration_task.parent_id=registration.id where (registration_task.status != 'Completed' AND registration.FIELD001 LIKE '%' AND registration_task.name LIKE '%' AND registration.FIELD060 LIKE 'GN001472%') AND registration_task.deleted=0 ORDER BY date_due asc LIMIT 0,20; 

my.cnf - '[mysqld]'.

 [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 100M table_cache = 2048 sort_buffer_size = 2M net_buffer_length = 100M read_buffer_size = 2M read_rnd_buffer_size = 160M myisam_sort_buffer_size = 128M query_cache_size = 16M query_cache_limit = 1M 

EXPLAIN over a query without an additional index

:

       
 + ---- + ------------- + ------------------- + -------- + - ------------------------------- + ---------------- + - -------- + ----------------------------------------- ------- + --------- + ----------------------------- +
 |  id |  select_type |  table |  type |  possible_keys |  key |  key_len |  ref |  rows |  Extra |
 + ---- + ------------- + ------------------- + -------- + - ------------------------------- + ---------------- + - -------- + ----------------------------------------- ------- + --------- + ----------------------------- +
 |  1 |  SIMPLE |  registration_task |  ref |  idx_reg_task_p, status_assignee |  idx_reg_task_p |  1 |  const |  1067354 |  Using where;  Using filesort |
 |  1 |  SIMPLE |  registration |  eq_ref |  PRIMARY, gbl |  PRIMARY |  8 |  sugarcrm401.registration_task.parent_id |  1 |  Using where |
 |  1 |  SIMPLE |  users |  ref |  PRIMARY |  PRIMARY |  38 |  sugarcrm401.registration_task.assigned_user_id |  1 |  |
 + ---- + ------------- + ------------------- + -------- + - ------------------------------- + ---------------- + - -------- + ----------------------------------------- ------- + --------- + ----------------------------- + 

EXPLAIN above the query, with an "experimental" index:

       
 + ---- + ------------- + ------------------- + -------- + - -------------------------------------------------- -------- + ------------------ + --------- + ------------ ------------------------------------ + -------- + ---- ------------------------- +
 |  id |  select_type |  table |  type |  possible_keys |  key |  key_len |  ref |  rows |  Extra |
 + ---- + ------------- + ------------------- + -------- + - -------------------------------------------------- -------- + ------------------ + --------- + ------------ ------------------------------------ + -------- + ---- ------------------------- +
 |  1 |  SIMPLE |  registration_task |  range |  idx_reg_task_p, status_assignee, NewIndex1, tcg_experimental |  tcg_experimental |  259 |  NULL |  103345 |  Using where;  Using filesort |
 |  1 |  SIMPLE |  registration |  eq_ref |  PRIMARY, gbl |  PRIMARY |  8 |  sugarcrm401.registration_task.parent_id |  1 |  Using where |
 |  1 |  SIMPLE |  users |  ref |  PRIMARY |  PRIMARY |  38 |  sugarcrm401.registration_task.assigned_user_id |  1 |  |
 + ---- + ------------- + ------------------- + -------- + - -------------------------------------------------- -------- + ------------------ + --------- + ------------ ------------------------------------ + -------- + ---- ------------------------- +

SHOW INDICES FROM registration_task;

 mysql> SHOW INDEXES FROM registration_task;
 + ------------------- + ------------ + ---------------- - + -------------- + ------------------ + ----------- + - ------------ + ---------- + -------- + ------ + ---------- - + --------- +
 |  Table |  Non_unique |  Key_name |  Seq_in_index |  Column_name |  Collation |  Cardinality |  Sub_part |  Packed |  Null |  Index_type |  Comment |
 + ------------------- + ------------ + ---------------- - + -------------- + ------------------ + ----------- + - ------------ + ---------- + -------- + ------ + ---------- - + --------- +
 |  registration_task |  0 |  PRIMARY |  1 |  id |  A |  1445612 |  NULL |  NULL |  |  BTREE |  |
 |  registration_task |  1 |  idx_reg_task_p |  1 |  deleted |  A |  2 |  NULL |  NULL |  |  BTREE |  |
 |  registration_task |  1 |  idx_reg_task_p |  2 |  parent_id |  A |  57824 |  NULL |  NULL |  |  BTREE |  |
 |  registration_task |  1 |  By_Assignee |  1 |  assigned_user_id |  A |  5295 |  NULL |  NULL |  YES |  BTREE |  |
 |  registration_task |  1 |  By_Assignee |  2 |  deleted |  A |  5334 |  NULL |  NULL |  |  BTREE |  |
 |  registration_task |  1 |  status_assignee |  1 |  status |  A |  18 |  NULL |  NULL |  YES |  BTREE |  |
 |  registration_task |  1 |  status_assignee |  2 |  deleted |  A |  23 |  NULL |  NULL |  |  BTREE |  |
 |  registration_task |  1 |  NewIndex1 |  1 |  deleted |  A |  2 |  NULL |  NULL |  |  BTREE |  |
 |  registration_task |  1 |  NewIndex1 |  2 |  assigned_user_id |  A |  5334 |  NULL |  NULL |  YES |  BTREE |  |
 |  registration_task |  1 |  NewIndex1 |  3 |  parent_id |  A |  180701 |  NULL |  NULL |  |  BTREE |  |
 |  registration_task |  1 |  tcg_experimental |  1 |  date_due |  A |  1919 |  NULL |  NULL |  YES |  BTREE |  |
 |  registration_task |  1 |  tcg_experimental |  2 |  deleted |  A |  3191 |  NULL |  NULL |  |  BTREE |  |
 |  registration_task |  1 |  tcg_experimental |  3 |  status |  A |  8503 |  NULL |  NULL |  YES |  BTREE |  |
 |  registration_task |  1 |  tcg_experimental |  4 |  assigned_user_id |  A |  53541 |  NULL |  NULL |  YES |  BTREE |  |
 |  registration_task |  1 |  tcg_experimental |  5 |  parent_id |  A |  722806 |  NULL |  NULL |  |  BTREE |  |
 + ------------------- + ------------ + ---------------- - + -------------- + ------------------ + ----------- + - ------------ + ---------- + -------- + ------ + ---------- - + --------- +

 15 rows in set (0.00 sec)

Decision?

I think that I may have solved the problem, which some seem so embarrassing explicit, but still missed: the definition of registration.id , is:

 `id` bigint(20) unsigned NOT NULL auto_increment 

So far, registration_task.parent_id (FK before registration.id ) has been:

 `parent_id` varchar(36) NOT NULL 

Modifying this with:

 alter table `sugarcrm401`.`registration_task` change `parent_id` `parent_id` bigint(20) UNSIGNED NOT NULL; 

... forces EXPLAIN to show only checked lines 25 , where before 651,903 and 103,345 , with crazy indexing.

If I placed the table definition of the registration table, I am sure that someone could notice this. I am going to check this out and post a follow-up post after the weekend.

Answer 1

The answer to most of the problems was posted in my edit:

Definition of registration.id :

 `id` bigint(20) unsigned NOT NULL auto_increment 

So far, registration_task.parent_id (from FK to registration.id ) has been:

 `parent_id` varchar(36) NOT NULL 

Modifying this with:

 alter table `sugarcrm401`.`registration_task` change `parent_id` `parent_id` bigint(20) UNSIGNED NOT NULL; 

... forces EXPLAIN to show only checked lines 25 , where before 651,903 and 103,345 , with crazy indexing.

If I posted a table definition for the registration table, I am sure that someone could notice it.

---

This, however, did not solve the problem completely. I am sure that I will leave some details, but this project is much behind me at this stage. Thanks so much for all your time and answers!

Answer 2

When you have a request that says! = It will invariably be slow. And note that the index is not used in this part of the query, even if the status field has two different indexes!

You do not want the varchar (255) field to be fully indexed and having it as part of two different keys will make your updates very slow. With a total of five indexes, we're just going to add to the mess. If you make any choice at the very moment when the update is taking place, it really will take a lot of time, as you have already seen.

What you can do is index only a small section of your 255 characters. Even better, you can use an integer (statusCode) instead of the status here. This will greatly speed up the process.

More memory or more CPUs will not help here. Having an extra hard drive gives you a 20 - 30% speed boost. But you can make the same query complete in less than a second by simply rebuilding your indexes.

Answer 3

You should be able to optimize this with appropriate indexes. As FractalizeR said, you need indexes for join columns and columns in the Where statement. Just adding them all to the same index will not solve your problem. What indexes do you have when registering?

Answer 4

Please post SHOW INDEXES results from your tables. The general recommendation that I can give now is the following:

• Add separate indexes for registration_task: assign_user_id, parent_id, date_due
• Add index to registration.FIELD060
• Removing the query part "AND registration.FIELD001 LIKE '%' AND registration_task.name LIKE '%'" is useless. % means any number of characters

After that, send the message EXPLAIN again.