Showing entries 1 to 2
Displaying posts with tag: optimizer_switch (reset)
Why Optimization derived_merge can Break Your Queries

Lately, I worked on several queries which started returning wrong results after upgrading MySQL Server to version 5.7 The reason for the failure was derived merge optimization which is one of the default


  options. Issues were solved, though at the price of performance, when we turned it


 . But, more importantly, we could not predict if any other query would start returning incorrect data, to allow us to fix the application before it was too late. Therefore I tried to find reasons why


  can fail. Analyzing the problem

In the first run, we turned SQL Mode


on, and this removed most of the problematic queries. That said, few of the queries that were successfully working with


  were affected.

A quick search in the …

[Read more]
Back on disk temporary tables

We already blog a lot on temporary tables on disk, how this could be bad for your workload and should be avoid.  Each temporary table will open a file descriptor,  external kernel call and by nature a well know file system slow operation.

We can point this benchmark simulating a working mail server

Monitoring of such queries can be trace via the status of

Evidence of what is happening watching many Aria or MyISAM tables created on disk :

/usr/sbin/lsof  | grep "mysql" | grep "#"
mysqld     1855     mysql 1658u      REG                8,1       8192    1505932 …

[Read more]
Showing entries 1 to 2