MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:living liquid | Christian Meisinger Date:April 20 2009 2:28pm
Subject:Small InnoDB table with many concurrent queries
View as plain text  
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


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


thanks for any info
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