Menu
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?
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;
[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
+----+-------------+-------------------+--------+--------------------------------+----------------+---------+------------------------------------------------+---------+-----------------------------+ | 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 | | +----+-------------+-------------------+--------+--------------------------------+----------------+---------+------------------------------------------------+---------+-----------------------------+
+----+-------------+-------------------+--------+-----------------------------------------------------------+------------------+---------+------------------------------------------------+--------+-----------------------------+ | 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 | | +----+-------------+-------------------+--------+-----------------------------------------------------------+------------------+---------+------------------------------------------------+--------+-----------------------------+
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)
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.
856
Create the MEMORY database and recreate the tables you'll be using with this syntax: CREATE TABLE tablename (...) ENGINE = MEMORY; . You can then import your data using LOAD DATA INFILE 'table_filename' INTO TABLE tablename for each table.
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.
103
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?
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
