… 10. Now let’s come to the MySQL configuration. MySQL Configuration I used Percona Server 5.5.22-… such that every partition would hold 100 million rows. Table Structure The table structure of the table…Now let’s come down to the interesting part. Results With the table purchases_noindex, that has no …
Comment: InnoDB's gap locks
May 7, 2012 By 15 Comments
… server ,and my results are as follow: Transaction1: root@localhost:test 14:33:38>start transaction; Query OK, 0 rows affected (0…:35:29>select * from t; +——+ | i | +——+ | 21 | | 30 | | 26 | +——+ 3 rows in set (0.00 sec) root@localhost:test 14:51… inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…(both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 …closed, other correspond to global memory allocation and will result in increased memory allocation until server is restarted. …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first… to the benchmarks, to see the difference in numbers. Benchmark results For the purpose of this benchmark, I have used TPC…. Note that as mentioned at the start of the benchmark results section, the InnoDB dataset size is ~5G. Now let’s…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… to the benchmarks, to see the difference in numbers. Benchmark results For the purpose of this benchmark, I have used TPC… thing to note is that MySQL 5.6 and MariaDB 5.5 are both reading more rows than MySQL 5.5, as can be seen by the numbers reported for the status counter Innodb_rows_read. While MySQL 5…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… you run the same query twice, you get the same result, regardless other session modifications on that table. This makes reads… lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700… this isolation level, it is normal and expected that query results can change during a transaction, so there is no need…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…mysql> select count(*) from A; +———-+ | count(*) | +———-+ | 2359296 | +———-+ 1 row in set (1.73 sec) mysql> select * from C; +——+ | t | +——+ | test | +——+ 1 row… */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…mysql> show processlist; +——-+——+———–+———-+———+——+————————-+—————————–+———–+—————+———–+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows…rows in set (0.00 sec) As result…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Comment: Finding out largest tables on MySQL Server
April 24, 2012 By 24 Comments
…results of these queries compare to the size … | CONCAT(table_schema, ‘.’, table_name) | rows | DATA | idx | total_size | idxfrac |…mysql/databasename # ls -lh tablename* -rw-rw—- 1 mysql mysql 8.7K 2010-08-05 16:31 tablename.frm -rw-rw—- 1 mysql mysql…
