Some thoughts: Just use delay_key_write on slave servers. And only ever do INSERT/UPDATE on …
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…’ ‘Handler_write‘, ’292923929′ ‘Key_blocks_not_flushed’, ’0′ ‘Key_blocks_unused’, ’28987′ ‘Key_blocks_used’, ’1332′ ‘Key_read_requests’, ’1552507′ ‘Key_reads’, ’1654′ ‘Key_write_requests’, ’114303′ ‘Key_writes‘, ’1346′ ‘Max_used_connections’, ’651′ ‘Not_flushed_delayed…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… key blocks from temporary table index, so we get a lot of key writes which kill performance. The problem with writes is OS can delay writes… “key%”; +————————+———+ | Variable_name | Value | +————————+———+ | Key_blocks_not_flushed | 49361 | | Key_blocks_unused | 61201 | | Key_blocks_used | 49361 | | Key_read_requests | 4007329 | | Key_reads | 49361 | | Key_write_requests | 873177 | | Key_writes…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
… restores The ability to read and write on every node without needing to worry about replication delay However, good engineers know there… NOT NULL AUTO_INCREMENT, `j` varchar(32) DEFAULT NULL, PRIMARY KEY (`i`) node2 mysql> insert into autoinc (j) values (‘node2′ ); Query… this problem, quite simply, is the tradeoff to multi-node writing in a synchronous replication system with optimistic locking. So, when…
Post: Investigating MySQL Replication Latency in Percona XtraDB Cluster
March 3, 2013 By 13 Comments
… the script doing writes to DPE1 and immediately reading from DPE2 Summary: 94 out of 10000 rounds (0.94%) Delay distribution: Min…% of Writes. We can do it with Sysbench, for example: sysbench –test=oltp –oltp-test-mode=nontrx –oltp-nontrx-mode=update_key…-inc=off –oltp-test-mode=nontrx –oltp-nontrx-mode=update_key –mysql-user=root –mysql-password=”" –oltp-table-size=1000000 –num…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… incrementally maintaining materialized views. While writing Flexviews, I learned how to …result set merge optimization enabled: ON DUPLICATE KEY UPDATE `origin_airport_id`=VALUES(`origin_…>= 0) flight_delayed`=`sum(DepDelay >= 0) flight_delayed` + VALUES(`sum(DepDelay >= 0) flight_delayed`) — SQL …
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… data to their own clients, with no delays. Finally, the analysis is usually multi-dimensional…. for the initial insertion, because you can write CSV files with any programming language. … There are already inconvenient limitations on some key parts of partitioning, such as maintenance and …
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… NULL, PRIMARY KEY (`date_id`), KEY `FlightDate` (`FlightDate`), KEY `Year` (`Year`,`Quarter`,`Month`,`DayOfWeek`), KEY `Quarter` (`Quarter`,`Month`,`DayOfWeek`), KEY `Month` (`Month`,`DayOfWeek`), KEY `DayOfWeek` (`DayOfWeek… innodb-recovery-stats innodb-recovery-update-relay-log innodb-replication-delay=# innodb-rollback-on-timeout innodb-rollback-segments=16 innodb-stats… innodb-thread-sleep-delay=0 innodb-use-sys-stats-table innodb-write-io-threads=4 join-buffer-size=16M key-buffer-size=64M…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… about last failed foreign key constraint: ———————— LATEST FOREIGN KEY ERROR ———————— 060717 4:…done by adjusting innodb_thread_sleep_delay variable. Value is specified in … writes are done to OS cache, and being sequential writes these logs writes are pretty…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
…. This is however not performance gotcha I’m going to write about. There is one more thing you should be aware…) NOT NULL auto_increment, `c` tinyint(4) default NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=MEMORY and populated it with 1… could delay writes and do all the changes directly in memory. MyISAM however had to perform number of random writes to the key file…
