อาการแปลกๆของ MySQL บน Xeon quad-core

เรื่องมีอยู่ว่า ผมทำ benchmark MySQL เพื่อให้แน่ใจว่ามันสามารถรับ load ได้จริง แต่เนื่องจากยุคนี้เป็นยุคของ Multi-cores การขยาย CPU จะไม่แข่งกันด้วย clock speed แต่จะแข่งกันที่จำนวน core ของ CPU โปรแกรมก็ต้องปรับเปลี่ยนเพื่อให้สามารถทำงานได้ดีบนจำนวน core ที่มากขึ้นเหล่านี้

MySQL เป็น database server ตัวหนึ่งที่ทำงานในลักษณะของ multi-threads ซึ่ง OS กระจาย thread ไปทำงานในแต่ละ core ของ CPU เคยได้ยินมาว่า MySQL ทำงานได้ไม่ดีนักกับจำนวน thread มากๆ มีหลายคนที่พยายามจะแก้ข้อจำกัดนี้ หนึ่งในนั้นคือ google ซึ่งมี code อยู่บน google code โดยจะมี patch เพื่อแก้ปัญหานี้

เดิมผมมี MySQL ทำงานอยู่บนเครื่อง PC Quad-core (Q6600) เครื่องหนึ่ง ลง Gentoo Linux ไว้ เมื่อได้ source code MySQL มาก็จัดแจง patch compile และ run โปรแกรม sysbench เพื่อทำ benchmark ผลออกมาน่าพอใจ throughput ออกมาได้พัน transaction ต่อวินาที (กราฟสีแดง)

ต่อมาได้ server ที่มี CPU Xeon quad-core มาทดสอบ ก็จัดแจงทำเหมือนเดิม แม้กระทั่ง mysql configuration file ก็ใช้ของเดิม พอ run sysbench อีกครั้ง ผลที่ออกมาน่าตกใจมาก (กราฟสีเขียว) throughput หล่นลงไปเหลือร้อยนิดๆ ลองเจ้าไปดูระระบบ CPU ก็ idle เกือบ 90% มันเกิดอะไรขึ้นหว่า พยายามหาคำตอบอยู่สองวันสองคืน ค้นใน Google ไม่มีใครพูดถึงเรื่องนี้เลย

sysbench-1

พี่ที่ทำงานช่วยดูเรื่องนี้ด้วยโดยลองลง FreeBSD 7.1 ให้ผลที่ไม่ต่างกัน ลองทำแบบเดิมในเครื่อง server ที่ใช้ Xeon quad-core อีกเครื่องก็ได้ผลไม่ต่างกัน นั่นแสดงว่าไม่ใช่ความบังเอิญ ผมยังไม่มีคำตอบในเรื่องนี้ เพราะไม่มีปัญญาจะแก้ไขอะไรได้

วิธีแก้ปัญหาของผมคือ เปลี่ยน OS ผมเริ่มด้วยการลง CentOS 5.2 ไว้ที่ server เครื่องหนึ่ง และตามด้วย Open Solaris ไว้ที่ server อีกเครื่องโดยที่ยังไม่ได้ทำ benchmark ใน CentOS เพราะคิดว่า Linux เหมือนกันคงเจอปัญหาเดียวกัน เลยมุ่งเป้าไปที่ Open Solaris มากกว่า พอลงเร็จพยายาม set network ให้ Open Solaris อยู่นานแต่ไม่สำเร็จ เริ่มเบื่อเลยกลับมาทำ benchmark ใน CentoOS ที่ลงไว้ก่อนหน้านี้ ผลออกมาอยากจะกรี๊ดดังๆ เพราะอดนอนมาสองคืน แต่แก้ได้ด้วย CentOS สาเหตุเกิดจากอะไรไว้จะกลับมาหาคำตอบ

sysbench-2

14 คิดบน “อาการแปลกๆของ MySQL บน Xeon quad-core

  1. ไม่รู้ว่าวิธีกรทดสอบใกล้เคียงกันรึเปล่า แต่ผมทำตาม manual ของ sysbench

    ตัวเครื่องเป็น Single CPU – Intel Xeon 4 core 2.66GHz (E5430) Debian 4.0 ใช้ MySQL 5.0.75 build 11 ของ Percona

  2. patch ของ Percona น่าสนใจมากครับ แสดงว่าปัญหาไม่มีใน Debian เย็นๆจะลองในเครื่องอื่นดูด้วยว่าปัญหาเกิดจากอะไร

  3. การใช้ SysBench ลองดูที่ http://sysbench.sourceforge.net/

    ส่วนการ plot graph ผมใช้ Excel ธรรมดาครับ รายละเอียด เดี๋ยวลองหาดูให้ ช่วงนี้ผมไม่สะดวก online เพราะยังไม่มีเครื่องส่วนตัวใช้

  4. ที่ผมสนใจเพราะจะได้นำมาใช้ประโยชน์ครับผมก็ลอง os centos มาหลาย version มาเจอ

    centos 4.7 ครับ +percona 5.1.45-10.2
    กำลังทดสอบครับ เพราะใช้เป็น database server ของ องค์กรครับ เป็นโปรแกรมบริหารโรงพยาบาล ก็เลยสนใจอยาก ทดสอบอะไรที่ดีๆครับ ตัวนี้เขาใช้ทั้งประเทศ เข้าไปแนะนำก็น่าจะดีนะครับ เป้น น อาจารย์ อีกท่าน http://www.hosxp.net

  5. ความคิดของผมคือ อยาก bench mysql on centos 4.7 vs mysql on centos 5.4
    ว่า จะมีความแตกต่างกันอย่างไร ตามกราฟข้างบน อะไรเหมือนกันหมด
    1. hardware +mysql+my.cnf แต่ต่างกันที่ os
    2. hardware +os +my.cnf แต่แตกต่างกันที่ mysql

    มันจะรัน analy นานไหมครับ ผมมี databse ใหญ่มาก ตารางยา 5.6 ล้านกว่า record
    สอนผมด้วย ขอบพระคุณครับ
    tanoy999@gmail.com

    • ติดตั้ง sysbench ตั้งแต่ครั้งแรก with -mysql without -mysql มันคืออะไรครับ
      อยากจะทดสอบ transection ตาม thread ต่างๆ คำสั่งอย่างไร
      ค่า transection ตาม thread ต่าง มันหมายความว่าอย่างไร ค่าสูงค่าต่ำมันดีอย่างไร ประมาณนี้ครับ

  6. ได้แล้วตัวนี้หรือเปล่า ค่าที่ได้ transaction / sec. >>>> 819.11 per sec.
    >>>>> transactions: 100000 (819.11 per sec.)

    Number of threads: 2

    Doing OLTP test.
    Running mixed OLTP test
    Doing read-only test
    Using Special distribution (12 iterations, 1 pct of values are returned in 75 p
    Using “BEGIN” for starting transactions
    Using auto_inc on the id column
    Maximum number of requests for OLTP test is limited to 100000
    Threads started!
    Done.

    OLTP test statistics:
    queries performed:
    read: 1400000
    write: 0
    other: 200000
    total: 1600000
    transactions: 100000 (819.11 per sec.)
    deadlocks: 0 (0.00 per sec.)
    read/write requests: 1400000 (11467.48 per sec.)
    other operations: 200000 (1638.21 per sec.)

    Test execution summary:
    total time: 122.0844s
    total number of events: 100000
    total time taken by event execution: 243.3033
    per-request statistics:
    min: 0.73ms
    avg: 2.43ms
    max: 729.04ms
    approx. 95 percentile: 1.28ms

    Threads fairness:
    events (avg/stddev): 50000.0000/2271.00
    execution time (avg/stddev): 121.6517/0.03

  7. ขอโทษด้วยครับเพิ่งได้เข้ามา

    ตัว Sysbench มันจะจำลอง Database structure ของตัวเองครับ มันเพียงพอสำหรับการทำ benchmark เพื่อเทียบ MySQL สองตัว แต่มันไม่สามารถบอกได้ว่า Database schema ที่เราออกแบบมานั้นดีแค่ไหน

    ส่วนตัวผมเอาไว้เทียบระหว่าง MySQL และ config file ที่ต่างๆกัน ส่วน OS ผมคิดว่ามีผลน้อยมาก การปรับปรุงเรื่องความเร็วของ Database นี่มีหลายเทคนิคมาก ลองดูจากที่นี่ได้http://highscalability.com/

    เรื่องของการ optimize ที่ MySQL หรือ config อาจไม่ช่วยอะไรมากนัก แต่ที่มีผลมากๆคือการปรับที่ query และระวังเรื่องการใช้ index ไว้วันหลังผมจะแวะไปดูที่ http://www.hosxp.net นะครับ

  8. อีกตัวผมลองแล้ว transaction พุ่งกระฉุด

    freebsd8.0+mysql5.1.30+xtradb

    อันนี้ my.cnf ของผม

    [client]
    port = 3306
    socket = /usr/local/var/mysql.sock
    default-character-set=tis620

    [mysqld]
    port = 3306
    socket = /usr/local/var/mysql.sock
    skip-locking
    key_buffer = 512M
    max_allowed_packet = 640M
    table_open_cache = 2000
    sort_buffer_size = 1M
    read_buffer_size = 1M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 256M
    query_cache_size= 512M
    thread_cache = 8
    thread_concurrency = 8
    low_priority_updates=1
    default-character-set=tis620
    skip-name-resolve
    skip-character-set-client-handshake
    init_connect = ‘SET NAMES tis620’
    max_connections = 250

    ################## InnoDB #######################
    innodb_data_home_dir = /usr/local/var/
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /usr/local/var/
    innodb_buffer_pool_size = 640M
    innodb_additional_mem_pool_size = 20M
    innodb_log_file_size = 256M
    innodb_file_per_table
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50
    innodb_thread_concurrency = 0
    innodb_flush_method = O_DIRECT
    innodb_checksums = 0
    innodb_doublewrite = 0
    innodb_support_xa = 0
    innodb_max_dirty_pages_pct = 90
    innodb_open_files = 2000
    innodb_locks_unsafe_for_binlog = 1
    join_buffer_size = 2M
    concurrent_insert = 2
    max_heap_table_size = 64M

    ############# Percona XtraDB ############
    innodb_adaptive_checkpoint = 1
    innodb_io_capacity = 1000
    innodb_write_io_threads = 16
    innodb_read_io_threads = 16

    [mysqldump]
    quick
    max_allowed_packet = 512M
    allow-keywords

    [mysql]
    no-auto-rehash
    default-character-set=tis620

    [isamchk]
    key_buffer = 512M
    sort_buffer_size = 512M
    read_buffer = 4M
    write_buffer = 4M

    [myisamchk]
    key_buffer = 512M
    sort_buffer_size = 512M
    read_buffer = 4M
    write_buffer = 4M

    [mysqlhotcopy]
    interactive-timeout

ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out / เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out / เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out / เปลี่ยนแปลง )

Connecting to %s