Мониторинг/тюнинг MySQL

Категория: / DEV Блог / Сервер (FreeBSD)
Логируем сложные запросы

log-slow-queries=/var/log/mysql/slow.log
long_query_time=5
log-queries-not-using-indexes


Перезапуск демона mysqld после падения

Сыплется mysql на вашем vds?
Решение хоть и неправильное - но помочь может.
Добавляем скрипт в кронтаб

#!/bin/sh
 
run_="/usr/local/etc/rc.d/mysql-server start"
test_=`ps -ax | grep mysqld | grep -v grep`
 
if [ -n "$test_" ]
then
    echo 'running...'
else
    echo 'not running, starting...'
    $run_
fi


Конфигурация для сервера с небольшим количеством памяти (~64Мб)

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 2M
max_allowed_packet = 1M
table_cache = 32
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
net_buffer_length = 256K
thread_stack = 64K
query_cache_size= 2M
 
skip-networking
server-id       = 1
 
# Uncomment the following if you want to log updates
#log-bin=mysql-bin
# Disable Federated by default - uncomment on >4.1
#skip-federated
 
# Uncomment the following if you are NOT using BDB tables
skip-bdb
skip-innodb


Оптимизируем структуру баз

Для myisam таблиц -
/usr/local/bin/myisamchk --fast --silent /var/db/mysql/*/*.MYI

Если таблиц много, команда завершится с ошибкой - too many arguments. Запускаем хаком -
find /usr/local/mysql/ -type f -name '*.MYI' -exec myisamchk --force --fast {} \;  > /dev/null 2>&1

myisamchk не лочит таблицы, поэтому лучше использовать более универсальный метод -
/usr/local/bin/mysqlcheck --repair --analyze --optimize --all-databases --auto-repair -u root -p${pass}


Утилита MyTop

/usr/ports/databases/mytop

Конфиг ~/.mytop

user=root
pass=password
host=localhost
delay=2
socket=/tmp/mysql.sock
batchmode=0
header=1
color=1
idle=1


Запускаем

MySQL on localhost (5.0.27)                                                                                         up 10+21:45:07 [01:02:18]
 Queries: 5.2M   qps:    6 Slow:     0.0         Se/In/Up/De(%):    55/00/00/00
             qps now:   17 Slow qps: 0.0  Threads:    9 (   5/   2) 31/00/00/00
 Key Efficiency: 99.8%  Bps in/out:   0.0/  0.0   Now in/out:  17.3/ 1.9k
 
      Id      User         Host/IP         DB      Time    Cmd Query or State
      --      ----         -------         --      ----    --- ----------
  185137      root       localhost       test         0  Query show full processlist
  185147    test       localhost     test         0  Query SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND post_type =
  185146    test       localhost realbase         1  Sleep
  185106    test       localhost     test         4  Query SELECT DISTINCT p.post_title, p.comment_count, p.post_date, p.ID, COUNT(tr.obj
  185088    test       localhost     test         6  Query SELECT DISTINCT p.post_title, p.comment_count, p.post_date, p.ID, COUNT(tr.obj
  185114    test       localhost     test        17  Query SELECT DISTINCT p.post_title, p.comment_count, p.post_date, p.ID, COUNT(tr.obj
  183812     tface       localhost      tace       815  Sleep



Утилита mysqlard

/usr/ports/databases/mysqlard

Демон mysqlard собирает статистику работы сервера mysql в базу данных rrd.
С помощью rrdtool отрисовывает графики загруженности и производительности.

Конфиг демона /usr/local/etc/mysqlard.cnf,
Контроллер /usr/local/etc/rc.d/mysqlard.sh

Устанавливаем

1) Add the 'mysqlar' user with USAGE privileges to your mysql server:

$ mysql -u root -p mysql
mysql> GRANT USAGE ON *.* TO mysqlar@localhost;
mysql> FLUSH PRIVILEGES;

2) Add the mysqlard crontab to root's crontab:

*/5 * * * * /usr/bin/env hourly=1 daily=1 weekly=1 monthly=1 \
/usr/local/bin/mysqlar_graph > /dev/null

3) Add a line like this to your apache's httpd.conf:

Alias /mysqlar/ /usr/local/www/mysqlard/
<Directory "/usr/local/www/mysqlard">
Options Indexes FollowSymLinks MultiViews ExecCGI
AllowOverride All
Order allow,deny
Allow from all
DirectoryIndex mysqlar.php
</Directory>

===> Installing rc.d startup script(s)

Утилита делает наглядные графики загруженности сервиса.


Утилита mysqlreport

/usr/ports/databases/mysqlreport
Тянет кучу перловых зависимостей. Не поставилось, попробую в другой раз.

[UPD]

[/usr/ports/databases/mysqlreport]# mysqlreport -help
mysqlreport v3.5 Apr 16 2008
mysqlreport makes an easy-to-read report of important MySQL status values.
 
Command line options (abbreviations work):
   --user USER       Connect to MySQL as USER
   --password PASS   Use PASS or prompt for MySQL user's password
   --host ADDRESS    Connect to MySQL at ADDRESS
   --port PORT       Connect to MySQL at PORT
   --socket SOCKET   Connect to MySQL at SOCKET
   --no-mycnf        Don't read ~/.my.cnf
   --infile FILE     Read status values from FILE instead of MySQL
   --outfile FILE    Write report to FILE
   --email ADDRESS   Email report to ADDRESS (doesn't work on Windows)
   --flush-status    Issue FLUSH STATUS; after getting current values
   --relative X      Generate relative reports. If X is an integer,
                     reports are live from the MySQL server X seconds apart.
                     If X is a list of infiles (file1 file2 etc.),
                     reports are generated from the infiles in the order
                     that they are given.
   --report-count N  Collect N number of live relative reports (default 1)
   --detach          Fork and detach from terminal (run in background)
   --help            Prints this
   --debug           Print debugging information
 
Visit http://hackmysql.com/mysqlreport for more information.


Разобраться в отчете утилиты вам поможет страница http://hackmysql.com/mysqlreportguide
Отчет генерируется следующим образом

mysqlreport --password
Password for database user root:
MySQL 5.0.51a-log        uptime 5 18:7:16       Sat Nov  1 12:25:05 2008
 
__ Key _________________________________________________________________
Buffer used     7.55M of 256.00M  %Used:   2.95
  Current      34.22M            %Usage:  13.37
Write hit      99.96%
Read hit       99.93%
 
__ Questions ___________________________________________________________
Total           2.73M     5.5/s
  QC Hits       1.53M     3.1/s  %Total:  56.10
  DMS         702.92k     1.4/s           25.78
  Com_        386.56k     0.8/s           14.18
  COM_QUIT     91.01k     0.2/s            3.34
  +Unknown     16.53k     0.0/s            0.61
Slow 5 s           82     0.0/s            0.00  %DMS:   0.01  Log:  ON
DMS           702.92k     1.4/s           25.78
  SELECT      661.35k     1.3/s           24.26         94.09
  UPDATE       21.47k     0.0/s            0.79          3.05
  INSERT       17.42k     0.0/s            0.64          2.48
  DELETE        2.66k     0.0/s            0.10          0.38
  REPLACE          18     0.0/s            0.00          0.00
Com_          386.56k     0.8/s           14.18
  set_option  188.04k     0.4/s            6.90
  change_db    80.73k     0.2/s            2.96
  show_tables  27.01k     0.1/s            0.99
 
__ SELECT and Sort _____________________________________________________
Scan          207.65k     0.4/s %SELECT:  31.40
Range          93.09k     0.2/s           14.08
Full join       3.65k     0.0/s            0.55
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan     210.79k     0.4/s
Sort range    126.25k     0.3/s
Sort mrg pass  14.45k     0.0/s
 
__ Query Cache _________________________________________________________
Memory usage   13.42M of  16.00M  %Used:  83.87
Block Fragmnt   9.76%
Hits            1.53M     3.1/s
Inserts       583.86k     1.2/s
Insrt:Prune    1.21:1     0.2/s
Hit:Insert     2.62:1
 
__ Table Locks _________________________________________________________
Waited             54     0.0/s  %Total:   0.00
Immediate       1.08M     2.2/s
 
__ Tables ______________________________________________________________
Open              256 of  256    %Cache: 100.00
Opened         75.20k     0.2/s
 
__ Connections _________________________________________________________
Max used           36 of  300      %Max:  12.00
Total          91.02k     0.2/s
 
__ Created Temp ________________________________________________________
Disk table    199.73k     0.4/s
Table         315.03k     0.6/s    Size:  32.0M
File           28.90k     0.1/s
 
__ Threads _____________________________________________________________
Running             1 of    2
Cached              7 of    8      %Hit:  99.52
Created           437     0.0/s
Slow                0       0/s
 
__ Aborted _____________________________________________________________
Clients            18     0.0/s
Connects          555     0.0/s
 
__ Bytes _______________________________________________________________
Sent            3.96G    8.0k/s
Received      348.27M   700.4/s
 
__ InnoDB Buffer Pool __________________________________________________
Usage           1.77M of   8.00M  %Used:  22.07
Read hit       99.71%
Pages
  Free            399            %Total:  77.93
  Data            113                     22.07 %Drty:   0.00
  Misc              0                      0.00
  Latched           0                      0.00
Reads          27.43k     0.1/s
  From file        79     0.0/s            0.29
  Ahead Rnd         4     0.0/s
  Ahead Sql         0       0/s
Writes              2     0.0/s
Flushes             2     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           213     0.0/s
  Writes           11     0.0/s
  fsync            11     0.0/s
  Pending
    Reads           0
    Writes          0
    fsync           0
 
Pages
  Created           0       0/s
  Read            113     0.0/s
  Written           2     0.0/s
 
Rows
  Deleted           0       0/s
  Inserted          0       0/s
  Read          2.51k     0.0/s
  Updated           0       0/s