จูน Mysql แบบนี้ถูกต้องไหมครับ

Technical topics
1

เว็บเป็นเว็บรันด้วย WP จำนวน 60เว็บครับ อาการคือรันๆไปหลายๆวันมันจะค้างเข้าเว็บอืด
CPU 8Core
Ram 8GB

[mysqld]
local-infile=0


skip-name-resolve
skip-character-set-client-handshake
max_connections = 500


#connectino settings
key_buffer = 512M
max_user_connections = 50
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 4M
sort_buffer_size = 2M
max_heap_table_size = 256M

#caching
table_cache = 1024
thread_cache = 16
thread_concurrency = 8
thread_cache_size = 64
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384

#timeout
wait_timeout = 1800
connect_timeout = 10
tmp_table_size = 256M
max_allowed_packet = 16M
max_connect_errors = 10
innodb_buffer_pool_size = 64M
key_buffer_size = 1332M
open_files_limit=50000

log-slow-queries=/var/log/mysql/slow-query.log
2

อันนี้มันไม่มี parameters ของ innodb เลยนะครับ ไม้ได้ใช้เลยเหรอครับ

3

max_connections = 256
=> ค่านี้ไม่ควรเกิน 300 แต่ก็เพิ่มขึ้นได้อีกตามลำดับ

key_buffer = 512M
=> 128 - 256M แล้วแต่ระบบ

join_buffer_size = 2M
read_buffer_size = 4M
sort_buffer_size = 2M
=> 512K ก็พอครับ ใส่แต่ละค่ามากสุดไม่ควรเกิน 1M ส่วนอื่นๆเพิ่มเอาตามลำดับ

tmp_table_size = 256M
=> 128M

innodb_buffer_pool_size = 64M
=> ดูที่ระบบเอาเองครับอันนี้

key_buffer_size = 1332M
=> 128M

open_files_limit=50000
=> 16384

log-slow-queries ไม่ควรเปิดทิ้งไว้เล่นๆครับ

และก็เพิ่ม

max_heap_table_size = 128M
=> ค่านี่ “ควรที่จะ” เท่ากันกับ tmp_table_size

innodb_additional_mem_pool_size = 1M
innodb_log_buffer_size = 500K
innodb_thread_concurrency = 2
innodb_file_per_table

4

เว็บผมเป็น myisam ไม่ได้ทำ innodb แต่แรกครับ ถ้ามานั่งแปลงกลัวข้อมูลเสียอีก

5

เดวลองดูครับขอบคุณมากฮะ

6

ไปรัน mysqltuner.pl แล้วเอาผลมาแปะครับ

7

เดวลองดูครับรอมันครบ 24ชมก่อนครับ เพราะเดวไม่ได้ค่าที่แท้จริงตามคำแนะนำ

8

ได้มาแล้วครับก่อน 24ชมได้ค่าแบบนี้ครับ

sh-3.2# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 68G (Tables: 1000315)
[--] Data in InnoDB tables: 1M (Tables: 65)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 61118

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15h 52m 35s (2M q [39.377 qps], 87K conn, TX: 5B, RX: 342M)
[--] Reads / Writes: 85% / 15%
[--] Total buffers: 353.5M global + 3.5M per thread (500 max threads)
[OK] Maximum possible memory usage: 2.1G (68% of installed RAM)
[OK] Slow queries: 0% (27/2M)
[OK] Highest usage of available connections: 2% (14/500)
[!!] Key buffer size / total MyISAM indexes: 128.0M/14.8G
[!!] Key buffer hit rate: 93.9% (21M cached / 1M reads)
[OK] Query cache efficiency: 48.8% (852K cached / 1M selects)
[!!] Query cache prunes per day: 909152
[OK] Sorts requiring temporary tables: 0% (312 temp sorts / 191K sorts)
[!!] Temporary tables created on disk: 46% (90K on disk / 195K total)
[OK] Thread cache hit rate: 99% (14 created / 87K connections)
[!!] Table cache hit rate: 0% (1K open / 4M opened)
[OK] Open file limit used: 12% (2K/16K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 1.3M/64.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    key_buffer_size (> 14.8G)
    query_cache_size (> 32M)
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)
    table_cache (> 1024)
9

ดูจากขนาด database แล้ว ผมว่าควรอัพแรมเครื่องเป็นซัก 24-32G แล้วอัด key_buffer_size ไป 16G เลยครับ แต่ถ้าอัดไม่ไหวจริงๆก็ถมแรมให้ key buffer ให้มากที่สุดเท่าที่จะให้ได้ครับ

10

  1. ทำไมมันบอกว่ามีแรมแค่ 3GB ?
    >>> [OK] Maximum possible memory usage: 2.1G ([U]68% of installed RAM[/U])

  2. ถ้าหา ssd ยัดได้จะดีมากครับ (แหงแหละ)

  3. ปรับ my.cnf ตามนี้


[mysqld]
local-infile=0




skip-name-resolve
skip-character-set-client-handshake
max_connections = 500


#global buffer
key_buffer = 2048M
innodb_buffer_pool_size = 64M
key_buffer_size = 2048M
open_files_limit=32768


#connectino settings
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 1M
sort_buffer_size = 2M
max_heap_table_size = 256M


#caching
table_cache = 4096
thread_cache = 16
thread_concurrency = 8
thread_cache_size = 64
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384


#timeout
wait_timeout = 1800
connect_timeout = 10
tmp_table_size = 256M
max_allowed_packet = 16M
max_connect_errors = 10
11

เพิ่มเติม

ถ้าเช็คแล้วว่าแรมยังพอเหลือว่างอยู่บ้าง(ซัก 1GB) ลองทำ ramdisk สำหรับ tmpdir ของ mysql ดูครับ

วิธีทำตามนี้


echo 'tmpfs /var/mysqltmp tmpfs size=1024M 0 0' >> /etc/fstab
mkdir /var/mysqltmp
mount /var/mysqltmp

แล้วเพิ่ม tmpdir=/var/mysqltmp ลงใน /etc/my.cnf ครับ

12

ขออนุญาติถามแทรกกระทู้นิดนะครับ ramdisk มีข้อดีและเสียอย่างไรบ้างครับ

13

เป็น Server แล้วทำเป็น OpenVZ แยกออกมาอีกที จะทำได้มั้ยครับ เครื่องทำ Flashcache จาก SSD อยู่ครับ

14

ได้ครับ

15

โอเคครับเดวค่อยเอาผลหลังจาก 24ชมมาให้ดูอีกทีครับ ตอนนี้ลองใส่ตามพี่ไอซ์แล้ว

16

ช่วยได้ในกรณีเกิด filesort แล้วเกิด copy to temporary table ครับ มันจะก๊อปไปลงแรม ซึ่งจะเร็วกว่า copy ลง disk

17

ยังไม่ตอบข้อ 0 ผมเลย

[COLOR=#333333]0) ทำไมมันบอกว่ามีแรมแค่ 3GB ?[/COLOR]
[COLOR=#333333]>>> [OK] Maximum possible memory usage: 2.1G ([/COLOR][U]68% of installed RAM[/U][COLOR=#333333])
[/COLOR]

18

ตัวแรมมัน4ครับกำหนดใน VZ และ Swap ของ VZ อีก2 รวม6กิ๊กกันไว้2กิ๊กให้เครื่องหลักกันมันล่มและหายไปด้วย เพราะ CentOS 6 ใช้ตัว Swap ได้และผมใช้ SolusVM มันไปกำหนดตรงนั้นให้อัตโนมัตร

19

swap ก้คือ swap ครับ อย่าไปนับว่ามันเป็นแรม มันไม่ใช่แรมครับ

20

โอเคครับ ปรับใหม่และครับ
พอเทสมัน Error แบบนี้ครับหมายความว่าไงครับ

>>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
ERROR 126 (HY000) at line 1: Incorrect key file for table '/var/mysqltmp/#sql_75e0_0.MYI'; try to repair it
ERROR 126 (HY000) at line 1: Incorrect key file for table '/var/mysqltmp/#sql_75e0_0.MYI'; try to repair it
[!!] InnoDB is enabled but isn't being used
Argument "" isn't numeric in numeric gt (>) at ./mysqltuner.pl line 564 (#1)
    (W numeric) The indicated string was fed as an argument to an operator
    that expected a numeric value instead.  If you're fortunate the message
    will identify which operator was so unfortunate.

[OK] Total fragmented tables:

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
ERROR 126 (HY000) at line 1: Incorrect key file for table '/var/mysqltmp/#sql_75e0_0.MYI'; try to repair it
Argument "" isn't numeric in numeric eq (==) at ./mysqltuner.pl line 623 (#1)

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 1h 2m 40s (4M q [47.175 qps], 152K conn, TX: 10B, RX: 686M)
[--] Reads / Writes: 85% / 15%
[--] Total buffers: 2.4G global + 5.5M per thread (500 max threads)
[!!] Maximum possible memory usage: 5.1G (169% of installed RAM)
[OK] Slow queries: 0% (23/4M)
[OK] Highest usage of available connections: 4% (21/500)
[!!] Cannot calculate MyISAM index size - re-run script as root user
[OK] Query cache efficiency: 38.3% (1M cached / 3M selects)
[!!] Query cache prunes per day: 1339893
[OK] Sorts requiring temporary tables: 0% (323 temp sorts / 448K sorts)
[!!] Temporary tables created on disk: 45% (200K on disk / 441K total)
[OK] Thread cache hit rate: 99% (21 created / 152K connections)
[!!] Table cache hit rate: 0% (4K open / 1M opened)
[OK] Open file limit used: 24% (8K/32K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 64M)
    table_cache (> 4096)