Sysbench has three distribution for random numbers: uniform, special and gaussian. I mostly use uniform and special, … physical sense of this parameter, if to apply to database tables, is following: say row 1 accessed N, then row 2… – a ranking tutorial To see distribution on graphs, I took tables with 1mln rows and run row lookup 1 million times…
Comment: How to load large files safely into InnoDB with LOAD DATA INFILE
May 9, 2012 By 28 Comments
Good to see a healthy thread spread across a good number of years. Thanks Baron! As I was reading the part… on master LOAD DATA INFILE ‘/tmp/data.out’ INTO TABLE some_data_table; Using this strategy, replication continues to happen without a…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
… into interesting problem – query joining about 20 tables (thank you ORM by joining all tables connected with foreign keys just in case… of execution plans changing for some over queries joining less number of tables. I was wondering if 0 is automatic selection why… learn how automatic selection works – it picks value of min(number of tables, 7) essentially limiting search depth to no more than…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… is made against base table, the number of these point primary key lookups will be depend on the number of rows that match… move on to the benchmarks, to see the difference in numbers. Benchmark results For the purpose of this benchmark, I have…_rows_read. While MySQL 5.6 is also reporting increased numbers for the counter Handler_read_key. This is because of…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… called the build step. After the hash table has been created, rows from table t2 are read and hash function is applied… move on to the benchmarks, to see the difference in numbers. Benchmark results For the purpose of this benchmark, I have… been changed to become sequential. The two other most important numbers are values for Innodb_buffer_pool_reads and Innodb_data…
Comment: How to recover a single InnoDB table from a Full Backup
March 22, 2012 By 7 Comments
…table –target-dir=/mnt/mysql/export/ –tables=data # xtrabackup_55 –prepare –export –innodb-file-per-table –target-dir=/mnt/mysql/export ALTER TABLE… Error: page 0 log sequence number 6094071743825 InnoDB: is in the future! Current system log sequence number 6087296438643. InnoDB: Your database …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 26, 2012 By 20 Comments
… i work in an environment with a large three digit number of servers, and in which we clone new slaves from… that is derived from that. Essentially, we are using FLUSH TABLES WITH READ LOCK, and then run ‘lvcreate -s’ to create…. We are now using ‘service mysql stop’ instead of FLUSH TABLES WITH READ LOCK to get reliable snapshots from a donor…
Comment: Joining many tables in MySQL - optimizer_search_depth
April 23, 2012 By 6 Comments
… a reasonable limit for the search depth (and the total number of tables) that can be optimized in reasonable time. I reached the conclusion that if we want a simple magic threshold, the number…
Post: Announcing Percona XtraBackup 2.0.0 GA
April 4, 2012 By 11 Comments
… from the last beta (1.9.2), only the version number has changed. New since 1.6: Percona XtraBackup can now… + external (usually single-threaded) compression utilities, which also had a number of other limitations (e.g. could not be used with… required to uncompress the entire backup to restore a single table). Percona XtraBackup now supports streaming incremental backups. In previous versions…
