Thing is I was getting plenty of
131104 20:10:36 [ERROR] /usr/sbin/mysqld: Sort aborted
with 256M and rather erratic behaviour in my load test.
Now at
sort_buffer_size = 1024M #! althoug ppl say to leave this at its default 2M; uhm, unexplained SORT ABORTED, upping it again from 256M
only got 3 such aborts. Uhm... is there anything else to tweak instead ?I get the feeling upping that setting so much does not bode well.
8 GB RAM, 16 CPU machine.
my.cnf:
max_connections = 100
#table_cache = 8M
#thread_concurrency = 10
sort_buffer_size = 1024M #! althoug ppl say to leave this at its default 2M; uhm, unexplained SORT ABORTED, upping it again from 256M
join_buffer_size = 2M #! desperation calls for it...
read_rnd_buffer_size = 2M #!
tmp_table_size = 320M #!
max_heap_table_size = 320M #!
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * InnoDB
#
innodb_buffer_pool_size = 4G #! This should be set to 50-80% of your machine's memory
innodb_flush_method = O_DIRECT #!
innodb_file_per_table = 1 #! may or may not help us (?)
#innodb_data_file_path = ibdata1:1000M:autoextend
#innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 2 #! You can achieve better performance by setting the value different from 1, but then you can lose up to one second worth of transactions in a crash
innodb_support_xa = 0 #! improves performance, dunno really if okin our case (?)
#innodb_lock_wait_timeout = 50
#innodb_log_files_in_group = 2
innodb_log_file_size = 700M #! Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group
#innodb_log_buffer_size = 8M
innodb_thread_concurrency = 31 #! A recommended value is 2 times the number of CPUs plus the number of disks.
Oh, and one more - the culprit for this high sort buffer requirement is a query incoming at a rate of 13 qps that will not use an index for sorting its resultset - of about 15k results each (with a high variance). It can't (apparently) use an index due to it being like
WHERE col1 BETWEEN ? AND ? ORDER BY col2;
Order by wouldn't play along nicely with range scans. Thanks!
LE:
| geotable | CREATE TABLE `geotable` (
`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`accuracy` smallint(6) NOT NULL DEFAULT '0',
`activity` tinyint(4) NOT NULL DEFAULT '0',
`lat` double(9,8) NOT NULL,
`lon` double(9,8) NOT NULL,
`language` char(2) NOT NULL DEFAULT 'en',
`sex` char(1) NOT NULL DEFAULT 'm',
`locality` varchar(128) NOT NULL DEFAULT 'undisclosed',
`country` char(4) NOT NULL DEFAULT '',
`province` varchar(32) NOT NULL DEFAULT '',
`city` varchar(32) NOT NULL DEFAULT '',
`text` varchar(32) DEFAULT 'some nickname',
`status` varchar(64) DEFAULT 'ask me',
`time` bigint(13) NOT NULL,
UNIQUE KEY `uk_uid` (`uid`),
KEY `lat_lon_time` (`lat`,`lon`,`time`),
KEY `ctry_time` (`country`,`time`),
KEY `time_lang` (`time`,`language`),
KEY `time_sex` (`time`,`sex`),
CONSTRAINT `geotable_constr_1` FOREIGN KEY (`uid`) REFERENCES `userstable` (`uid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=700555 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
| userstable | CREATE TABLE `userstable` (
`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(2049) NOT NULL,
`sha1_user_id` char(40) CHARACTER SET ascii NOT NULL,
`user_pw` varchar(255) DEFAULT NULL,
`acc_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_login` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_logout` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`online_status` tinyint(4) DEFAULT '0',
`failed_logins` int(11) DEFAULT '0',
`account_status` tinyint(4) DEFAULT '1',
PRIMARY KEY (`uid`),
UNIQUE KEY `sha1_user_id` (`sha1_user_id`),
KEY `user_pw` (`user_pw`),
KEY `user_id` (`user_id`(255)),
KEY `last_login` (`last_login`),
KEY `last_logout` (`last_logout`)
) ENGINE=InnoDB AUTO_INCREMENT=700562 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
Query is at query not taking index into account