Very slow queries on new server

[Ian L]

I just purchased a new (to me) server with dual 3.06Ghz XEON processors and 6GB of RAM. I migrated my database from my shared web host to the new server hoping for a performance boost. However, the performance hasn't been any better and is worse in some cases. I increased the key_buffer to 1600M but it doesn't look like anything close to that is actually being utilized. What am I doing wrong?

mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------+------------+
| Variable_name | Value |
+-------------------------------+------------+
| bdb_log_buffer_size | 262144 |
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 1572864000 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
+-------------------------------+------------+
13 rows in set (0.00 sec)

Here is one of my slow queries. It took 1 min 32.90 sec the last time I ran it:

mysql> EXPLAIN SELECT * FROM `ips` LEFT JOIN `title_keys` ON ips.title_key = title_keys.id WHERE (title_keys.title LIKE '%test3%' OR ips.title LIKE '%test3%' OR ips.domain LIKE '%test3%') ORDER BY ips.time DESC LIMIT 0,21;
+----+-------------+------------+--------+---------------+------+---------+----------------------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+----------------------------------+---------+-------------+
| 1 | SIMPLE | ips | index | NULL | time | 4 | NULL | 9568100 | |
| 1 | SIMPLE | title_keys | eq_ref | id | id | 4 | mydropsp_webspider.ips.title_key | 1 | Using where |
+----+-------------+------------+--------+---------------+------+---------+----------------------------------+---------+-------------+
2 rows in set (0.00 sec)

Here are the tables:

mysql> SHOW CREATE TABLE ips;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ips | CREATE TABLE `ips` (
`id` int(11) NOT NULL auto_increment,
`ip` bigint(20) NOT NULL default '0',
`domain` varchar(100) collate utf8_unicode_ci NOT NULL default '',
`title` varchar(250) collate utf8_unicode_ci NOT NULL default '',
`time` int(11) NOT NULL default '0',
`title_key` int(2) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `ip` (`ip`),
KEY `time` (`time`),
KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=9730020 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE title_keys;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| title_keys | CREATE TABLE `title_keys` (
`id` int(4) NOT NULL auto_increment,
`title` varchar(250) collate utf8_unicode_ci NOT NULL default '',
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

It looks like my cached query Key_reads/Key_read_requests ratio is .0038% so I don't think that's the issue:

mysql> show status like 'key%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 1241955 |
| Key_blocks_used | 120758 |
| Key_read_requests | 31404875 |
| Key_reads | 120758 |
| Key_write_requests | 1 |
| Key_writes | 1 |
+------------------------+----------+
7 rows in set (0.00 sec)



Edited 2 time(s). Last edit at 09/14/2010 01:44PM by Ian L.