Once your tables' engine are all of innodb, your configuration file has to be changed to fit innodb's feature, not myisam.
On Tue, Apr 21, 2009 at 2:09 PM, living liquid | Christian Meisinger < c.meisin...@livingliquid.com> wrote: > 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.meisin...@livingliquid.com> > > > >> 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 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com > > -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
