Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
setting sort_buffer_size = 1024M is an VERY VERY VERY bad idea.. this buffer size is ALWAYS allocated with every GROUP BY/ORDER BY query even when the needed memory is smaller.. –  Raymond Nijland Nov 4 '13 at 18:22
    
I think you should place your create table statements with querys and explains.. –  Raymond Nijland Nov 4 '13 at 18:25
    
okay, done in the edit! –  kellogs Nov 5 '13 at 1:51
    
Hi kellogs, please run the queries above and post the results (in the question body): EXPLAIN EXTENDED SELECT SQL_CALC_FOUND_ROWS SQL_NO_CACHE g.*, u.user_id FROM geotable g JOIN userstable u ON g.uid = u.uid WHERE ( lat >= ? AND lat <= ? AND lon >= ? AND lon <= ? ) ; EXPLAIN EXTENDED SELECT SQL_CALC_FOUND_ROWS SQL_NO_CACHE g.*, u.user_id FROM geotable g JOIN userstable u ON g.uid = u.uid WHERE ( lat >= ? AND lat <= ? AND lon >= ? AND lon <= ? ) ORDER BY time ASC; –  MTIhai Nov 5 '13 at 9:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.