Introduction

The MySQL Optimizer sometimes needs a temporary data-store during query processing, for storing intermediate results. Before MySQL 5.7, this need was serviced exclusively using a combination of the HEAP/MEMORY storage engine (for smaller tables) and the MyISAM storage engine (for larger tables). You can find more information on when disk based temporary tables (MyISAM or InnoDB) are used instead of MEMORY tables here.

The …

It is time for the Query Tuning Webinar again! This year I will be delivering the Webinar on July 24 at 10 a.m. PDT, Advanced MySQL Query Tuning, hosted by Percona. I have included some new topics about loose and tight index scan and will also show some real world examples and solutions for MySQL query optimizations.

You can register for the Webinar here. It will also be recorded, so if you can’t make it on July 24, 10am you can always watch it later.

A few weekends ago, I started to again look at the code in Drizzle for producing internal temporary tables. Basically, we have a few type of tables:

• Standard
• Temporary (from CREATE TEMPORARY TABLE)
• Temporary (from ALTER TABLE)
• Internal temporary (to help with query execution)

If you’re lucky enough to be creating one of the first three types, you go through an increasingly lovely pile of code that constructs a nice protobuf message about what the table should look like and hands all responsibility over to the storage engine as to how to do that. The basic idea is that Drizzle gets the heck out of the way and lets the storage engine do its thing. This code path looks rather different than what we inherited from MySQL. For a start, we actually have a StorageEngine object rather than just lumping everything into the handler (which we correctly name a Cursor). However… the final …

With MySQL 5.5 the default storage engine was changed to InnoDB. This was a good step as most users expected MySQL to support transactions, row level locking and all the other InnoDB features, but with 5.1 they sometimes forgot to choose the right storage engine. As most databases don't have multiple storage engines and many MySQL users changed the default storage engine to InnoDB this made the switch to MySQL easier and the default behaviour more in line with what people expect from a relational database.

Changing the storage engine can of course be done on a per table or per instance basis (default-storage-engine in my.cnf). The temporary tables created with 'CREATE TEMPORARY TABLE ...' should not be forgotten. The performance of InnoDB or MyISAM for temporary tables can have quite some impact, especially with slow storage, a buffer pool which is too small to hold the complete dataset or very small temporary tables. In MySQL 5.6 …

This guide will get you up and running with how to identify the bottleneck queries using the excellent tool pt-query-digest. You will learn how to use and analyze the output returned by pt-query-digest. You will also learn some differences between slow query logging in various MySQL versions. Later on in the post I will also show you how to make use of the extra diagnostic data available with Percona Server.

The post Profiling your slow queries using pt-query-digest and some love from Percona Server appeared first on ovais.tariq.

In my previous post, I’ve showed how to use Dtrace to find queries creating disk temporary tables (only available for OS with dtrace: solaris, freebsd, etc).

In MySQL 5.6 (which is not released yet, use “labs” version for now) we can use new performance_schema table events_statements_history or events_statements_history_long to find all performance metrics for all queries including created disk/memory tables, use of index, etc. WOW! This is what I have been waiting for a long time!

To illustrate, I have grabbed mysql-5.6.3-labs-performance-schema-linux2.6-x86_64.tar.gz from labs.mysql.com (this feature is only in labs version) and run sysbench readonly test (you need to disable prepared statements in sysbench, seems to be not working with …

Sometimes we have a lots of small and rather fast queries which use group by/order by, thus creating temporary tables. Some of those queries are retrieving text fields and mysql have to use disk (myisam) temporary tables. Those queries usually run for less than 1-2 seconds, so they did not get into slow query log, however, they sometimes add serious load on the system.

Here is the stat example:

bash-3.00$  /usr/local/mysql/bin/mysqladmin -uroot -p -i 2 -r extended-status|grep tmp_disk
...
| Created_tmp_disk_tables           | 109           |
| Created_tmp_disk_tables           | 101           |
| Created_tmp_disk_tables           | 122           |
...

40-50 tmp_disk_tables created per second

So, how can we grab those queries? Usually we have to temporary enable general log, filter out queries with “group by/order by” and profile them all. On solaris/mac we can use dtrace instead.

Here is the simple script, …

Followup on Experiment: MySQL tmpdir on tmpfs, about tmpdir=/dev/shm in my.cnf (it’s not a dynamic variable that can be set at runtime). It’s working well, also confirmed by comments from others that they’ve been using it for a while.

This particular setting is Linux specific. On Solaris, the default /tmp is already on a tmpfs so that’s fine too. Brian reminded me that this tweak is also useful if you’re stuck with a 32-bit OS as you can then utilise some more memory in a practical way.

Extra useful hint from Harrison: if you are using replication, you will also want slave_load_tmpdir=/tmp on your slave (real disk which survives a restart). The issue is that with statement based binary logging, there are many events which create a file for a replicated LOAD DATA INFILE. If …

In MySQL, the tmpdir path is mainly used for disk-based sorts (if the sort_buffer_size is not enough) and disk-based temp tables. The latter cannot always be avoided even if you made tmp_table_size and max_heap_table_size quite large, since MEMORY tables don’t support TEXT/BLOB type columns, and also since you just really don’t want to run the risk of exceeding available memory by setting these things too large.

You can see how your server is doing with temporary tables, how many of those become disk tables, and also other created temporary files, by looking at SHOW GLOBAL STATUS LIKE ‘Created_tmp%’;

So why might tmpfs be better? Well, not just any tmpfs, but specifically /dev/shm. Its …

Few days back, one of my colleagues posted a good question. It sounds something like this;

"Temporary tables are session based that means under different sessions we can create temporary tables with similar names. Now since slave thread is singleton, how does it manage to keep them separate?"

He was very much right in asking this and the answer is not all that intuitive. Lets go through the binlog events to see why it is not that intuitive.

   1: mysql> SHOW BINLOG EVENTS IN 'log-bin.000016';

   2: . . .     

3: | log-bin.000016 |  389 | Query       | 2515922453 |         488 | use `test`; CREATE TEMPORARY TABLE test.t(a int)         |    

4: | log-bin.000016 |  488 | Query       | 2515922453 |         582 | use `test`; INSERT INTO test.t(a) VALUES(1)         |    

5: | log-bin.000016 |  582 | Query       | 2515922453 |         676 | use `test`; INSERT INTO test.t(a) VALUES(3) …