List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:April 20 2009 5:24pm
Subject:Re: Small InnoDB table with many concurrent queries
View as plain text  
MyISAM should be fine with many SELECTs running at once. Tables are
only locked for INSERTs DELETEs and UPDATEs, and conditionally at
that. You can set MySQL to always append INSERTs to the end of the
file so you can insert while selecting.

What is the whole picture? If you were having locking problems, you
are doing more than just a lot of SELECTs. How many inserts, updates,
deletes are you doing? If you are doing a lot of updates, even InnoDB
will block if you are trying to update the same record across queries.

If you have a lot of querying in "sending data" state, check which
ones are in a different state. One of those may be the culprit.

Brent Baisley

On Mon, Apr 20, 2009 at 10:28 AM, 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
> 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
>
> ----------------------------------------------
>
>
> 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
>
>
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