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 | |
+----+-------------+-------------------+--------+-----------------------------------------------------------+------------------+---------+------------------------------------------------+--------+-----------------------------+
