… on Amazon EC2, and I have some interesting results that I wanted to share. I used a … 10. Now let’s come to the MySQL configuration. MySQL Configuration I used Percona Server 5.5.22-…= 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that the buffer pool is sized at …
Comment: InnoDB's gap locks
May 7, 2012 By 15 Comments
… my server ,and my results are as follow: Transaction1: root@localhost:test 14:33:38>start transaction; Query OK, 0 rows affected… inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread id 31929, query id 484616 localhost root update insert…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…query execution, which size can be controlled by tmp_table_size and which also only exist for duration of query…can do better as you can query temporary tables too: mysql> select sum(data_length+index…correspond to global memory allocation and will result in increased memory allocation until server is…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… this gap lock, when you run the same query twice, you get the same result, regardless other session modifications on that table…(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163…. In this isolation level, it is normal and expected that query results can change during a transaction, so there is no need…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… as mentioned at the start of the benchmark results section, the InnoDB dataset size is ~5G. Now let’s take a look… to MySQL 5.5 While with join_buffer_size set to 6M and read_rnd_buffer_size set to 6M, the query time for MySQL 5.6 becomes approximately equal to that of MySQL 5.5. MariaDB…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… the difference in numbers. Benchmark results For the purpose of this benchmark, I have used TPC-H Query #10 and ran it… (InnoDB dataset size ~95G), because the query was taking far too long to execute, ~11 hours in case of MySQL 5.5 and… as mentioned at the start of the benchmark results section, the InnoDB dataset size is ~5G. Ok so now let’s take…
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
… the results of these queries compare to the size of the tables on disk? For example, using file_per_table, if the total_size…/lib/mysql/databasename # ls -lh tablename* -rw-rw—- 1 mysql mysql 8.7K 2010-08-05 16:31 tablename.frm -rw-rw—- 1 mysql mysql 58G 2012-04-24 13:41 tablename.ibd On disk vs the information_schema query, there is around…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… a single base table. Knowing this, the query result set is treated as a materialized view…any size. Set processing is massively parallel . In fact, it is embarassingly parallel. Because Shard-Query…MySQL storage nodes are supported. Amdahl’s law applies to the distributed processing. The results from…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… the output from the ./run_query command, called pivot_results cd shard-query/ $ ./run_query < queries.sql | tee raw |./pivot_results & [1] 12359 $ tail -f ./raw…-cache=128 thread-cache-size=32 thread-stack=256K tmp-table-size=64M transaction-isolation=READ-COMMITTED user=mysql wait-timeout=86400 To…
