MySQL always writes simple temporary/memory tables to disk (both tmp_table_size and max_heap_table_size are set to 8GB)

memoryMySQLmysql-5.5performancewindows

I'm struggling with this issue and just can't figure out why this is happening.

CREATE TABLE sitecache._test ENGINE=MEMORY SELECT * FROM website.gallery_likes WHERE 1=0;

Every time I execute this query, the Created tmp disk tables server variable increases immediately by 5. The same thing happens for CREATE TEMPORARY TABLE ... and also for creating a table with a structure definition instead of copying the structure from another table.

Structure of website.gallery_likes:

CREATE TABLE gallery_likes (
  photoid mediumint(8) unsigned NOT NULL,
  userid int(10) unsigned NOT NULL,
  timestamp int(10) unsigned NOT NULL,
  KEY photoid (photoid,userid)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

As you can see there's no TEXT and no BLOB fields, it's a fairly simple table. If I then dump a large set of data into that temporary/memory table, it takes several minutes to complete and I can see how mysqld is writing a lot of data to a temporary file in the C:\Windows\Temp directory.

tmp_table_size and max_heap_table_size are both set to 8GB (and confirmed via SHOW VARIABLES).

MySQL 5.6.17 x64 on Windows Server 2012 R2 x64 with 64GB RAM.

Config:

[client]
no-beep
port=3306

[mysql]
default-character-set=latin1

[mysqld_safe]
open-files-limit=16384

[mysqld]
log-bin="D:/MySQL Blackhole/master_replication.log"
sync_binlog=0
binlog-do-db=...
binlog-ignore-db=...
...edited...
binlog-format=STATEMENT
expire_logs_days=1
server-id=1
ft_min_word_len=3
innodb_ft_min_token_size=3
port=3306
datadir="D:/MySQL/data/"
character-set-server=latin1
default-storage-engine=MYISAM
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=NONE
general-log=0
general_log_file="MYSQL_SERVER.log"
slow-query-log=0
slow_query_log_file="MYSQL_SERVER-slow.log"
long_query_time=10
log-error="MYSQL_SERVER.err"
max_connections=1024
query_cache_size=0
table_open_cache=4096
tmp_table_size=8G
max_heap_table_size=8G
thread_cache_size=64
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=256M
key_buffer_size=8192M
read_buffer_size=2M
read_rnd_buffer_size=128M
sort_buffer_size=8M
innodb_flush_log_at_trx_commit=0
innodb_flush_log_at_timeout=1
innodb_log_buffer_size=32M
innodb_buffer_pool_size=32G
innodb_log_file_size=8G
innodb_thread_concurrency=0
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=0
innodb_open_files=4096
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=strict_crc32
innodb_adaptive_hash_index=ON
back_log=80
flush_time=0
join_buffer_size=8M
max_allowed_packet=16M
max_connect_errors=10
open_files_limit=16384
query_cache_type=0
table_definition_cache=2500
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

(query_cache_size=0 and default-storage-engine=MYISAM on purpose)

This happens on the production system. I have MySQL 5.6.16 x64 on my development machine (Windows 8.1 x64) running with the exact same databases and almost the same config (smaller innodb buffer pool, tmp_table_size at 512M, no replication) and this doesn't happen here. Something environmental must be different.

I've now read the documentation multiple times but I just don't see any connection as to why this happens. The table is fairly simple with no columns that are more than 512 bytes long.
Full disclosure: I already posted it on StackOverflow but nobody responded.

Best Answer

Okay, in case anyone stumbles upon a similar problem and Google directs them here:
I don't exactly figured out why, but sort of figured out what causes it, phpMyAdmin was the culprit.

Issuing the same commands directly in the mysql command line did not cause the temporary tables to be written to disk.
Now, I also have phpMyAdmin on my development machine and it's not an issue here. But I'm running PHP as a module in Apache, whereas on the production server it's IIS with PHP running as FastCGI. So some configuration or limitation must've caused this, not exactly sure.

Related Question