# \[HowTo\] Optimising MYSQL

**URL:** <https://forums.interworx.com/t/howto-optimising-mysql/9430>\
**Category:** Hacks / Tools / Tips / Tricks\
**Created:** [January 20, 2005, 12:59am UTC](https://forums.interworx.com/t/howto-optimising-mysql/9430 "2005-01-20T00:59:43Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![system](https://forums.interworx.com/uploads/default/original/2X/4/40ab17cc34b792a0172b48ad1333b9ba9299a9a1.png) [@system](https://forums.interworx.com/u/system)\
**Post date:** [January 20, 2005, 12:59am UTC](https://forums.interworx.com/t/howto-optimising-mysql/9430/1 "2005-01-20T00:59:43Z")

</div>

Hello,

I?d like to share with the interworx-cp community my knowledge about tuning / tweaking mysql.

First of all, sorry if my English is not perfect. Maybe some sentences may be difficult to understand. So do not hesitate to ask me for explanations :-p

Secondly, the default INTEWORX-CP mysql setup works very fine and is setup for all common usage.  
DO NOT CHANGE your setting without understanding what you do.  
Make a backup of your my.cnf before editing the /etc/my.cnf  
DO THESE CHANGES AT YOUR OWN RISKS.

My thread is only to help you to more well understand how tuning mysql.

Optimising mysql is very well commented on the net, and you?ll find huge information on how to do this. There is never ?best parameters?, the best parameters is those fits your needs, box hardware, mysql usage?  
So, I?ll not give the best parameters but rather how to define these ones. Make some tests, and you?ll quickly find your own parameters.

I?ll give you at the end of this post some web pointers which may help you.

There a lot of available parameters but only few one are very important to tweak your mysql box.

The most important variables are (for me, and it is not exhaustive)

> 

- max\_connections
- wait\_timeout
- thread\_cache\_size
- 
- table\_cache
- 
- key\_buffer\_size
- query\_cache\_size
- tmp\_table\_size

First of all, how to find your variable, and the mysql usage ?

\*VARIABLES

> 

from mysql :  
show variables;

or from command line :  
mysqladmin variables

\*PROCESS / STATUS

> 

from Mysql :  
show status;

or from command line  
mysqladmin ?i10 processlist extended-status

\*SOME USEFUL COMMAND FOR YOU BOX USAGE

> 

\>Top

\>ps ?axfu

\>vmstat 1

\* OPTIMISING MYSQL

To obtain the stat of your mysql server since it has been loaded, run mysqladmin processlist extended-status as mentionned above.

1 - The two most important variables : Table\_cache and Key\_buffer\_size

```
* If Opened_tables is big, then your table_cache variable is probably

```

too small.  
table\_cache 64  
Open\_tables 64  
Opened\_tables 544468

This is the first serious problem. “The table\_cache is the number of open  
tables for all threads. MySQL, being multi-threaded, may be running many  
queries on the table at one time, and each of these will open a table.”  
Therefore, even though we only have a few tables, we will need many more  
open\_tables.

The Opened\_tables value is high and shows the number of  
cache misses. Getting the table\_cache size correct is one of the two best  
things you can do to improve performance.

```
* If Key_reads is big, then your key_buffer_size variable is probably

```

too small. The cache hit rate can be calculated with  
Key\_reads/Key\_read\_requests.  
key\_buffer\_size 16M  
Key\_read\_requests 2973620399  
Key\_reads 8490571  
(cache hit rate = 0.0028)

?The key\_buffer\_size affects the size of the index buffers and the speed  
of index handling, particularly reading." The MySQL manual (and other sources) say that  
“Key\_reads/Key\_read\_request ratio should normally be \< 0.01.” This is the  
other most important thing to get correct. Here the value seems to be correct (\< 0.01)

Also check key\_write\_requests and key\_writes.  
The key\_writes/key\_writes\_request should normally be \< 1 (near 0.5 seems to be fine)

Here is a very interesting web pointer : [http://www.databasejournal.com/features/mysql/article.php/10897\_1402311\_3](http://www.databasejournal.com/features/mysql/article.php/10897_1402311_3)

2 - Others important settings are : Wait\_timeout, max\_connexion, thread\_cache

A little explanation :  
Generaly you have a lot of mysql process that are sleeping because wait\_timeout are not set low. So I make sure that the wait\_timeout is set to a very low value: 15 seconds (for me) . That means MySQL would close any connection that was idle for more than 15 seconds.

The problem is you also have to increment your max\_connexion (mine is set to 300) to be sure there is not a lot of idle clients holding connections and blocking out new clients from connecting and getting real work done.  
The pbm is that the box has to create new threads (MySQL is a multi-threaded server) at a very high rate. That may sucks up a measurable amount of CPU time.

So the solution is to use the Thread\_cache (from mysql doc) :  
?How many threads we should keep in a cache for reuse. When a client disconnects, the client’s threads are put in the cache if there aren’t more than thread\_cache\_size threads from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. (Normally this doesn’t give a notable performance improvement if you have a good thread implementation.) By examing the difference between the Connections and Threads\_created you can see how efficient the current thread cache is for you.?

```
* If Threads_created is big, you may want to increase the

```

thread\_cache\_size variable. The cache hit rate can be calculated with  
Threads\_created/Connections.  
thread\_cache\_size 0  
Threads\_created 150022  
Connections 150023

This is the second problem that should be fixed. A cache size of zero is the default for my-medium.cnf but the recommended size in my-large.cnf is 8.

you may try this formula : table\_cache = opened table / max\_used\_connection

3 - Finally, you may also have a look at : tmp\_table\_size and Handler\_read\_rnd / Handler\_read\_rnd\_next

```
* If Created_tmp_disk_tables is big, you may want to increase the

```

tmp\_table\_size variable to get the temporary tables memory-based instead  
of disk based.

tmp\_table\_size 32M  
Created\_tmp\_disk\_tables 3227  
Created\_tmp\_tables 159832  
Created\_tmp\_files 4444

Created\_tmp\_disk\_tables are the “number of implicit temporary tables on  
disk created while executing statements” and Created\_tmp\_tables are  
memory-based. Obviously it is bad if you have to go to disk instead of  
memory. About 2% of temp tables go to disk, which doesn’t seem too bad  
but increasing the tmp\_table\_size probably couldn’t hurt either.

- If Handler\_read\_rnd is big, then you probably have a lot of queries  
that require MySQL to scan whole tables or you have joins that don’t use  
keys properly.  
Handler\_read\_rnd 27712353  
Handler\_read\_rnd\_next 283536234

These values are high, that we could probably stand to improve  
the indexes and queries.

I hope this will help some of you to more understand how it is possible to optimise MYSQL to fit your needs, hardaware box, or mysql current usage.

Maybe there is others tweaks to perform, but I know well only these ones. I did setup using these ones on differents mysql box, and generally it did help us to increase performance without have to change hardware (our boxes have 2GB ram)

Pascal

---

<div class="post-metadata">

**Author:** ![system](https://forums.interworx.com/uploads/default/original/2X/4/40ab17cc34b792a0172b48ad1333b9ba9299a9a1.png) [@system](https://forums.interworx.com/u/system)\
**Post date:** [January 21, 2005, 6:28pm UTC](https://forums.interworx.com/t/howto-optimising-mysql/9430/2 "2005-01-21T18:28:48Z")

</div>

I forgot to tell you two or three importants things like :

Used MySQL memory = key\_buffer + max\_connections \* (join\_buffer + record\_buffer + sort\_buffer + thread\_stack + tmp\_table\_size)

Notice the max\_connexion and the multiplier.  
connexion increase = memory usage increase too.

Notice key\_buffer  
for a given memory :  
more you add mem to key buffer, less connexion is  
less is key buffer, more connexion is

If you change one of these settings for a high value, you system may swap.  
If you system swap, try lot decrease these values

Also, about table\_cache :  
Increasing the size of the table cache may really help you.  
But you must be careful not to make the value too large. All operating systems have a limit on the number “open file pointer” (sorry in french it is called pointer, maybe descriptors is the good translation) a single process may have.  
If MySQL tries to open a lot of files, the OS may refuse it and MySQL will generate error message in the error log.

Pascal

---

<div class="post-metadata">

**Author:** ![system](https://forums.interworx.com/uploads/default/original/2X/4/40ab17cc34b792a0172b48ad1333b9ba9299a9a1.png) [@system](https://forums.interworx.com/u/system)\
**Post date:** [March 14, 2006, 3:40pm UTC](https://forums.interworx.com/t/howto-optimising-mysql/9430/3 "2006-03-14T15:40:34Z")

</div>

Help!

Hi Pascal,

If got a huge problem, we have a dual xeon 2,8 ghz with 2gb of mem running only the sql server is sooo slow… i cant find the problem, i already have cepperated the database on 2 servers but still the same problem… Could you look to my statics and help me out what the problem is?!

Many thanks,

Christian (Netherlands)

#######################################################

Variable\_name Value  
Aborted\_clients 1632  
Aborted\_connects 446  
Binlog\_cache\_disk\_use 0  
Binlog\_cache\_use 0  
Bytes\_received 3526355276  
Bytes\_sent 880506088  
Com\_admin\_commands 40  
Com\_alter\_db 0  
Com\_alter\_table 6  
Com\_analyze 0  
Com\_backup\_table 0  
Com\_begin 0  
Com\_change\_db 821  
Com\_change\_master 0  
Com\_check 17  
Com\_checksum 0  
Com\_commit 0  
Com\_create\_db 0  
Com\_create\_function 0  
Com\_create\_index 0  
Com\_create\_table 2  
Com\_dealloc\_sql 0  
Com\_delete 4  
Com\_delete\_multi 0  
Com\_do 0  
Com\_drop\_db 0  
Com\_drop\_function 0  
Com\_drop\_index 0  
Com\_drop\_table 0  
Com\_drop\_user 0  
Com\_execute\_sql 0  
Com\_flush 19  
Com\_grant 0  
Com\_ha\_close 0  
Com\_ha\_open 0  
Com\_ha\_read 0  
Com\_help 0  
Com\_insert 25509  
Com\_insert\_select 0  
Com\_kill 0  
Com\_load 0  
Com\_load\_master\_data 0  
Com\_load\_master\_table 0  
Com\_lock\_tables 0  
Com\_optimize 1  
Com\_preload\_keys 0  
Com\_prepare\_sql 0  
Com\_purge 19  
Com\_purge\_before\_date 0  
Com\_rename\_table 0  
Com\_repair 0  
Com\_replace 0  
Com\_replace\_select 0  
Com\_reset 0  
Com\_restore\_table 0  
Com\_revoke 0  
Com\_revoke\_all 0  
Com\_rollback 0  
Com\_savepoint 0  
Com\_select 11355963  
Com\_set\_option 11306312  
Com\_show\_binlog\_events 0  
Com\_show\_binlogs 165  
Com\_show\_charsets 464  
Com\_show\_collations 464  
Com\_show\_column\_types 0  
Com\_show\_create\_db 0  
Com\_show\_create\_table 133  
Com\_show\_databases 64  
Com\_show\_errors 0  
Com\_show\_fields 228  
Com\_show\_grants 205  
Com\_show\_innodb\_status 0  
Com\_show\_keys 159  
Com\_show\_logs 0  
Com\_show\_master\_status 0  
Com\_show\_new\_master 0  
Com\_show\_open\_tables 5  
Com\_show\_privileges 0  
Com\_show\_processlist 80  
Com\_show\_slave\_hosts 0  
Com\_show\_slave\_status 0  
Com\_show\_status 11  
Com\_show\_storage\_engines 6  
Com\_show\_tables 872  
Com\_show\_variables 994  
Com\_show\_warnings 0  
Com\_slave\_start 0  
Com\_slave\_stop 0  
Com\_truncate 1  
Com\_unlock\_tables 0  
Com\_update 11208407  
Com\_update\_multi 0  
Connections 11306142  
Created\_tmp\_disk\_tables 0  
Created\_tmp\_files 3  
Created\_tmp\_tables 877  
Delayed\_errors 0  
Delayed\_insert\_threads 0  
Delayed\_writes 0  
Variable\_name Value  
Flush\_commands 1  
Handler\_commit 0  
Handler\_delete 2  
Handler\_discover 0  
Handler\_read\_first 263  
Handler\_read\_key 22408394  
Handler\_read\_next 32313  
Handler\_read\_prev 1058708072  
Handler\_read\_rnd 0  
Handler\_read\_rnd\_next 769312987  
Handler\_rollback 0  
Handler\_update 11180960  
Handler\_write 25550  
Key\_blocks\_not\_flushed 0  
Key\_blocks\_unused 13667  
Key\_blocks\_used 830  
Key\_read\_requests 94470278  
Key\_reads 2193  
Key\_write\_requests 25960  
Key\_writes 25910  
Max\_used\_connections 123  
Not\_flushed\_delayed\_rows 0  
Open\_files 88  
Open\_streams 0  
Open\_tables 64  
Opened\_tables 335  
Qcache\_free\_blocks 9  
Qcache\_free\_memory 16077208  
Qcache\_hits 51511  
Qcache\_inserts 11328247  
Qcache\_lowmem\_prunes 0  
Qcache\_not\_cached 27719  
Qcache\_queries\_in\_cache 29  
Qcache\_total\_blocks 83  
Questions 45258200  
Rpl\_status NULL  
Select\_full\_join 0  
Select\_full\_range\_join 0  
Select\_range 0  
Select\_range\_check 0  
Select\_scan 128879  
Slave\_open\_temp\_tables 0  
Slave\_running OFF  
Slave\_retried\_transactions 0  
Slow\_launch\_threads 0  
Slow\_queries 2  
Sort\_merge\_passes 0  
Sort\_range 0  
Sort\_rows 774  
Sort\_scan 32  
Table\_locks\_immediate 22517635  
Table\_locks\_waited 73303  
Threads\_cached 0  
Threads\_connected 1  
Threads\_created 11306141  
Threads\_running 1  
Uptime 1626337

---

<div class="post-metadata">

**Author:** ![system](https://forums.interworx.com/uploads/default/original/2X/4/40ab17cc34b792a0172b48ad1333b9ba9299a9a1.png) [@system](https://forums.interworx.com/u/system)\
**Post date:** [March 31, 2006, 11:43am UTC](https://forums.interworx.com/t/howto-optimising-mysql/9430/4 "2006-03-31T11:43:49Z")

</div>

Your runtime values don’t point necessarily to a bad mysql server configuration, but instead to poor query design. These values:

Handler\_read\_rnd\_next 769312987  
Key\_read\_requests 94470278  
Select\_scan 128879  
Created\_tmp\_tables 877

Indicate that you have several queries that either:

- are not using indexes
- are performing full table scans to retrieve values
- are not performing lossless joins

You need to schedule a nice evening with your SQL console, your SQL queries and run EXPLAIN to discover which are creating these temp tables, and where you could use indexes to prevent your mysql server from scanning entire tables to locate single potential keys.

Use the mysql slow query log, and the log queries without indexes directives and then examine your logs to find the culprit.

There’s a saying in our field, tweaking servers can improve your performance by ten, tweaking the queries can improve it by ten thousand.

You might want to add skip-locking to your mysqld section, that’s a huge lock value for what I assume are myisam tables (which perform table locking). If you’re using InnoDB and have set it to a finer grained lock, you can ignore this last statement. If you are not using InnoDB, turn it off.

Disk access also plays a great part, make sure you have hdparm set to have DMA access on drives that can support it. Further, if something is chewing up your filesystem time, ie. scripts that have bad code that loop file locks, this could be consuming your file descriptors. Tweaking your filesystem is an entirely different ballgame - but should be considered. Lots of good tutorials available on the net.