… innodb_io_capacity = 800 innodb_read_io_threads = 8 innodb_write_io_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size = 0…” method reduces the impact of traditional InnoDB log flushing, which can cause downward spikes in performance. Other then that, I have also…
Post: Testing Samsung SSD SATA 256GB 830 - not all SSD created equal
April 25, 2012 By 10 Comments
… every 10 sec to see how stable the performance is, and it really helps to observe some artifacts… InnoDB is not alone with its flashing problems. You can see there periodical stalls in throughput (0 throughput …I ran random read sync IO mode, now for 1-64 threads. The throughput: We are getting to …
Post: Testing STEC SSD MACH16 200GB SLC
April 25, 2012 By 7 Comments
…are two reasons for that. First, MySQL/InnoDB uses async writes, so this will …=rndwr –max-time=18000 –max-requests=0 –num-threads=$numthreads –rand-init=on –… sec to see how stable the performance is, and it really helps to …random read sync IO mode, now for 1-64 threads. The throughput: We are…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…_tables 1 1 1 1 1 Created_tmp_tables 1 1 1 1 1 Handler_mrr_init N/A 0 0 1 1 Handler_mrr_rowid_refills N/A N/A N/A 1 0 Handler… these lookups are performed in a single call to the storage engine and the counters Handler_read_key and Innodb_rows_read… and then to perform the MRR range scan on the PK. This causes the counters Handler_read_key and Innodb_rows_read…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… WHERE condition defined as: l_partkey = x and l_quantity >= 1 and l_quantity = 1 and l_quantity
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… values by PK column and then performing the lookup, and then there are other possibilities like InnoDB doing read_ahead by noticing…=4M Created_tmp_disk_tables 0 0 0 0 0 0 Created_tmp_tables 1 1 1 1 1 1 Handler_mrr_init N/A 112 3 1133 1 1 Handler_mrr_key_refills… Innodb_pages_read 329115 355323 143808 358308 144798 144881 Innodb_rows_read 4127318 6718351 6611675 6707882 5479445 5527245 Select_scan 1 1 1 1 1 1 Sort_scan 1 1 1 1 1 1…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…-transaction to get consistent backup for their Innodb tables without making database read only. … particular ALTER TABLE. When ALTER TABLE is Performed in many cases it will Create …select * from C; +——+—+ | t | i | +——+—+ | test | 0 | +——+—+ 1 row in set (0.00 sec) As you can …
Post: Announcing Percona XtraBackup 2.0.0 GA
April 4, 2012 By 11 Comments
…1.9.2), only the version number has changed. New since 1.6: Percona XtraBackup can now save Galera replication information while performing…Percona XtraBackup against debug versions of InnoDB. This is only for very advanced …F You can download Percona XtraBackup 2.0.0 from our downloads site or …
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…> INSERT INTO t VALUES(31); Query OK, 1 row affected (0.00 sec) After running the delete …the concurrency and the performance you can disable them in two different ways: 1- Change the ISOLATION level…information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses …
Post: ext4 vs xfs on SSD
March 15, 2012 By 23 Comments
… 1 thread: 87 MiB/sec ext4 4 threads: 74 MiB/sec xfs 4 threads: 97 MiB/sec Dropping performance … 5.1 + InnoDB-plugin and later MySQL 5.5 (or equally Percona Server 5.1 and 5.5), InnoDB uses “asynchronous…d,s,id){var js,fjs=d.getElementsByTagName(s)[0];if(!d.getElementById(id)){js=d.createElement(s);…
