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.
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. Your test might be spilling over to disk much sooner than you realize.
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 |
±---------------±---------+
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) 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 and Created_tmp_tables
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.
+----------------------------------------------------------+-------+
| 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 |
+----------------------------------+---------+
I do have data in file_summary_by_instance. I have the same setup_consumers as you. Check setup_actors, and setup_instruments.
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 |
+----------------------------------------------------+---------+
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.