Hello, please sign in or register
You are here: Home

Mysql performance tuning

I ran the function top on my linux server today. The results were worrying. The mysql service mysqld was frequently maxing out the CPU at 99.9%. The site is receiving several thousand unique visitors who total up over 100'000 hits to the database per day. This article documents my efforts to tweak the server into a super-duper server and handle the load.

Monitoring

A great place to start. For command shell there's top which will display you mysql statistics and is distributed in Fedora 4.

mytop

Additionallyfor linux kernels 2.2-2.4 install mytop which displays running results of your mysql server and can be installed [CODE=command shell] # http://jeremy.zawodny.com/mysql/mytop # To install mytop... wget http://dll.elix.us/mytop-1.4.tar.gz tar -zxvf mytop-1.4.tar.gz cd mytop-1.4 perl Makefile.PL make make test make install # you can now monitor performance from command line by typing.. mytop [/CODE]

SHOW STATUS

MySQL stores data about its current status on the systems. Visit Server Status Variables You can access this via the command shell e.g.

[CODE=shell] mysqladmin variables // or mysqladmin extended-status [/CODE] or by mysql API [CODE=mysql] SHOW STATUS SHOW STATUS LIKE 'Key%'; [/CODE]



+--------------------------+------------+

| Variable_name            | Value      |

+--------------------------+------------+

| Key_read_requests        | 10004033   | 

| Key_reads                | 90690     | 

| Key_write_requests       | 7079     | 

| Key_writes               | 87    |

+--------------------------+------------+

Find out how often mysql reads index's from cache, rather than from the table.

[CODE=maths] 100 - ( ( Key_reads / Key_read_requests ) * 100 ) // 100 - ( 90690 / 10004033 )*100) // 99.09 % [/CODE]

The closer this value is to 100 the better, more effiecient the use of memory is.

Slow Query Logging

Another good place to start is by logging the query times. So you can answer the question, "Should i rewrite my SQL statements, or sort my table index's out".

MySQL supposedly has a built in slow query logger that be activated by including the following code into your MySQL's my.ini or my.cnf... albeit this didn't work for me. [CODE=/etc/my.cnf] ... [mysqld] ... log-slow-queries=/var/log/mysql_slow_query_log long_query_time=10 ... [/CODE]

Alternatively you can restart your mysqld server with the command ./mysqld start --log-slow-queries /var/log/mysql_slow_query_log --long_query_time 10

ADOdb ( that huge memory leak) database application layer for PHP and Python, Has a collection of monitoring functions. First initiate your logging "LogSQL()".... then create a script alike.

[CODE=database-stats.php] UI($pollsecs=5); //print $perf->HealthCheck(); //$perf->Poll($pollSecs=5); print $perf->SuspiciousSQL($numsql=10); print $perf->ExpensiveSQL($numsql=10); print $perf->InvalidSQL($numsql=10); print $perf->Tables($orderby=1); print $perf->CPULoad(); ?> [/CODE]

MySQL configuration Options

Type SHOW VARIABLES in your database API, a list of all the available variables will be displayed

Key_buffer_size

This sets the memory allocation to index keys. The key buffer is where MySQL caches index blocks for MyISAM tables. Whenever a key is accessed MySQL will check to see if it is cached in memory or not. The larger allocation the more index's can be stored in memory. Once the key_buffer size is filled the older cached items are dropped out. My.cnf displays a value of 8388600, or 8mb

Sort Buffer

Sort buffer as the name suggests is a buffer assigned to answering queries that invoke sorting data i.e.the SQL statement includes ORDER BY. It is also used by queries which use GROUP BY. If the value of the memory allocated to sort buffer is increased it can dramatically reduce the tine taken to sort large sets of data. [CODE=/etc/my.cnf]... set-variable = sort_buffer=8M ... [/CODE]

my.cnf

This is my current mysql configuration file - my.cnf, or my.ini on windows.

[CODE=/etc/my.cnf] [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # Stopwords use, reduce word length ft_stopword_file='/var/lib/mysql/ft_stop_word.txt' ft_min_word_len = 1 # Caching query-cache-type = 1 query-cache-size = 20M master-host=igweb04 master-user=linuxrepl master-password=slavepwd server-id=2 #master-port=3306 master-connect-retry=60 replicate-do-db=booksharp replicate-ignore-db=books replicate-ignore-db=booksbeta [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [/CODE]

External Reference

http://www.linux-mag.com/2001-12/mysql_01.html

Comments

pGFEvFHstISuMoEyS
That's not just logic. That's raelly sensible.
Created 08/08/11
eTbaJYWTdQmXEfB
PiYcco vfwprfsxsqln
Created 09/08/11
SUIqegVfukVQbe
Dv1Ond , [url=http://hdhngutkrouv.com/]hdhngutkrouv[/url], [link=http://wewfaortwckd.com/]wewfaortwckd[/link], http://kmzgqzqcwbaz.com/
Created 09/08/11
sOPDHalwoRFRHnb
ChetbJ lmdiyuqiaujf
Created 11/08/11
CrpndsPOcDAEHfwldZM
3rHJcH , [url=http://jspggpgtwpek.com/]jspggpgtwpek[/url], [link=http://npcbrlreuzwu.com/]npcbrlreuzwu[/link], http://rckhoeguffke.com/
Created 16/08/11
clibin
Tyrion 5c cases watched her ray ban sunglasses read. His timber...
Created 18/04/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
wanglili
kate spade outlet online store ugg boots polo ralph...
Created 21/08/17
Title*
Comment

Prove you are not a robot

To prove you are not a robot, please type in the six character code you see in the picture below
Security confirmation codeI can't see this!
Contact
Name*
Email never shown*
Home Page

Author

Andrew Dodson
Since:Feb 2007

Comment | flag

Categories

Bookmark and Share