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

Displaying posts with tag: temporary table (reset)

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

On July 24 at 10 a.m. PDT, I will be delivering a Webinar on Advanced MySQL Query Tuning. I will focus on optimizing the common slow queries with group by and order by. Those queries usually create temporary tables and perform a “filesort” operation. I will show how to optimize those queries so that they will be running significantly faster, which will increase the application performance and decrease MySQL load.

I presented a similar topic in April at the

  [Read more...]
Temporary file behavior… (and how lsof save my life)
+0 Vote Up -0Vote Down

I would like to share this story based on a true event about the temporary files behavior in MySQL.

MONyog reports this error to my already full mailbox several times a day :


 

1 – Catch the query (if you can) !

 

I don’t have access to the client logs but I would like to know which query is involved in this error.

Let me explain how I can retrieve informations about this query with MONyog and a very simple shell loop :

  •  Enable the query sniffer in MONyog (based on processlist) : Edit server -> Advanced settings -> Sniffer settings

  [Read more...]
Performance improvements for big INFORMATION_SCHEMA tables
Employee +2 Vote Up -0Vote Down
A short while after I fixed the legacy bug that prevented temporary MyISAM tables from using the dynamic record format, I got an email from Davi Arnaut @ Twitter. It turned out that Twitter needed to fix the very same problem, but for the case when INFORMATION_SCHEMA temporary tables use MyISAM.

In short, INFORMATION_SCHEMA tables provide access to database metadata. Despite their name, they are more like views than tables: when you query them, relevant data is gathered from the dictionary and other server internals, not from tables. The gathered data is stored in a temporary table (memory or MyISAM depending on size) and then returned to the user.

The reason Davi emailed me was to let me know that he had further improved the fix for



  [Read more...]
Copying unused bytes is bad (duh!)
Employee +3 Vote Up -0Vote Down
Last summer my colleague Marko Mäkelä committed this seemingly innocent performance fix for InnoDB in MySQL 5.6:

3581 Marko Makela    2011-08-10
Bug#12835650 VARCHAR maximum length performance impact

row_sel_field_store_in_mysql_format(): Do not pad the unused part of
the buffer reserved for a True VARCHAR column (introduced in 5.0.3).
Add Valgrind instrumentation ensuring that the unused part will be
flagged uninitialized.

Before this, buffers which were used to send VARCHARs from InnoDB to the MySQL server were padded with 0s if the string was shorter than specified by the column. If, e.g., the string "foo" was stored in a VARCHAR(8), InnoDB used to write "3foo00000" to the buffer (the first character - 3 - determines the actual length of the string). However, even though these trailing bytes








  [Read more...]
Why a new memory engine may change everything ?
+1 Vote Up -1Vote Down

I’m sure you are aware that the last Percona server release includes a new improved MEMORY storage engine for MySQL.
This new engine is based on Dynamic Row Format and offers some of great features, specialy for VARCHAR, VARBINARY, TEXT and BLOB fields in MEMORY tables.

But because this new MEMORY engine by Percona has some limitations and because Percona server hasn’t used it for its internal temporary tables yet, I would like to talk about what can be the real benefits to have a brand new MEMORY engine based on Dynamic row format specialy for internal memory


  [Read more...]
MySQL ERROR 1137 (HY000): Can’t reopen table: ‘tmp_journals’
+0 Vote Up -0Vote Down

When setting up a query using a temporary lookup table, I got this error:

ERROR 1137 (HY000): Can't reopen table: 'tmp_journals'

It transpires that since 4.1 the way MySQL handles temporary tables has changed. This affects joins, unions and subqueries. There is an obvious fix:

mysql> CREATE TEMPORARY TABLE tmp_journals_2 LIKE tmp_journals;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tmp_journals_2 SELECT * FROM tmp_journals;
Query OK, 3228659 rows affected (2.01 sec)
Records: 3228659  Duplicates: 0  Warnings: 0 

Then the query is easy:

SELECT COUNT(1) cnt, journal_invoice_ref
FROM tmp_journals
GROUP BY journal_date 
HAVING cnt > 10000</pre>

UNION

SELECT COUNT(1) cnt, journal_invoice_ref
FROM tmp_journals_2
GROUP BY journal_invoice_ref
HAVING cnt < 10

Running a case sensitive query in on a case insensitive table
+0 Vote Up -0Vote Down

A colleague at work asked me “how can I run a case sensitive select on a case insensitive table?” out of curiosity and for a moment I hesitated, then said, yeah why not :) ….

Below are two different approaches (one of which is quite inefficient) and if anyone has another way, better or worse, please do leave a comment with your suggested approach :).

Cheers,
Darren

Preparation


mysql [localhost] {root} (test) > create table t1(a varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {root} (test) > insert into t1 (a) values ('darren');
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {root} (test) > insert into t1 (a) values ('Darren');
Query OK, 1 row affected (0.00 sec)

mysql






  [Read more...]
Gigantic IN Clauses
+0 Vote Up -0Vote Down

Over the last few weeks I’ve been looking at several customers’ slow query logs, and I found in many of them an odd type of query. These are SELECT statements that contain an IN clause that includes dozens, sometimes hundreds of values. These statements often end in the slow query log. I’m not sure if these queries are this way by design or if they are generated by a specific database development tool.

I did some tests in one of my own databases, one with only around 10K rows in its largest table. The database corresponds to the Amarok media player. For example, I queried for songs by B. B. King (spelled “BB King”, “B.B. King”, etc. or with other artists: “B. B. King & Eric Clapton”).

The first query used a

  [Read more...]
Showing entries 1 to 8

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.