List:General Discussion« Previous MessageNext Message »
From:living liquid | Christian Meisinger Date:April 21 2009 6:09am
Subject:Re: Small InnoDB table with many concurrent queries
View as plain text  
ah sorry... there are a few UPDATEs too but most is SELECTs...

60:40 i would say.


> Excuse me. But why concurrent queries request table locks?
> 
> 2009/4/20 living liquid | Christian Meisinger <c.meisinger@stripped>
> 
>> Hi there.
>>
>> I've a small table with my daily banner hits.
>> 1. version was with myisam but with a lot of concurrent queries (all
>> SELECTs) i get too many table locks.
>> so i changed it to an innodb table.
>> works great most of the time.
>>
>> sometimes it seems to be too much, starting at about 500 concurrent queries
>> i see a huge amount of processes
>> taking about 3 minutes to finish 'sending data'.
>>
>> the SELECT queries use the correct index and data returned is small (2
>> integers).
>>
>> the table has only 4MB and about 35000 rows.
>> it can't be the size of the table...
>>
>> mysql server is connected with a 1G switch.
>> so i don't think it's network related.
>>
>> mysql server is a dual xeon 2,3GHz with 8G ram and SCSI disk RAID5.
>>
>> did i hit a innodb limit with this server configuration?
>> or is my my.cnf bad?
>>
>> my.cnf ---------------------------------------
>>
>> key_buffer              = 750M
>> max_allowed_packet      = 32M
>> table_cache             = 10000
>> sort_buffer_size        = 4M
>> join_buffer_size        = 4M
>> read_buffer_size        = 2M
>> read_rnd_buffer_size    = 4M
>> myisam_sort_buffer_size = 128M
>> query_cache_size        = 750M
>> query_cache_limit       = 16M
>> thread_cache            = 32
>> thread_concurrency      = 16
>>
>> tmp_table_size          = 700M
>> max_heap_table_size     = 700M
>>
>> net_buffer_length = 16K
>>
>> skip-external-locking
>>
>> innodb_additional_mem_pool_size = 16M
>> innodb_buffer_pool_size         = 2G
>> innodb_thread_concurrency       = 16
>> innodb_flush_log_at_trx_commit  = 2
>> innodb_log_buffer_size          = 8M
>> innodb_lock_wait_timeout        = 120
>> innodb_log_file_size            = 256M
>> innodb_log_files_in_group       = 3

Thread
Small InnoDB table with many concurrent queries living liquid | Christian Meisinger20 Apr
  • Re: Small InnoDB table with many concurrent queriesKrishna Chandra Prajapati20 Apr
  • Re: Small InnoDB table with many concurrent queriesBrent Baisley20 Apr
Re: Small InnoDB table with many concurrent queriesliving liquid | Christian Meisinger21 Apr
  • Re: Small InnoDB table with many concurrent queriesMoon's Father21 Apr