… written before – MyISAM Does Not Scale, or it does quite well – two main things stopping you is table locks and global mutex… information about table usage I will use Percona Patches: mysql> select * from information_schema.table_statistics where table_schema=’test’ and… use different formula if you like. mysql> select t.table_name,index_length/(select sum(index_length) from information_schema.tables where…
Post: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
… surprise with concurrent inserts into a MyISAM table. The inserts were not happening concurrently with SELECT statements; they were blocking and the… ordinary SELECT gets a lock on the table like this: Locked – read Low priority read lock But on INSERT…SELECT, you’ll see this: Read lock…
Post: MyISAM concurrent insert
June 13, 2006 By 12 Comments
… range-queries. The reason of problems is POSIX read-write locks, which are used to protect key_buffer from concurrent changes…-space (Solaris) or kernel-space (Linux). Some results for MyISAM table, query SELECT id FROM sbtest WHERE id BETWEEN N AND N…-concurrent-insert can improve MyISAM performance. As I said the reason of bad scalability is rw-locks and currently MySQL developers are…
Comment: Using MyISAM in production
May 1, 2008 By 36 Comments
… mostly getting lock wait time out and deadlock problems. by switching to myisam no locks occur on the tables and the selects which are summarizing the tables with write-intensive operations relieved free. i think myisam…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…lock_countblogpercona.commanual memlockblogpercona.commanual min_examined_row_limitblogpercona.commanual myisam_block_sizeblogpercona.commanual myisam_data_pointer_sizeblogpercona.commanual myisam…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… insert statement. concurrent_insert – Enables concurrent insert (while Selects are running) for MyISAM storage engine. Other Storage engine ether always have it… table locks or if LOCK TABLES are used. So in practice this option is used for MyISAM and MEMORY tables. max_write_lock_count…
Post: The perils of InnoDB with Debian and startup scripts
January 28, 2009 By 13 Comments
… happen on a server with MyISAM tables, if there are enough …| 6356 | user | statistics | select … | 6357 | user | statistics | select … | 6358 | user | Sending data | select … | 6359 | user | statistics | select … | 6360 | user | |… this is effectively a global lock. The debian-sys-maint …
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
… myisam and innodb tables. Global read lock only held until myisam tables are dumped. We are researching into how we could further improve lock times here when non-transactional tables are used Almost no locking, if not using myisam tables Built in… due to problems with “FLUSH TABLE WITH READ LOCK“. Namely when a select is blocking the flush from completing and queuing all…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…_insert_select‘, ’11′ ‘Com_kill’, ’47′ ‘Com_load’, ’0′ ‘Com_load_master_data’, ’0′ ‘Com_load_master_table’, ’0′ ‘Com_lock_tables…’, ’1462755007′ ‘Rpl_status’, ‘NULL’ ‘Select_full_join’, ’0′ ‘Select_full_range_join’, ’0′ ‘Select_range’, ’5683551′ ‘Select_range_check’, ’0′ ‘Select_scan’, ’1104831′ ‘Slave_open…=3600 wait_timeout=3600 key_buffer_size=32M # Only for MyISAM which isnt used. sort_buffer_size=2M query_cache_size…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… data pages – it also contain adaptive hash indexes, insert buffer, locks which also take some time. Though it is not as… but there are other things you need OS cache for – MyISAM tables (mysql database, temporary etc) will need it, .frm file… only described Innodb Buffer Pool selection for dedicated Innodb system. If you have fair amount of MyISAM, Archive, PBXT, Falcon or other…
