MySQL Forums
Forum List  »  Performance

Re: MySQL 5.5.31 cripplingly slow
Posted by: Mohammad Chang
Date: January 18, 2014 09:53AM

Wow, thanks so much! What an educational experience this has been. I modified my key_buffer_size according to the results of that (the innodb_buffer_pool_size was good as I had it). I apologize if this is easily answered through a google search, but why don't you want the key_buffer_size to be bigger than a certain value? For instance, 20% of my RAM is larger than the value I obtained through your new method: what's the downside of setting key_buffer_size too high?

There's only been one slow query logged. It should only be executed once per iteration of the long script, and it still doesn't take too long so I'm not particularly worried about it, but if you care to take a look, here's the outputs you requested:

# Query_time: 1.157580 Lock_time: 0.000151 Rows_sent: 1447 Rows_examined: 5788
SELECT
craigusers.id, craigusers.email, craigusers.url, maxprice, craigusers.location, `date`, description, craigusers.userid, pid, empty,
cusers.email AS userEmail,
preferences.from
FROM
craigusers
JOIN
cusers
ON
craigusers.userid = cusers.id
JOIN
preferences
ON
craigusers.userid = preferences.userid
WHERE
`delete`=0
ORDER BY
craigusers.id;

------------------------------------------------------------------------

SHOW CREATE TABLE `craigusers`;
CREATE TABLE `craigusers` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`email` varchar(50) CHARACTER SET latin1 NOT NULL,
`url` varchar(250) CHARACTER SET latin1 NOT NULL,
`maxprice` varchar(7) CHARACTER SET latin1 NOT NULL DEFAULT 'none',
`location` varchar(50) CHARACTER SET latin1 NOT NULL DEFAULT 'any',
`date` int(10) NOT NULL,
`description` varchar(250) CHARACTER SET latin1 NOT NULL,
`pid` int(10) unsigned DEFAULT NULL,
`userid` int(11) NOT NULL DEFAULT '37',
`delete` tinyint(3) unsigned NOT NULL,
`replyto` varchar(320) DEFAULT NULL,
`added` datetime NOT NULL,
`updated` bigint(20) unsigned DEFAULT NULL,
`empty` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `userid` (`userid`),
KEY `delete` (`delete`),
KEY `updated` (`updated`),
KEY `pid` (`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=15135 DEFAULT CHARSET=utf8

SHOW CREATE TABLE `cusers`;
CREATE TABLE `cusers` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(64) NOT NULL,
`salt` varchar(512) NOT NULL,
`password` varchar(512) NOT NULL,
`email` varchar(512) NOT NULL,
`access` tinyint(2) unsigned NOT NULL,
`created` datetime NOT NULL,
`code` char(36) DEFAULT NULL,
`pro` tinyint(3) unsigned NOT NULL DEFAULT '0',
`deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `pro` (`pro`),
KEY `deleted` (`deleted`)
) ENGINE=MyISAM AUTO_INCREMENT=3102 DEFAULT CHARSET=utf8

SHOW CREATE TABLE `preferences`;
CREATE TABLE `preferences` (
`userid` int(11) unsigned NOT NULL COMMENT 'id from cusers',
`url` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'display whole url',
`email` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'display watching email',
`price` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'display max price',
`location` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'display location',
`placeid` int(11) unsigned NOT NULL COMMENT 'base url from places',
`from` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'mail from posting',
`sortColumn` varchar(8) NOT NULL,
`sortOrder` varchar(4) NOT NULL DEFAULT 'asc',
`enginealerts` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT 'Alert user about engine status',
PRIMARY KEY (`userid`),
KEY `enginealerts` (`enginealerts`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='User preferences for craigwatch'


------------------------------------------------------------------------


SHOW TABLE STATUS LIKE 'craigusers';
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| craigusers | MyISAM | 10 | Dynamic | 2874 | 202 | 580968 | 281474976710655 | 157696 | 332 | 15135 | 2014-01-10 23:50:09 | 2014-01-18 09:44:01 | 2014-01-14 22:30:12 | utf8_general_ci | NULL | | |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.70 sec)

SHOW TABLE STATUS LIKE 'cusers';
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| cusers | MyISAM | 10 | Dynamic | 3007 | 237 | 715300 | 281474976710655 | 77824 | 0 | 3102 | 2013-11-10 06:37:25 | 2014-01-17 19:52:33 | 2014-01-14 22:30:12 | utf8_general_ci | NULL | | |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

SHOW TABLE STATUS LIKE 'preferences';
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------------------------------+
| preferences | MyISAM | 10 | Dynamic | 3042 | 20 | 60924 | 281474976710655 | 56320 | 0 | NULL | 2013-10-17 06:48:54 | 2014-01-18 08:48:16 | 2014-01-14 22:30:13 | utf8_general_ci | NULL | | User preferences for craigwatch |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------------------------------+
1 row in set (0.00 sec)



------------------------------------------------------------------------


EXPLAIN SELECT craigusers.id, craigusers.email, craigusers.url, maxprice, craigusers.location, `date`, description, craigusers.userid, pid, empty, cusers.email AS userEmail, preferences.from FROM craigusers JOIN cusers ON craigusers.userid = cusers.id JOIN preferences ON craigusers.userid = preferences.userid WHERE `delete`=0 ORDER BY craigusers.id;
+----+-------------+-------------+--------+---------------+---------+---------+-----------------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+---------------+---------+---------+-----------------------+------+-----------------------------+
| 1 | SIMPLE | craigusers | ref | userid,delete | delete | 1 | const | 1352 | Using where; Using filesort |
| 1 | SIMPLE | cusers | eq_ref | PRIMARY | PRIMARY | 4 | dsr.craigusers.userid | 1 | Using where |
| 1 | SIMPLE | preferences | eq_ref | PRIMARY | PRIMARY | 4 | dsr.craigusers.userid | 1 | Using where |
+----+-------------+-------------+--------+---------------+---------+---------+-----------------------+------+-----------------------------+
3 rows in set (0.03 sec)



The only comment I'd like to make about the above output is that I'm quite embarrassed by my tables. I created them 11 years ago and I would do things a bit differently now, including using better names, particularly ones that don't use reserved words and ones that are more descriptive.

Thanks again for everything!

Options: ReplyQuote


Subject
Views
Written By
Posted
2676
January 15, 2014 07:16AM
1123
January 15, 2014 11:44AM
1185
January 15, 2014 02:12PM
3438
January 17, 2014 05:52AM
1450
January 17, 2014 07:56AM
1215
January 17, 2014 11:23AM
1247
January 17, 2014 11:29AM
1315
January 17, 2014 02:03PM
1092
January 17, 2014 02:43PM
1241
January 17, 2014 05:13PM
Re: MySQL 5.5.31 cripplingly slow
1484
January 18, 2014 09:53AM
1164
January 18, 2014 04:00PM
1141
January 18, 2014 08:55PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.