Issue MariaDB High "created tmp disk tables"

[AdrianC]

Server operating system version

Almalinux 8.10

Plesk version and microupdate number

18.0.38

I am trying to optimize my ~large dbs (around 150 GB MySQL) to write less to SSD and more to memory, and I am comparing the variables on a Alma Linux 8.10 with Plesk 18.0.38 with MariaDB 10.3.28 vs a Rocky Linux machine with MySQL 8.x without any panel.

On Plesk the MySQL status variables always indicate that there are a lot of TMP tables written to disk instead of memory, and I get this on 2 plesk servers that I have.

Alma Linux 8.10 with Plesk:
--------------------------------
Created tmp disk tables: 537.3 k <<< THIS
Created tmp files: 5
Created tmp tables: 540.4 k

On Rocky Linux without any panel
----------------------------------------
Created tmp disk tables: 0 (zero) <<<
Created tmp files: 5
Created tmp tables: 1.4K

All servers have:
tmp_table_size = 8G
max_heap_table_size = 8G

I am thinking there is something inside plesk itself, not my databases, that causes it to write a lot of tables to disk ? Maybe some plesk service that are not well configured to write tables to memory ? I am just guessing but not sure where to look next.

 

[Bitpalast]

Try to give much more RAM to the innodb_buffer_pool_size. Depending on the RAM of your server, grant up to 70% of that RAM.

Just some sample values to reduce disk access to the database files:
innodb_buffer_pool_size = 96G
innodb_flush_method=O_DIRECT
table_open_cache = 10000
innodb_log_file_size = 24G
query_cache_size = 512M

Plus, check your software if it uses frequent LEFT JOIN or RIGHT JOIN statements. I recommend using INNER JOIN wherever possible, because this will reduce the size of the JOINs temporary data. Regular JOINs can lead to cartesian products of significant size which require temporary disk storage as they might not fit into RAM. INNER JOINs avoid large cartesian products.

 

[AdrianC]

Thanks Bitpalast.
I currently have these below values (some are close).
I will need to test the recommended values in a few days and I will post a reply.

innodb_buffer_pool_size = 90G
innodb_flush_method=fsync
table_open_cache = 2000
innodb_log_file_size = 50M
query_cache_size = 1M

 

[mow]

innodb_flush_method=O_DIRECT

Caveat: O_DIRECT can lead to problems with SoftRAID where the RAID might appear inconsistent (the data in that place would be garbage anyway though).

 

[AdrianC]

Try to give much more RAM to the innodb_buffer_pool_size. Depending on the RAM of your server, grant up to 70% of that RAM.

Just some sample values to reduce disk access to the database files:
innodb_buffer_pool_size = 96G
innodb_flush_method=O_DIRECT
table_open_cache = 10000
innodb_log_file_size = 24G
query_cache_size = 512M

Plus, check your software if it uses frequent LEFT JOIN or RIGHT JOIN statements. I recommend using INNER JOIN wherever possible, because this will reduce the size of the JOINs temporary data. Regular JOINs can lead to cartesian products of significant size which require temporary disk storage as they might not fit into RAM. INNER JOINs avoid large cartesian products.

I tried all the above variables...
- It made no difference in the amount of "Created tmp disk tables".
- I counted them over time, it increases around 110 to 200 per minute,
- The "Created tmp files" remains 5.
- This happens while I stopped all my scripts that write to database.
- I also tried clearing all variables from /etc/my.cnf and using default values made no difference.
- Created tmp disk tables is always around half of Created tmp tables, does this indicate anything ?
- "swappiness" is 10, and on most of my servers is 60, can this be related ?

What else cand I try ? I am thinking this is eider a plesk issue or a MariaDB 10.3 issue, maybe soeoone else can check these variables on their server ?

SHOW STATUS LIKE 'created_%';

Variable_name Value
Created_tmp_disk_tables 0
Created_tmp_files 5
Created_tmp_tables 1

OK, WAIT A MINUTE ....

If I run the above query in MySQL console or in PhpMyAdmin > SQL command, I get "Created_tmp_disk_tables 0" (and Created_tmp_tables 0)
But inside PhpMyAdmin > Status > All status variables I get "Created tmp disk tables 3.9 k"

Can that be a bug in PHPMyAdmin 5.2.1 ?

 

[Bitpalast]

Then it's likely the JOINs in the SQL syntax. They are the main cause for large temporary tables.

 

[mow]

If I run the above query in MySQL console or in PhpMyAdmin > SQL command, I get "Created_tmp_disk_tables 0" (and Created_tmp_tables 0)
But inside PhpMyAdmin > Status > All status variables I get "Created tmp disk tables 3.9 k"

Did you recently restart the mysql server?

Also, you should have a look at mysqltuner. Don't blindly follow the recommendations, though.

 

[AdrianC]

Did you recently restart the mysql server?
Also, you should have a look at mysqltuner. Don't blindly follow the recommendations, though.

I did restart the server multiple times while testing variables, the many "Created tmp disk tables" always started from zero when restarting and increased from there. But at this point I think it is a PhpMyAdmin bug.

I tried mysqltuner some years ago, I will give it another try becuase I know a bit more about mysql tuning variables

 

[mow]

SHOW STATUS LIKE 'created_%';

Variable_name Value
Created_tmp_disk_tables 0
Created_tmp_files 5
Created_tmp_tables 1

OK, WAIT A MINUTE ....

If I run the above query in MySQL console or in PhpMyAdmin > SQL command, I get "Created_tmp_disk_tables 0" (and Created_tmp_tables 0)
But inside PhpMyAdmin > Status > All status variables I get "Created tmp disk tables 3.9 k"

Can that be a bug in PHPMyAdmin 5.2.1 ?

No, it's just that you're listing the worker status.
SHOW GLOBAL STATUS LIKE 'created_%'; has a very different result.

 

[AdrianC]

Agh ... so I DO have a problem

Uptime 345860

SHOW STATUS LIKE 'created_%';

Created_tmp_disk_tables 0
Created_tmp_files 5
Created_tmp_tables 8

SHOW GLOBAL STATUS LIKE 'created_%';

Created_tmp_disk_tables 849552 <<<<<<<<<<<<<<< :/
Created_tmp_files 5
Created_tmp_tables 1378882