…memory available, and memory is what really really matters. High-Memory Quadruple Extra Large Instance 68.4 GB of memory…Now let’s come to the MySQL configuration. MySQL Configuration I used Percona Server 5… 100 million rows. Table Structure The table structure of the table with no secondary indexes …
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
… because the entire table fits in main memory. Each row is on the order of 30 bytes in the primary table, and so… rows and 55GB RAM, Inno would hit a memory wall. Given that Memory sizes are perhaps 10 times bigger than they were… python version that Mark wrote http://bazaar.launchpad.net/~mdcallag/mysql-patch/mytools/annotate/head%3A/bench/ibench/iibench.py) is…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… Permanent tables it is easy. We can look at information_schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory‘; +——————————-+ | sum(data…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
… read less than 1000 rows and doing it completely in memory. The plan optimizer picked was very good one, yet you… somewhat conflicting though as if we assume MySQL users do not join lots of tables (less than 7) when using 0 as… backward compatibility. In MySQL 5.6 things are likely to get even better handling joins of many tables as optimizer heuristics are…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…, MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table participating… in the build step is such that the hash table fits in memory. You can read more about the hash join algorithm… a look at the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M & read…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…. Suppose you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column=x… MySQL 5.6, when the workload fits entirely in memory, because there is no extra cost for memory access at random locations versus memory…-memory, in fact MRR adds extra sorting overhead which means that the queries are just a bit slower as compared to MySQL…
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…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 3 Comments
… 2008 Igor Chernyshev of eBay implemented true-VARCHAR support for MEMORY tables for MySQL 5.0. Although this patch did not add the… expected memory savings. One quite important feature is missing from this release however: MyISAM tables are still used for temporary tables where the MEMORY tables…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
One performance gotcha with MEMORY tables you might know about comes from the fact it is the only MySQL storage engine which defaults… comes from the fact MEMORY tables use HASH indexes by default. I’ve created rather similar test table: CREATE TABLE `test` ( `id` int(11) NOT NULL auto_increment, `c` tinyint(4) default NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=MEMORY…
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
… in memory workload and against single tables. Now after adding multi-tables support to…tables. 1. table_io_waits_summary_by_table mysql> select * from table_io_waits_summary_by_table where OBJECT_NAME=’sbtest55′ G *************************** 1. row *************************** OBJECT_TYPE: TABLE…
