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.meisin...@livingliquid.com> 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=brentt...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
