MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Moon's Father Date:April 21 2009 6:18am
Subject:Re: Small InnoDB table with many concurrent queries
View as plain text  
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.meisinger@stripped> 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.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
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>


-- 
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

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