# Mysql 8.0 Slow temp table populate **URL:** \ **Category:** Percona Server for MySQL 8.0\ **Created:** [June 20, 2023, 2:01pm UTC](https://forums.percona.com/t/mysql-8-0-slow-temp-table-populate/22962 "2023-06-20T14:01:18Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\ **Post date:** [June 20, 2023, 2:01pm UTC](https://forums.percona.com/t/mysql-8-0-slow-temp-table-populate/22962/1 "2023-06-20T14:01:18Z")
Compared to mysql 5.7 inserting into a mysql 8.0.32 temporary table is 10-20% slower. This is for 1 million rows. CREATE temporary TABLE t1 (row\_num BIGINT UNSIGNED NOT NULL AUTO\_INCREMENT, fk varCHAR(20), id varCHAR(20), PRIMARY KEY(row\_num), INDEX idx\_1(fk,id), INDEX idx\_2(id)); INSERT INTO t1(id, fk) VALUES (’ 16001’,’ 162611\_2\_0’),(’ 16002’,’ 162612\_2\_0’),(’ … Have other people noticed this? Does anyone have any suggestions how to improve this or where I can look foe the bottleneck? Thanks. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [June 20, 2023, 2:56pm UTC](https://forums.percona.com/t/mysql-8-0-slow-temp-table-populate/22962/2 "2023-06-20T14:56:39Z")
Hey @Jamie_Downs, Temporary tables behave differently in 8.0. What was your temp engine in Percona 5.7? What were your settings for max\_heap\_size and temp\_table\_size? In 5.7, each temp table used its own “buffer” for data in memory before spilling to disk. But in 8.0 all temp tables now share a single buffer. Check out some of the [new temptable variables](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_temptable_max_mmap). Your test might be spilling over to disk much sooner than you realize. ---
**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\ **Post date:** [June 20, 2023, 3:03pm UTC](https://forums.percona.com/t/mysql-8-0-slow-temp-table-populate/22962/3 "2023-06-20T15:03:12Z")
> [@matthewb](#): > > porary tables behave differently in 8.0. What was your temp engine in Percona 5.7? What were your settings for max\_heap\_size and temp\_table\_size? In 5.7, each temp table used it Thanks Matthew. Our Percona 5.7 temp setting are as follows: ±--------------------±---------+ | Variable\_name | Value | ±--------------------±---------+ | max\_heap\_table\_size | 16777216 | ±--------------------±---------+ ±---------------±---------+ | Variable\_name | Value | ±---------------±---------+ | max\_tmp\_tables | 32 | | tmp\_table\_size | 16777216 | ±---------------±---------+ ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [June 20, 2023, 4:52pm UTC](https://forums.percona.com/t/mysql-8-0-slow-temp-table-populate/22962/4 "2023-06-20T16:52:48Z")
Per your config, in 5.7, you have a max 16MB temp table before spilling to disk. In 8.0 this was increased to a default of 1GB ( [`temptable_max_ram`](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_temptable_max_ram)) Somehow, in your setup, in 5.7, using less RAM is faster than 8.0 using more RAM. These are two servers that you can compare side-by-side? Same CPU/same disks? What temp table engine are you using in 5.7? According to the manual, as of 8.0.28, `tmp_table_size` also affects TempTable engine size, which has a default of 16MB, which matches your 5.7 setting. Try increasing this in 8.0. Also, watch these two variables on both 5.7 and 8.0 when running your test. Are you creating on-disk tables? [`Created_tmp_disk_tables`](https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Created_tmp_disk_tables) and [`Created_tmp_tables`](https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Created_tmp_tables) ---
**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\ **Post date:** [June 20, 2023, 8:09pm UTC](https://forums.percona.com/t/mysql-8-0-slow-temp-table-populate/22962/5 "2023-06-20T20:09:11Z")
Hi Matthew, Yes these servers can be compared side by side. They are ec2-instances. Configured with same storage ram etc… I will try increasing the `tmp_table_size` on 8.0 tomorrow. I will also look at Created\_tmp\_disk\_tables and Created\_tmp\_tables. I’m pretty sure we are going to disk as the tables are fairly large. Another angle I’ve been trying to compare is file\_io between the servers. I can’t figure out how to enable data collection for performance\_schema.file\_summary\_by\_instance. The table is empty. This is what I have switched on and restarted sql. ```auto +----------------------------------------------------------+-------+ | Variable_name | Value | +----------------------------------------------------------+-------+ | performance_schema | ON | | performance_schema_max_file_instances | -1 | +----------------------------------------------------------+-------+ +----------------------------------+---------+ | NAME | ENABLED | +----------------------------------+---------+ | events_stages_current | NO | | events_stages_history | NO | | events_stages_history_long | NO | | events_statements_cpu | NO | | events_statements_current | YES | | events_statements_history | YES | | events_statements_history_long | NO | | events_transactions_current | YES | | events_transactions_history | YES | | events_transactions_history_long | NO | | events_waits_current | YES | | events_waits_history | YES | | events_waits_history_long | YES | | global_instrumentation | YES | | thread_instrumentation | YES | | statements_digest | YES | +----------------------------------+---------+ ``` ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [June 22, 2023, 3:11pm UTC](https://forums.percona.com/t/mysql-8-0-slow-temp-table-populate/22962/6 "2023-06-22T15:11:13Z")
I do have data in `file_summary_by_instance`. I have the same setup\_consumers as you. Check setup\_actors, and setup\_instruments. ```auto mysql1-T1 mysql> select name, enabled from performance_schema.setup_instruments WHERE name like 'wait%' and enabled = 'YES'; +----------------------------------------------------+---------+ | name | enabled | +----------------------------------------------------+---------+ | wait/io/file/sql/binlog | YES | | wait/io/file/sql/binlog_cache | YES | | wait/io/file/sql/binlog_index | YES | | wait/io/file/sql/binlog_index_cache | YES | | wait/io/file/sql/relaylog | YES | | wait/io/file/sql/relaylog_cache | YES | | wait/io/file/sql/relaylog_index | YES | | wait/io/file/sql/relaylog_index_cache | YES | | wait/io/file/sql/io_cache | YES | | wait/io/file/sql/casetest | YES | | wait/io/file/sql/dbopt | YES | | wait/io/file/sql/ERRMSG | YES | | wait/io/file/sql/select_to_file | YES | | wait/io/file/sql/file_parser | YES | | wait/io/file/sql/FRM | YES | | wait/io/file/sql/load | YES | | wait/io/file/sql/LOAD_FILE | YES | | wait/io/file/sql/log_event_data | YES | | wait/io/file/sql/log_event_info | YES | | wait/io/file/sql/misc | YES | | wait/io/file/sql/pid | YES | | wait/io/file/sql/query_log | YES | | wait/io/file/sql/slow_log | YES | | wait/io/file/sql/tclog | YES | | wait/io/file/sql/trigger_name | YES | | wait/io/file/sql/trigger | YES | | wait/io/file/sql/init | YES | | wait/io/file/sql/SDI | YES | | wait/io/file/sql/hash_join | YES | | wait/io/file/mysys/proc_meminfo | YES | | wait/io/file/mysys/charset | YES | | wait/io/file/mysys/cnf | YES | | wait/io/file/keyring_file/keyring_file_data | YES | | wait/io/file/keyring_file/keyring_backup_file_data | YES | | wait/io/file/csv/metadata | YES | | wait/io/file/csv/data | YES | | wait/io/file/csv/update | YES | | wait/io/file/innodb/innodb_dblwr_file | YES | | wait/io/file/innodb/innodb_tablespace_open_file | YES | | wait/io/file/innodb/innodb_data_file | YES | | wait/io/file/innodb/innodb_log_file | YES | | wait/io/file/innodb/innodb_bmp_file | YES | | wait/io/file/innodb/innodb_temp_file | YES | | wait/io/file/innodb/innodb_arch_file | YES | | wait/io/file/innodb/innodb_clone_file | YES | | wait/io/file/innodb/meb::redo_log_archive_file | YES | | wait/io/file/myisam/data_tmp | YES | | wait/io/file/myisam/dfile | YES | | wait/io/file/myisam/kfile | YES | | wait/io/file/myisam/log | YES | | wait/io/file/myisammrg/MRG | YES | | wait/io/file/archive/metadata | YES | | wait/io/file/archive/data | YES | | wait/io/file/archive/FRM | YES | | wait/io/table/sql/handler | YES | | wait/lock/table/sql/handler | YES | | wait/lock/metadata/sql/mdl | YES | +----------------------------------------------------+---------+ ``` ---
**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\ **Post date:** [June 23, 2023, 2:42pm UTC](https://forums.percona.com/t/mysql-8-0-slow-temp-table-populate/22962/7 "2023-06-23T14:42:04Z")
> [@matthewb](#): > > have data in `file_summary_` Thanks for getting back to me @matthewb. I discovered performance\_schema\_max\_file\_instances had been set to 0. With regard to the wider discussion above: After extensive testing we have found it’s 10-15% slower inserting 1m rows into a temp table in 8.0. As suggested I compared Created\_tmp\_disk\_tables and Created\_tmp\_tables between the 5.7 and 8.0 servers. Surprisingly there no Created\_tmp\_disk\_tables on the 8.0 box. If you have any further suggestions that would be great.