MySQL – Where Does MySQL Write Temporary Tables?

innodbMySQLoptimization

MySQL 5.1.x | Windows Server 2008

Data Directory is on a different partition.

I have an innodb database (innodb_file_per_table is set) that I'm trying to optimize and I don't appear to have enough space. When I run the optimize command, I see:

foodb.foo_table
note      : Table does not support optimize, doing recreate + analyze instead
error     : The table '#sql-123_foo' is full
status    : Operation failed

My understanding is that when I run OPTIMIZE TABLE, it's essentially running ALTER TABLE behind the scenes and that usually needs to make a copy of the table. My question is, where is the table being created?

Best Answer

From the MySQL manual for 5.1:

..

On Windows, Netware and OS2, MySQL checks in order the values of the TMPDIR, TEMP, and TMP environment variables. For the first one found to be set, MySQL uses it and does not check those remaining. If none of TMPDIR, TEMP, or TMP are set, MySQL uses the Windows system default, which is usually C:\windows\temp.

..

ALTER TABLE creates a temporary copy of the original table in the same directory as the original table.

In newer MySQL versions it gets a bit more complicated to explain because there is also an in-place fast ALTER TABLE, but for 5.1 the answer to your question is simply "in the table's directory".

Related Solutions

MySQL – Optimizing InnoDB Default Settings

The way to tune InnoDB is centered around

InnoDB Buffer Pool : It caches data pages and index pages. The amount of data and index you can cache is not a function of disk space constraints but a function of available memory and diskspace currently used by InnoDB.
InnoDB MetaData : By default, the file ibdata1 normally houses anything and everything InnoDB. That would include data pages, index pages, table metadata, MVCC data.

Here is a formula I have used for the past 5 years to compute the InnoDB Buffer Pool based on diskspace used by InnoDB data and index pages:

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(Power1024<0,0,
IF(Power1024>3,0,Power1024)))+0.49999),SUBSTR(' KMG',IF(Power1024<0,0,
IF(Power1024>3,0,Power1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,(SELECT 2 Power1024) B;

Use (SELECT 0 Power1024) for Bytes
Use (SELECT 1 Power1024) for KB
Use (SELECT 2 Power1024) for MB
Use (SELECT 3 Power1024) for GB
Use (SELECT 4 Power1024) for TB (Email Me if you have TerraBytes of RAM)

Of course, I said a function of available memory and diskspace currently used by InnoDB. From here, just use common sense. The recommended number from the above query SHOULD NOT EXCEED 75% OF INSTALLED RAM !!! That's the simplest rule-of-thumb for sizing the InnoDB Buffer Pool.

You should also set innodb_flush_method to O_DIRECT since it will provided stable synchronous writes of InnoDB. I have also written a post on how to optimize Disk Storage for InnoDB.

With regard to the message Table does not support optimize, doing recreate + analyze instead, the reason why you get that error message is the fact that the storage engine is InnoDB. Mechanically, OPTIMIZE TABLE just copies the table to a temp table and performs ANALYZE TABLE.

In reality, ANALYZE TABLE against InnoDB is completely useless. Even if you ran ANALYZE TABLE on an InnoDB table, the InnoDB storage engine performs dives into the index for cardinality approximations over and over again, thus trashing the statistics you just compiled. In fact, Percona performed some tests on ANALYZE TABLE and came to that same conclusion as well.

Here are other posts I have made over the year about InnoDB Tuning

Mysql – Cannot write to thesql database while select query in process

Your first query is asking for 20,000 rows. what is being victimized here ???

Your Innodb Buffer Pool and Log Files.

Because of SELECT * FROM history WHERE log LIKE '%a%' ORDER BY log ASC LIMIT 0, 20000;, all the data pages for the history table is hogging InnoDB resources.

Since you are using Amazon RDS, you cannot resize the InnoDB Log Files. They are always 128M under all RDS MySQL models. Your buffer pool may or may not be full (You have 5882511360 set for it, which is 5610M or 5.4785G). Naturally, bigger model means more IOPs.

You can run this to see how full the buffer pool is

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Another aspect I see is the log field (TEXT). You are asking for an ORDER BY on this big column.

You need to change the first query to throttle the SELECT. In other words, fetch smaller chunks of history.

If you cannot change the data or the query, there is nothing you can configure except to shift to m1.xlarge (Comes with 11922309120 as the Buffer Pool Size = 11370M = 11.1035G) although log file size would still be stuck at 128M.

Infrastructure aside, it is possible for SELECTs to block INSERTs, UPDATEs, and DELETEs in InnoDB.

I wrote about this before

Best Answer

Related Solutions

MySQL – Optimizing InnoDB Default Settings

Mysql – Cannot write to thesql database while select query in process

Related Question