Optimiser un serveur MySQL

25.06.2008 0

Voici quelques informations et scripts bien utiles pour optimiser un serveur MySQL:

http://rackerhacker.com/mysqltuner/

Ce script produit des recommandations après avoir analysé la configuration du serveur


ovh ~ # ./mysqltuner.pl

>> MySQLTuner 0.9.1 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[OK] You have the latest version of MySQLTuner
[OK] Currently running supported MySQL version 5.0.44-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 428K (Tables: 79)
[!!] InnoDB is enabled but isn't being used
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 9

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 20h 44m 17s (282K q [0.557 qps], 67K conn, TX: 102M, RX: 38M)
[--] Reads / Writes: 66% / 34%
[--] Total buffers: 1.6M per thread and 58.0M global
[OK] Maximum possible memory usage: 213.5M (21% of installed RAM)
[OK] Slow queries: 0% (0/282K)
[OK] Highest usage of available connections: 10% (10/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/315.0K
[OK] Key buffer hit rate: 99.9%
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0%
[OK] Temporary tables created on disk: 4%
[!!] Thread cache is disabled
[!!] Table cache hit rate: 12%
[OK] Open file limit used: 12%
[OK] Table locks acquired immediately: 99%

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Add skip-bdb to MySQL configuration to disable BDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)
table_cache (> 64)

http://www.day32.com/MySQL/

Cette page regroupe plusieurs scripts dont certains surveillent une réplication MySQL. Voici un exemple de résultat sur le même serveur que précédemment


-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.0.44-log i686

Uptime = 5 days 20 hrs 49 min 18 sec
Avg. qps = 0
Total Questions = 282259
Threads Connected = 6

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 0 out of 282280 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.

BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_logs_days will allow you to remove old binary logs automatically
See http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html

WORKER THREADS
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 1
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 6
Historic max_used_connections = 10
The number of used connections is 10% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

MEMORY USAGE
Max Memory Ever Allocated : 57 M
Configured Max Per-thread Buffers : 158 M
Configured Max Global Buffers : 42 M
Configured Max Memory Limit : 200 M
Physical Memory : 997.33 M
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 448 K
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 712
Key buffer fill ratio = 1.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

SORT OPERATIONS
Current sort_buffer_size = 512 K
Current read_rnd_buffer_size = 508 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly

OPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 64 tables
You have a total of 96 tables
You have 64 open tables.
Current table_cache hit rate is 8%, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 2541 temp tables, 4% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 252 K
Current table scan ratio = 1 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 5629
Your table locking seems to be fine

http://hackmysql.com/mysqlreport

Ce script dispose d’un guide comple disponible ici http://hackmysql.com/mysqlreportguide qui permet d’approfondir les résultats obtenus.


MySQL 5.0.44-log uptime 5 20:52:50 Wed Jun 25 14:51:12 2008

__ Key _________________________________________________________________
Buffer used 150.00k of 16.00M %Used: 0.92
Current 1.87M %Usage: 11.67
Write hit 24.14%
Read hit 99.85%

__ Questions ___________________________________________________________
Total 282.76k 0.6/s
Com_ 184.34k 0.4/s %Total: 65.19
DMS 144.44k 0.3/s 51.08
-Unknown 113.28k 0.2/s 40.06
COM_QUIT 67.25k 0.1/s 23.79
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 144.44k 0.3/s 51.08
SELECT 96.55k 0.2/s 34.14 66.84
REPLACE 40.42k 0.1/s 14.30 27.98
INSERT 6.53k 0.0/s 2.31 4.52
UPDATE 681 0.0/s 0.24 0.47
DELETE 267 0.0/s 0.09 0.18
Com_ 184.34k 0.4/s 65.19
admin_comma 113.73k 0.2/s 40.22
change_db 67.88k 0.1/s 24.00
show_variab 760 0.0/s 0.27

__ SELECT and Sort _____________________________________________________
Scan 6.44k 0.0/s %SELECT: 6.67
Range 0 0/s 0.00
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 1.51k 0.0/s
Sort range 993 0.0/s
Sort mrg pass 0 0/s

__ Table Locks _________________________________________________________
Waited 26 0.0/s %Total: 0.02
Immediate 146.48k 0.3/s

__ Tables ______________________________________________________________
Open 64 of 64 %Cache: 100.00
Opened 860 0.0/s

__ Connections _________________________________________________________
Max used 10 of 100 %Max: 10.00
Total 67.27k 0.1/s

__ Created Temp ________________________________________________________
Disk table 112 0.0/s
Table 2.55k 0.0/s Size: 32.0M
File 5 0.0/s

__ Threads _____________________________________________________________
Running 1 of 6
Cached 0 of 0 %Hit: 0
Created 67.27k 0.1/s
Slow 0 0/s

__ Aborted _____________________________________________________________
Clients 34 0.0/s
Connects 11 0.0/s

__ Bytes _______________________________________________________________
Sent 103.02M 203.1/s
Received 38.68M 76.3/s

__ InnoDB Buffer Pool __________________________________________________
Usage 2.80M of 16.00M %Used: 17.48
Read hit 100.00%
Pages
Free 845 %Total: 82.52
Data 178 17.38 %Drty: 0.00
Misc 1 0.10
Latched 0 0.00
Reads 1.40k 0.0/s
From file 0 0/s 0.00
Ahead Rnd 0 0/s
Ahead Sql 0 0/s
Writes 1.17k 0.0/s
Flushes 189 0.0/s
Wait Free 0 0/s

__ InnoDB Lock _________________________________________________________
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 0 0/s
Writes 38 0.0/s
fsync 16 0.0/s
Pending
Reads 0
Writes 0
fsync 0

Pages
Created 178 0.0/s
Read 0 0/s
Written 189 0.0/s

Rows
Deleted 0 0/s
Inserted 0 0/s
Read 0 0/s
Updated 0 0/s


 Olivier PIERRE, freelance au Luxembourg Analyste développeur PHP/MySQL/jQuery et administrateur système Linux spécialiste Gentoo


Les messages similaires