MySQL tuning

Вот и пришло время, когда мне вдруг пришлось лезть в дебри MySQL tuning и пробовать заставить работать базу шустрее.

mysqlreport

Key _________________________________________________________________
Buffer used 3.00k of 16.00M %Used: 0.02
 Current 2.92M %Usage: 18.26
Write hit 99.44%
Read hit 99.70%
__ Questions ___________________________________________________________
Total 46.14k 0.2/s
 QC Hits 31.06k 0.2/s %Total: 67.31
 Com_ 11.13k 0.1/s 24.13
 DMS 6.62k 0.0/s 14.36
 -Unknown 2.85k 0.0/s 6.17
 COM_QUIT 173 0.0/s 0.37
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 6.62k 0.0/s 14.36
 SELECT 6.13k 0.0/s 13.29 92.59
 INSERT 233 0.0/s 0.50 3.52
 UPDATE 198 0.0/s 0.43 2.99
 DELETE 60 0.0/s 0.13 0.91
 REPLACE 0 0/s 0.00 0.00
Com_ 11.13k 0.1/s 24.13
 show_tables 3.26k 0.0/s 7.07
 admin_comma 2.76k 0.0/s 5.99
 show_fields 2.57k 0.0/s 5.56
__ SELECT and Sort _____________________________________________________
Scan 6.60k 0.0/s %SELECT: 107.63
Range 338 0.0/s 5.51
Full join 74 0.0/s 1.21
Range check 3 0.0/s 0.05
Full rng join 0 0/s 0.00
Sort scan 615 0.0/s
Sort range 168 0.0/s
Sort mrg pass 0 0/s
__ Query Cache _________________________________________________________
Memory usage 1.49M of 32.00M %Used: 4.65
Block Fragmnt 8.51%
Hits 31.06k 0.2/s
Inserts 5.68k 0.0/s
Insrt:Prune 5.68k:1 0.0/s
Hit:Insert 5.46:1
__ Table Locks _________________________________________________________
Waited 0 0/s %Total: 0.00
Immediate 11.87k 0.1/s
__ Tables ______________________________________________________________
Open 85 of 400 %Cache: 21.25
Opened 888 0.0/s
__ Connections _________________________________________________________
Max used 7 of 151 %Max: 4.64
Total 243 0.0/s
__ Created Temp ________________________________________________________
Disk table 2.80k 0.0/s
Table 6.61k 0.0/s Size: 16.0M
File 6 0.0/s
__ Threads _____________________________________________________________
Running 1 of 3
Cached 4 of 8 %Hit: 97.12
Created 7 0.0/s
Slow 0 0/s
__ Aborted _____________________________________________________________
Clients 136 0.0/s
Connects 13 0.0/s
__ Bytes _______________________________________________________________
Sent 76.37M 391.8/s
Received 10.59M 54.3/s
__ InnoDB Buffer Pool __________________________________________________
Usage 11.59M of 127.98M %Used: 9.06
Read hit 99.88%
Pages
 Free 7.45k %Total: 90.94
 Data 734 8.96 %Drty: 0.00
 Misc 8 0.10
 Latched 0 0.00
Reads 540.45k 2.8/s
 From file 657 0.0/s 0.12
 Ahead Rnd 0 0/s
 Ahead Sql 0/s
Writes 67.97k 0.3/s
Flushes 2.52k 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 667 0.0/s
 Writes 3.61k 0.0/s
 fsync 1.25k 0.0/s
 Pending
 Reads 0
 Writes 0
 fsync 0
Pages
 Created 78 0.0/s
 Read 656 0.0/s
 Written 2.52k 0.0/s
Rows
 Deleted 57 0.0/s
 Inserted 8.88k 0.0/s
 Read 96.96k 0.5/s
 Updated 185 0.0/s

Отлично, первое что произошло после изменения my.cnf – база упала.
Пришлось все закомментить. Пока работал над своей базой, на работе упали продовские, так что это увлекательное путешествие я пока откладываю до свободного времени.

Возвращаясь к прошлому опыту, я все таки выставил настройки по образу старой системы, описанному здесь: http://www.trianglesis.org.ua/mysql-default-my-cnf-konciguraciya

mysqlreport

Use of uninitialized value in formline at /usr/bin/mysqlreport line 1238, <STDIN> line 1.
MySQL 5.5.38-0+wheezy1 uptime 0 0:5:59 Tue Oct 14 14:58:14 2014
__ Key _________________________________________________________________
Buffer used 0 of 64.00M %Used: 0.00
 Current 11.67M %Usage: 18.24
Write hit 0.00%
Read hit 0.00%
__ Questions ___________________________________________________________
Total 332 0.9/s
 DMS 153 0.4/s %Total: 46.08
 Com_ 108 0.3/s 32.53
 QC Hits 51 0.1/s 15.36
 COM_QUIT 46 0.1/s 13.86
 -Unknown 26 0.1/s 7.83
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 153 0.4/s 46.08
 SELECT 153 0.4/s 46.08 100.00
 REPLACE 0 0/s 0.00 0.00
 DELETE 0 0/s 0.00 0.00
 INSERT 0 0/s 0.00 0.00
 UPDATE 0 0/s 0.00 0.00
Com_ 108 0.3/s 32.53
 show_tables 38 0.1/s 11.45
 show_create 26 0.1/s 7.83
 admin_comma 19 0.1/s 5.72
__ SELECT and Sort _____________________________________________________
Scan 80 0.2/s %SELECT: 52.29
Range 6 0.0/s 3.92
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 10 0.0/s
Sort range 1 0.0/s
Sort mrg pass 0 0/s
__ Query Cache _________________________________________________________
Memory usage 274.22k of 23.00M %Used: 1.16
Block Fragmnt 0.52%
Hits 51 0.1/s
Inserts 83 0.2/s
Insrt:Prune 83:1 0.2/s
Hit:Insert 0.61:1
__ Table Locks _________________________________________________________
Waited 0 0/s %Total: 0.00
Immediate 165 0.5/s
__ Tables ______________________________________________________________
Open 100 of 1024 %Cache: 9.77
Opened 404 1.1/s
__ Connections _________________________________________________________
Max used 3 of 1000 %Max: 0.30
Total 49 0.1/s
__ Created Temp ________________________________________________________
Disk table 72 0.2/s
Table 278 0.8/s Size: 16.0M
File 6 0.0/s
__ Threads _____________________________________________________________
Running 1 of 1
Cached 2 of 128 %Hit: 93.88
Created 3 0.0/s
Slow 0 0/s
__ Aborted _____________________________________________________________
Clients 2 0.0/s
Connects 6 0.0/s
__ Bytes _______________________________________________________________
Sent 363.46k 1.0k/s
Received 36.03k 100.4/s
__ InnoDB Buffer Pool __________________________________________________
Usage 10.14M of 127.98M %Used: 7.92
Read hit 96.00%
Pages
 Free 7.54k %Total: 92.08
 Data 648 7.91 %Drty: 0.00
 Misc 1 0.01
 Latched 0 0.00
Reads 16.21k 45.1/s
 From file 649 1.8/s 4.00
 Ahead Rnd 0 0/s
 Ahead Sql 0/s
Writes 1 0.0/s
Flushes 1 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 659 1.8/s
 Writes 7 0.0/s
 fsync 7 0.0/s
 Pending
 Reads 0
 Writes 0
 fsync 0
Pages
 Created 0 0/s
 Read 648 1.8/s
 Written 1 0.0/s
Rows
 Deleted 0 0/s
 Inserted 0 0/s
 Read 823 2.3/s
 Updated 0 0/s

About trianglesis

Александр Брюндтзвельт - гений, филантроп, 100 гривен в кармане. Этот блог - "сток" моих мыслей и заметок. Достаточно одного взгляда на него, чтобы понять, что такой же бардак творится у меня в голове. Если вам этот бардак интересен - милости прошу.
Bookmark the permalink.