… MySQL in wild. I also wanted to share some troubleshooting techniques I use. Plot Memory Usage First I would like to see MySQL memory consumption plotted. I use “VSZ” columns from “ps” output on Linux. It helps me to understand how this memory allocation…_heap_table_size variable which allows you to limit size of MEMORY tables (the limit applies both to implicit and explicit ones) but…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
…than 1000 rows and doing it completely in memory. The plan optimizer picked was very good one,…found the following explanation from Timour Katchaounov in MySQL mailing list archives I have some recollection …picks value of min(number of tables, 7) essentially limiting search depth to no more than 7 …
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…. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…_orderdate LIMIT 10; In-memory workload Now let’s see how effective are the join optimizations when the workload fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is… 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
… revenue desc LIMIT 20; In-memory workload Now let’s see how effective is MRR when the workload fits entirely in memory. For… 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: Dynamic row format for MEMORY tables
September 6, 2011 By 3 Comments
… features in the MEMORY storage engine (also known as HEAP) in MySQL has been one of the long-standing limitations caused by the fact that variable-length records were not supported by the engine. This is true for all MySQL… community to remove this limitation. In 2008 Igor Chernyshev of eBay implemented true-VARCHAR support for MEMORY tables for MySQL 5.0. Although…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…am constantly referring to the amazing MySQL manual, especially the option and variable reference table. But just…_scanblogpercona.commanual server_idblogpercona.commanual shared_memoryblogpercona.commanual shared_memory_base_nameblogpercona.commanual show_slave_auth_infoblogpercona.commanual skip_…
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
…, fast, and just works. When your dataset exceeds your memory limitations or the database can simply not keep up any more… do. Using this application I can then compare using MySQL to using MySQL + Memcached, and then to other solutions like Tokyo Tyrant… simple, fast, and just works. When your dataset exceeds your memory limitations or the database can simply not keep up any more…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… LIMIT which restricts how many rows will be examined MySQL will still print full number. Here is example: mysql> explain select * from lt limit… for sort or temporary tables MySQL always calls it “filesort” even if it is done in memory. Also it would not tell… BLOB fields can’t work with in memory temporary table. The other thing – since MySQL 4.1 sorting can be done with…
Post: MySQL Limitations Part 4: One thread per connection
October 27, 2010 By 14 Comments
… what’s seriously limiting MySQL in core use cases (links: part 1, 2, 3). This post is about the way MySQL handles connections… processes that communicate through shared memory or other means. It’s cheap to create a connection to MySQL, because it just requires… them. That’s why I listed those as major limitations. And because MySQL is a multi-threaded database for Web usage that…
