How to make MySQL utilize available system resources, or find "the real problem"?

Question

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

The web user interface that uses these particular queries (below) is showing sometimes 30+, even up to 120+ seconds at the worst, to generate the pages involved.

On development, when the queries are run alone, they take up to 20 seconds on the first run (with no query cache enabled) but anywhere from 2 to 7 seconds after that - I assume because the tables and indexes involved have been placed into ram.

From what I can tell, the longest load times are caused by Read/Update Locking. These are MyISAM tables. So it looks like a long update comes in, followed by a couple 7 second queries, and they're just adding up. And I'm fine with that explanation.

What I'm not fine with is that MySQL doesn't appear to be utilizing the hardware it's on, and while the bottleneck seems to be the database, I can't understand why.

I would say "throw more hardware at it", but we did and it doesn't appear to have changed the situation. Viewing a 'top' during the slowest times never shows much cpu or memory utilization by mysqld, as if the server is having no trouble at all - but then, why are the queries taking so long?

How can I make MySQL use the crap out of this hardware, or find out what I'm doing wrong?

Extra Details:

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

So desperate to fix this issue. Suffice it to say, there is legacy code "generating" these queries, and they're pretty much stuck the way they are. I have tried every combination of Indexes on the tables involved, but any suggestions are welcome.

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

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 the ridiculous queries in question:

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]' section.

[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 above query, without 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 query, with '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 INDEXES 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)

Solution?

I think I may have solved the problem, that to some will seem so embarrassingly obvious, but was somehow overlooked until now: The definition of registration.id, is:

`id` bigint(20) unsigned NOT NULL auto_increment

While the registration_task.parent_id (FK to registration.id) was:

`parent_id` varchar(36) NOT NULL

Changing this via:

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

... causes the EXPLAIN to show only 25 rows examined, where it was earlier 651,903, and 103,345 at it's best when forcing crazy indexing.

Had I posted the table definition of the registration table, I'm sure someone might have spotted it. I'm going to verify this and post followup after the weekend.

Answer 1

Please post SHOW INDEXES result from your tables. Generic recommendation I can give just now is to:

• Add separate indexes on registration_task fields: assigned_user_id, parent_id, date_due
• Add index on registration.FIELD060
• Remove part of the query "AND registration.FIELD001 LIKE '%' AND registration_task.name LIKE '%'" - it is useless. % means any number of any chars

After that please post EXPLAIN again.

Answer 2

When you have a query that says != it's invariably going to be slow. And note that the index is not being used on that part of the query even though the status field is on two different indexes!

You don't want to have a varchar(255) field fully indexed and having it as part of two different keys is going to make your updates very very slow. Having a total of five indexes is just going to add to the mess. If you are doing any select at the same moment that an update is happening, it's really going to take a long time as you have already seen.

What you might want to do is to index only a small section of your 255 character field. Better still, you might want use an integer (statusCode) instead of status here. That will speed things up a great deal.

Having more memory or more CPU is not going to help here. Having an extra hard drive give you a 20 - 30% speed boost. But you can make the same query complete in less than a second by just reorganizing your indexes.

Answer 3

You should be able to optimize this with proper Indexes. As FractalizeR said, you need indexes on join columns, and columns in your Where statement. Just adding them all to one Index will not solve your problem. What indexes do you have on Registration?