Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 10

Displaying posts with tag: temporary tables (reset)

New Upcoming Webinar: Advanced Query Tuning
+1 Vote Up -0Vote Down

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.

Refactoring Internal temporary tables (another stab at it)
+1 Vote Up -2Vote Down

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

  [Read more...]
MyISAM in a MySQL 5.6 InnoDB Only instance
+1 Vote Up -0Vote Down
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

  [Read more...]
Profiling your slow queries using pt-query-digest and some love from Percona Server
+2 Vote Up -0Vote Down

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.

Using MySQL 5.6 to find queries creating disk temporary tables
+2 Vote Up -0Vote Down

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

  [Read more...]
Using Dtrace to find queries creating disk temporary tables
+0 Vote Up -0Vote Down

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

  [Read more...]
Update: MySQL tmpdir on tmpfs
+0 Vote Up -0Vote Down

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

  [Read more...]
Experiment: MySQL tmpdir on tmpfs
+3 Vote Up -3Vote Down

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

  [Read more...]
Temporary tables as seen by replication slave
+0 Vote Up -0Vote Down

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)
  [Read more...]
More MySQL idiocy, or "Why does INFORMATION_SCHEMA fail to show information about TEMPORARY tables?"
+0 Vote Up -0Vote Down
This problem is "documented" in two bugs:
http://bugs.mysql.com/bug.php?id=36073
http://bugs.mysql.com/bug.php?id=20001

Why in the world is this just a "documentation fix"? There have been a number of occasions when I've tried to write generic stored procedures that might want to analyze information about temporary tables. For instance, given a SELECT statement, I want to determine what datatypes each select clause produces. A fairly straightforward way to do this might be to run a "create temporary table xyz as (SELECT ... LIMIT 0), to produce an empty temporary table to analyze. This doesn't work however, because there isn't any information on xyz in the information schema! Worse, SHOW statements don't work as a PREPARED STATEMENT so there isn't any way to determine this



  [Read more...]
Showing entries 1 to 10 of 10

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.