…for causing unexpected slowdowns. delay_key_writes Whatever way you enable this option – globally, for table or for set of statements (by using LOCK TABLES/UNLOCK TABLES…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…? To much to handle for 32 bit? Some stats at writing time (18:37 friday) …’ ‘Com_create_function’, ’0′ ‘Com_create_index‘, ’0′ ‘Com_create_table‘, ’31′ ‘Com_dealloc_sql’, ’0′ ‘…Com_load_master_data’, ’0′ ‘Com_load_master_table‘, ’0′ ‘Com_lock_tables‘, ’102′ ‘Com_optimize’, ’0′ ‘Com_preload_keys’,…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… tables have “holes” (deleted rows) which can help significantly. delay_key_write Delay updating indexes for MyISAM tables. Good for performance but tables …for storage engines with table locks or if LOCK TABLES are used. So in practice this option is used for MyISAM and MEMORY tables. max_write_lock…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… hash indexes, insert buffer, locks which also take some time. Though it is not as critical – for … cache for your Innodb tables but there are other things you need OS cache for – MyISAM tables (mysql … copying, due to adaptive hash indexes, ability to buffer writes and number of other factors so …
Post: Percona XtraDB Cluster reference architecture with HaProxy
June 20, 2012 By 63 Comments
…view# 18: Primary, number of nodes: 3, my index: 0, protocol version 2 120619 13:20:17…because of optimistic locking at commit time, rollbacks can happen if we have conflicting writes. In the second…rows affected (0.00 sec) Populate the table with data for the benchmark. # sysbench –test=oltp –db-…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… of memory for write buffering efficiently. Q30: Question about using solid state disks for storing ib_log* files; I know that innodb writes files… online reorganize which locks and reorganizes small portions of table online. Q38: Seems that adaptive hash indexes are not used for primary key lookups…
Post: Write contentions on the query cache
September 5, 2012 By 5 Comments
… fields were correctly indexed. With SHOW PROFILE, … | | checking permissions | 0.000004 | | Opening tables | 0.000021 | | System lock | 0.000009 | | init | 0…. there is competition for the lock. Of course, … writing a result set into the cache. When writing, locking…
Comment: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
October 26, 2011 By 15 Comments
… an innodb table that “dumps” any updates to the table to a myisam table holding a log of updates. The update table has the… index, or something simmilar) while checking for key value existence, as inserts in myisam are concurrent with reads, and then try to lock for write access when performing the update. This would lead to a deadlock when trying to upgrade two read locks to write locks or…
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
…writes and wait until InnoDB flushes all pages. To stop write activity we can either stop application or lock tables: mysql> flush tables with read lock…INDEX ————————————- Ibuf: size 1, free list len 398, seg size 400, Another thread writing…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…. Some data hand updated.’; — – Table structure for table `ontime_fact` — CREATE TABLE `ontime_fact` ( `date_id` …Data_length: 1616904192 Max_data_length: 0 Index_length: 539279360 Data_free: 4194304 Auto_…table innodb-write-io-threads=4 join-buffer-size=16M key-buffer-size=64M local-infile=on lock…
