List:General Discussion« Previous MessageNext Message »
From:Krishna Chandra Prajapati Date:April 20 2009 2:58pm
Subject:Re: Small InnoDB table with many concurrent queries
View as plain text  
change connect timeout to 10sec
flatly use skip-name-resolve

whats the total size of your database.

On Mon, Apr 20, 2009 at 7:58 PM, living liquid | Christian Meisinger <
c.meisinger@stripped> wrote:

> 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

If not using myisam table, you it to 400mb

>
> max_allowed_packet      = 32M
> table_cache             = 10000

big (5000 to 6000)

>
> 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

if the query result set is small change it between 1 to 3 mb

>
> 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

too big can also be an issue

>
> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>


-- 
Krishna Chandra Prajapati

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