A couple of weeks ago I upgraded MySQL from 5.0.51a to 5.1.52 on my CentOS x86 dedicated database server and since then I have been having intermittent problems whereby hundreds of wildcard search queries on my downloads table begin hanging in the "sending data" phase.
The issue appears to start at random and when it happens my server load just climbs and climbs until I intervene by stopping my web server (Litespeed) and letting the processlist clear itself up. It is happening about 6 times per day on average usually during a ~5 hour window (our quietest period and when some stats are running).
I do not know if it's related, but every time the issue begins happening again (typically after about 19 hours of no problems), stopping my web server will not always get things going and in the hope/belief it will help, I optimize all my tables and every one with overhead goes through a "repair by sorting". Surely it shouldn't have to repair every day!?
mysql> SHOW CREATE TABLE downloads_cache\G -- engine, indexes
*************************** 1. row ***************************
Table: downloads_cache
Create Table: CREATE TABLE `downloads_cache` (
`download_id` int(11) NOT NULL AUTO_INCREMENT,
`name_subcat` varchar(255) NOT NULL DEFAULT '',
`name_title` varchar(255) NOT NULL DEFAULT '',
`package` varchar(150) NOT NULL,
`tags` varchar(255) NOT NULL DEFAULT '',
`channel_id` int(11) NOT NULL DEFAULT '0',
`category_id` int(11) NOT NULL DEFAULT '0',
`catother_ids` varchar(100) NOT NULL,
`folder_id` int(11) NOT NULL DEFAULT '0',
`credit_cost` int(11) NOT NULL DEFAULT '1',
`file_format` varchar(5) NOT NULL DEFAULT '',
`file_format_2` varchar(5) NOT NULL DEFAULT '',
`file_format_3` varchar(5) NOT NULL DEFAULT '',
`file_size` int(11) NOT NULL DEFAULT '0',
`file_isanimated` tinyint(1) NOT NULL DEFAULT '0',
`file_version` varchar(20) NOT NULL DEFAULT '',
`file_fversion` tinyint(1) unsigned NOT NULL,
`file_dimensions` varchar(10) NOT NULL DEFAULT '',
`file_length` varchar(5) NOT NULL DEFAULT '',
`file_bitrate` varchar(6) NOT NULL DEFAULT '',
`pf_minver` tinyint(2) NOT NULL DEFAULT '0',
`pf_maxver` tinyint(2) NOT NULL DEFAULT '0',
`pf_targetver` tinyint(2) NOT NULL DEFAULT '0',
`is_creation` tinyint(4) NOT NULL DEFAULT '0',
`is_flash` tinyint(1) unsigned NOT NULL DEFAULT '0',
`preview_count` int(11) NOT NULL DEFAULT '0',
`preview_format` varchar(5) NOT NULL DEFAULT '',
`info_description` text NOT NULL,
`info_date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`info_date_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`info_date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`publisher` varchar(100) NOT NULL,
`info_submittedby_name` varchar(50) NOT NULL DEFAULT '',
`info_submittedby_id` int(11) NOT NULL DEFAULT '0',
`info_updatedby_name` varchar(50) NOT NULL,
`info_updatedby_id` int(10) unsigned NOT NULL DEFAULT '0',
`info_modifiedby_id` int(10) unsigned NOT NULL DEFAULT '0',
`info_authorisedby_id` int(11) NOT NULL DEFAULT '0',
`info_downloads_web` int(11) NOT NULL DEFAULT '0',
`info_downloads_wap` int(11) NOT NULL DEFAULT '0',
`info_downloads_today` int(11) NOT NULL DEFAULT '0',
`info_downloads_week` int(10) unsigned NOT NULL DEFAULT '0',
`info_downloads_30days` int(11) NOT NULL DEFAULT '0',
`info_downloads_f2` int(11) NOT NULL DEFAULT '0',
`info_downloads_thisver` int(10) unsigned NOT NULL DEFAULT '0',
`info_last_download_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`info_last_download_referrer` varchar(150) NOT NULL DEFAULT '',
`info_last_download_ip` varchar(14) NOT NULL DEFAULT '',
`info_last_download_host` varchar(255) NOT NULL DEFAULT '',
`info_last_download_agent` mediumtext NOT NULL,
`info_isauthor` tinyint(1) NOT NULL DEFAULT '0',
`info_exclusiveitem` tinyint(1) NOT NULL DEFAULT '0',
`info_matureitem` tinyint(1) NOT NULL DEFAULT '0',
`info_chart` int(11) NOT NULL DEFAULT '0',
`info_awards` varchar(255) NOT NULL DEFAULT '',
`vote_rate_1` int(11) NOT NULL DEFAULT '0',
`vote_rate_2` int(11) NOT NULL DEFAULT '0',
`vote_rate_3` int(11) NOT NULL DEFAULT '0',
`vote_rate_4` int(11) NOT NULL DEFAULT '0',
`vote_rate_5` int(11) NOT NULL DEFAULT '0',
`vote_rate_total` int(11) NOT NULL DEFAULT '0',
`vote_total` int(11) NOT NULL DEFAULT '0',
`cache_vote_average` decimal(10,2) NOT NULL DEFAULT '0.00',
`keycode` varchar(10) NOT NULL DEFAULT '',
`keycode_s` varchar(10) NOT NULL DEFAULT '',
`oldid` int(11) NOT NULL DEFAULT '0',
`d_notes` varchar(255) NOT NULL DEFAULT '',
`d_status` tinyint(2) NOT NULL DEFAULT '0',
PRIMARY KEY (`download_id`),
KEY `name_subcat` (`name_subcat`),
KEY `name_title` (`name_title`),
KEY `category_id` (`category_id`),
KEY `subcategory_id` (`folder_id`),
KEY `keycode` (`keycode`),
KEY `keycode_s` (`keycode_s`),
KEY `d_status` (`d_status`),
KEY `oldid` (`oldid`),
KEY `tags` (`tags`),
KEY `file_dimensions` (`file_dimensions`),
KEY `info_submittedby_name` (`info_submittedby_name`),
KEY `is_flash` (`is_flash`),
KEY `file_fversion` (`file_fversion`),
KEY `package` (`package`)
) ENGINE=MyISAM AUTO_INCREMENT=484844 DEFAULT CHARSET=latin1
mysql> SHOW TABLE STATUS LIKE 'downloads_cache'\G -- sizes;
*************************** 1. row ***************************
Name: downloads_cache
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 484337
Avg_row_length: 292
Data_length: 141456172
Max_data_length: 281474976710655
Index_length: 64366592
Data_free: 0
Auto_increment: 484844
Create_time: 2011-03-06 01:08:48
Update_time: 2011-03-06 01:09:04
Check_time: 2011-03-06 01:10:02
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Query... (all are different and not always searching '%funny%')
Quote
mysql> EXPLAIN SELECT SQL_CACHE download_id, name_subcat, name_title, category_id, info_submittedby_name, info_matureitem, cache_vote_average, keycode FROM downloads_cache WHERE d_status=1 AND (category_id = '3') AND (replace(name_subcat,'''','') LIKE '%funny%' OR replace(name_title,'''','') LIKE '%funny%') ORDER BY download_id DESC LIMIT 0,24\G -- clues of inefficiencies
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: downloads_cache
type: index
possible_keys: category_id,d_status
key: PRIMARY
key_len: 4
ref: NULL
rows: 177
Extra: Using where
1 row in set (0.02 sec)
Quote
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| bulk_insert_buffer_size | 8388608 |
| join_buffer_size | 1048576 |
| key_buffer_size | 402653184 |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 4194304 |
| read_rnd_buffer_size | 786432 |
| sort_buffer_size | 4194304 |
| sql_buffer_result | OFF |
+-------------------------+-----------+
10 rows in set (0.04 sec)
Server has 4GB RAM
Just a FYI, downloads_cache is a copy of the main downloads table to make the query cache more effective. I automatically recreate the table twice daily (or at the moment, manually, once per day for fear of this problem re-occurring when I'm not monitoring).
I'd be very grateful for your help.
Edited 1 time(s). Last edit at 03/05/2011 11:10PM by Mark P.