Geek Answers Handbook

Optimal configuration of MySQL temporary tables (memory tables)?

First of all, I am new to mysql optimization. The fact is that I have in my web application (about 400 queries per second) a query that I use GROUP BY, which I cannot avoid, and this is the reason for creating temporary tables. My configuration was:

max_heap_table_size = 16M  
tmp_table_size = 32M

Result: temporary table on disk percentage + - 12.5%

Then I changed my settings according to this post

max_heap_table_size = 128M  
tmp_table_size = 128M

Result: temporary table on disk percentage + - 18%

The results were not expected, I do not understand why.

Wrong tmp_table_size = max_heap_table_size? Should not increase size?

Query

SELECT images, id  
FROM classifieds_ads   
WHERE  parent_category = '1' AND published='1' AND outdated='0'
GROUP BY aux_order  
ORDER BY date_lastmodified DESC  
LIMIT 0, 100;

EXPLAIN

| 1 |SIMPLE|classifieds_ads | ref |parent_category, published, combined_parent_oudated_published, oudated | combined_parent_oudated_published | 7 | const,const,const | 67552 | Using where; Using temporary; Using filesort |
+5
1

" " EXPLAIN , . , .

temp , tmp_table_size max_heap_table_size.

Max_heap_table_size - , MEMORY, , .

Tmp_table_size - , , . max_heap_table_size. tmp_table_size , max_heap_table_size. .

, , :

mysql> show global status like 'Created%'; 
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 20    |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 43    |
+-------------------------+-------+

. 43 , 20 .

tmp_table_size max_heap_table_size, .

, ? , . , 95% , . 5% - , , .

, tmp_table_size max_heap_table_size . Created_tmp_disk_tables Created_tmp_tables, , : 95% ( - , ).

, MySQL , temp. , , . EXPLAIN , , .

Percona Server, MySQL . - . , .

+9

More articles:


All Articles