Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are running a site (Moodle) that the users currently find slow. I think I have tracked down the problem to MySQL creating temporary tables on disk. I watch the variable created_tmp_disk_tables in Mysql Workbench server administration and the number increases with roughly 50 tables/s. After a days usage, created_tmp_disk_tablesis >100k. Also, the memory does not seem to be released. The usage keeps increasing until the system becomes pretty much unusable and we have to re-start MySQL. I need to re-start it almost every day and it begins with using about 30-35% of available memory and finishing the day with 80%.

I have no blobs in the database and no control over the queries either so I can't attempt to optimise them. I have also used the Percona Confirguration Wizard to generate a configuration file but that my.ini didn't solve my problem either.

Questions

  1. What should I change to stop MySQL from creating temporary tables on disk? Are there settings I need to change? Should I throw more memory at it?

  2. How can I stop MySQL from eating up my memory?

Edit

I enabled slow_queries log and discovered that the query SELECT GET_LOCK() was logged as slow. A quick search revealed that I had allowed persistent connections in the PHP configuration (mysqli.allow_persistent = ON). I turned this off. This reduced the rate at which MySQL consumes memory.It is still creating temporary tables though.

I also checked that the key_buffer size is large enough. I looked at the variable key_writes. This should be zero. If not, increase the key_buffer_size.I have zero key_reads and zero key_writes so I assume that the key_buffer_size is large enough.

I increased the tmp_table_size and max-heap-table-size to 1024M as an increase in created_tmp_disk_tables may indicate that the tables can't fit in memory. This didn't solve it.

Ref: http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/

Edit 2

If you see many sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value. I had 2 sort_merge_passes in an hour so I consider the sort_buffer_size to be large enough.

Ref: Mysql Manual on sort_buffer_size

Edit 3

I have modified the sort and join buffers as suggested by @RolandoMySQLDBA. The result is displayed in the table below but I think the created_tmp_tables_on_disk is still high. I restarted the mysql server after I changed the value and checked the created_tmp_tables_on_disk after a day (8h) and calculated the average. Any other suggestions? It seems to me that there is something that doesn't fit inside some kind of container but I can't work out what it is.

+---------------------+-------------+-------------+--------------------+
| Tmp_table_size,     | Sort_buffer | Join_buffer | No of created      |
| max_heap_table_size |             |             | tmp_tables on disk |
+---------------------+-------------+-------------+--------------------+
| 125M                | 256K        | 256K        |  100k/h            |
+---------------------+-------------+-------------+--------------------+
| 125M                | 512K        | 512K        |  100k/h            |
+---------------------+-------------+-------------+--------------------+
| 125M                | 1M          | 1M          |  100k/h            |
+---------------------+-------------+-------------+--------------------+
| 125M                | 4M          | 4M          |  100k/h            |
+---------------------+-------------+-------------+--------------------+   



This is my configuration:

+-----------------------+-----------------------+
|DATABASE SERVER        |WEB SERVER             |
+-----------------------+-----------------------+
|Windows Server 2008 R2 |Windows Server 2008 R2 |
+-----------------------+-----------------------+
|MySQL 5.1.48           |IIS 7.5                |
+-----------------------+-----------------------+
|4 Core CPU             |4 Core CPU             |
+-----------------------+-----------------------+
|4GB RAM                |8GB RAM                |
+-----------------------+-----------------------+

Additional information

+--------------------+---------+
|PARAM               |VALUE    |
+--------------------+---------+
|Num of tables in Db |361      |
+--------------------+---------+
|Size of database    |2.5G     |
+--------------------+---------+
|Database engine     |InnoDB   |
+--------------------+---------+
|Read/write ratio    |3.5      |
|(Innodb_data_read/  |         |
|innodb_data_written)|         |
+--------------------+---------+
|Avg table size      |15k rows |
+--------------------+---------+
|Max table size      |744k rows|
+--------------------+---------+

This setup was given to me so I have limited control over it. The web server is using very little CPU and RAM so I have excluded that machine as a bottleneck. A majority of the MySQL settings originates from a config auto-generation tool.

I have monitored the system using PerfMon over a few representative days. From that, I conclude that it is not the OS that is swapping to disk.

My.ini

[client]
port=3306
[mysql]
default-character-set=utf8

[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.1/"
datadir="D:/DBs/Data/"
default-character-set=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=125
query_cache_size=350M
table_cache=1520
tmp_table_size=125M
table-definition-cache= 1024
max-heap-table-size= 32M
thread_cache_size=38

MyISAM Specific options
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=125M
key_buffer_size=55M
read_buffer_size=1024K
read_rnd_buffer_size=256K
sort_buffer_size=1024K
join_buffer_size=1024K


INNODB Specific options
innodb_data_home_dir="D:/DBs/"
innodb_additional_mem_pool_size=32M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_buffer_pool_size=2G
innodb_log_file_size=407M
innodb_thread_concurrency=8
share|improve this question
    
You should optimize offending JOINS in combination with GROUP BY / ORDER BY these are most likely to cause an disk based temporary table.. Best option to track these slow querys is slow query log or profiling... –  Raymond Nijland Nov 12 '13 at 18:48
    
Ah, but I can't optimise the queries because they are part of a core package that I can't change. There are many who run Moodle on MySQL and I have got some hints/teaks/tips from those people but none of them have worked for me. –  user30431 Nov 12 '13 at 18:53
    
Why is the database on a server with less RAM? You write yourself that your web server doesn't need much RAM ... –  edze Nov 12 '13 at 19:19
    
That is what I was given. It can be changed though. Would you recommend that? 8G on the database server? I was tasked with investigating why it runs slowly so I just inherited the setup. –  user30431 Nov 12 '13 at 19:22
1  
If you cant optimize queries (you will never stop the temporary tables) then spend money on better hardware... please buy an SSD disk or lots off RAM and use software to configure an RAM disk... and configure tmpdir like in @RolandoMySQLDBA 's answer. –  Raymond Nijland Dec 4 '13 at 18:38
show 11 more comments

2 Answers

up vote 2 down vote accepted

Looking at the my.ini, I have two suggestions

SUGGESTION #1

I would bump up the following settings in your my.ini

sort_buffer_size=4M
join_buffer_size=4M

This will make some joins and sort stay in memory. Of course, once a JOIN or an ORDER BY needs more than 4M, it will page to disk as a MyISAM table.

If you cannot login as root@localhost, then restart mysql with

C:\> net stop mysql
C:\> net start mysql

If you can login as root@localhost, you do not have to restart mysql to use these settings.

Just run this in the MySQL client:

SET @FourMegs = 1024 * 1024 * 4;
SET GLOBAL sort_buffer_size = @FourMegs;
SET GLOBAL join_buffer_size = @FourMegs;

SUGGESTION #2

Since your Data is on Drive D:, you may have Disk I/O on Drive C:.

Please run this query:

mysql> show variables like 'tmpdir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tmpdir        | C:\Windows\TEMP |
+---------------+-----------------+
1 row in set (0.00 sec)

Since I run mysql on my Desktop with defaults, my temp tables are being written to Drive C:. If Drive D is a better disk than Drive C:, perhaps you can map temp tables to Drive D: by setting tmpdir in my.ini as follows:

tmpdir="D:/DBs/"

You will have to restart mysql since tmpdir is not a dynamic variable.

Give it a Try !!!

UPDATE 2013-11-29 10:09 EST

SUGGESTION #3

Given the fact that MySQL is running in Windows and you cannot touch the queries in the core package, I have two ideas tat must be done together.

IDEA #1 : Move the Database to a Linux Machine

You should be able to

  • Setup a Linux machine
  • Install MySQL on the Linux machine
  • Enable Binary Logging for MySQL in Windows
  • mysqldump the database to a text SQL file
  • Load SQL file to MySQL running in Linux
  • Setup replication from MySQL/Windows to MySQL/Linux

IDEA #2 : Reconfigure Moodle to point to the Linux Machine

Moodle was designed for LAMP in the first place. Just change the config files to point to the Linux machine instead of localhost.

Here is a link to an old Moodle 2.3 doc on setting up MySQL : http://docs.moodle.org/23/en/Installing_Moodle#Create_an_empty_database

I am sure the latest docs are available as well.

What is the Point of Moving the Database to Linux ???

How does this help the temp table situation ???

I would then suggestion setting up a RAM disk as the target folder for your temp tables

Temp table creation will still happen, but it will be written to RAM rather than disk. reducing Disk I/O.

UPDATE 2013-11-29 11:24 EST

SUGGESTION #4

I would suggest revisiting SUGGESTION #2 with a fast RAID-0 disk (32+ GB), configuring it as Drive T: (T for Temp). After installing such a disk, add this to my.ini:

[mysqld]
tmpdir="T:\"

MySQL restart would be required, using

net stop mysql
net start mysql

BTW I said RAID-0 on purpose so that you can get good write performance over a RAID-1, RAID-10. A tmp table disk is not something I would make redundant.

Without optimizing the queries as @RaymondNijland has been commenting on, you cannot reduce the temp table creation count in any way. SUGGESTION #3 and SUGGESTION #4 offer speeding up temp table creation and temp table I/O as the only alternative.

share|improve this answer
    
Suggestion #1: This actually made it worse.Changing those to 4M created 100k temp tables on disk per hour. I then lowered it to 1M and that resulted in ~75k temp tables on disk per hour. I increased the sort and join buffer -> temp tables increase? This tells us something, doesn't it? Maybe there is anything else I need to change in conjunction with the sort and join buffers? –  user30431 Nov 19 '13 at 10:23
    
Suggestion 2: The C and D partitions are on the same physical disk so they have the same performance. –  user30431 Nov 19 '13 at 10:25
    
I just noticed that tmp_table_size and max_heap_table_size are different. They should be the same the number. Both should be 125M. –  RolandoMySQLDBA Nov 20 '13 at 15:30
    
Should I follow Suggestion #1 and change tmp_table_size and max_heap_table_size, all in one go? –  user30431 Nov 20 '13 at 15:38
    
max_heap_table_size is already 125M. Just run SET GLOBAL tmp)table_size = 1024 * 1024 * 125;. Then, see how things are, if they imptove, then set tmp_table_size=125M to /etc/my.cnf. –  RolandoMySQLDBA Nov 20 '13 at 16:59
show 2 more comments

I answer my own question here for completeness

I will select @RolandoMySQLDBA as the preferred answer because it gave me the most hints even though it didn't actually solve my problem.

Below are the results of my investigation

Conclusion

MySQL on Windows just creates lots of temporary tables and tuning MySQL by modifying the content of the configuration files did not help.

Details

The table details the parameters I have modified in my.ini respectively before executing any queries. MySQL was re-started between each test.

I used the my.ini found in the original question as a template and I then changed the value of the parameters one by one according to the table below.

I used JMeter to generate 100 concurrent web requests (as that represented our usage) repeated 10 ten times. Each Test consisted thus of 1000 requests in total. This resulted in subsequent database calls. This showed that MySQL would create lots of temporary tables regardless of what configuration parameters we changed.

+----+------------+-------+---------------+------------+
|Test|Parameter   |Value  |NumOfTempTables|Db Max Conn |
+----+------------+-------+---------------+------------+
| 1  |key_buffer_ | 25M   | 30682         | 29         |
|    |size        |       |               |            | 
+----+------------+-------+---------------+------------+
| 2  |key_buffer_ | 55M   | 30793         | 29         |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 3  |key_buffer_ | 100M  | 30666         | 28         |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 4  |key_buffer_ | 125M  | 30593         | 24         |
|    |size        |       |               |            | 
+----+------------+-------+---------------+------------+
| 5  |query_cache_| 100M  | 30627         | 32         |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 6  |query_cache_| 250M  | 30761         | 26         |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 7  |query_cache_| 500M  | 30864         | 83*        |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 8  |query_cache_| 1G    | 30706         | 75*        |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 9  |tmp_table_  | 125M  | 30724         | 31         |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 10 |tmp_table_  | 250M  | 30689         | 90*        |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 11 |tmp_table_  | 500M  | 30792         | 28         |
|    |size        |       |               |            |  
+----+------------+-------+---------------+------------+
| 12 |Sort_buffer&| 256K  | 30754         | 28         |
|    |Join_buffer |       |               |            |
+----+------------+-------+---------------+------------+
| 13 |Sort_buffer&| 512K  | 30788         | 30         |
|    |Join_buffer |       |               |            | 
+----+------------+-------+---------------+------------+
| 14 |Sort_buffer&| 1M    | 30788         | 28         |
|    |Join_buffer |       |               |            | 
+----+------------+-------+---------------+------------+
| 15 |Sort_buffer&| 4M    | 30642         | 35         |
|    |Join_buffer |       |               |            |
+----+------------+-------+---------------+------------+
| 16 |innodb-     | 1G    | 30695         | 33         |
|    |buffer-     |       |               |            |
|    |pool-size   |       |               |            |
+----+------------+-------+---------------+------------+
| 17 |innodb-     | 2G    | 30791         | 28         |
|    |buffer-     |       |               |            |
|    |pool-size   |       |               |            | 
+----+------------+-------+---------------+------------+
| 18 |innodb-     | 3G    | 30719         | 34         |
|    |buffer-     |       |               |            |
|    |pool-size   |       |               |            |  
+----+------------+-------+---------------+------------+

*Average of three runs

The images below depict the amount of memory and CPU the database server required for the different configurations. The black lines indicate the minimum and maximum values and the blue bars indicate start and end values. The maximum memory was 4096M as indicated in the question.

Memory Usage CPU Usage

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.