วานๆพี่ๆช่วยดูให้ทีครับ ปัญหา sql ล่มบ่อย

Technical topics
1

สมาชิกใหม่ครับ ขอฝากเนื้อฝากตัวด้วยนะครับ วันนี้มาขอคำแนะนำจากพี่ๆที่นี่ครับ เรื่องมีอยู่ว่าผมเพิ่งจะลองเปลี่ยนมาใช้ VPS ได้ไม่นาน เนื่องจากอยู่ hostgator ไม่ไหวเพราะ traffic ที่เพิ่มขึ้นจึงต้องเปลี่ยนมาใช้ VPS ตอนนี้ก็ยังงงๆทำอะไรไม่ค่อยถูก ไม่รู้ว่าต้องset my.cnf ยังไงให้เหมาะสมกับเว็บตัวเอง ก่อนหน้านี้เว็บผมมี traffic ประมาณ 15000-20000 uip/วัน PV วันละประมาณ 2 แสน หมายถึงตอนที่ใช้ hostgator นะครับ แต่พอย้ายมาใช้ VPS ของ Photon เว็บ sql ล่มบ่อยๆทำเอา traffic หายไปเยอะเลย เศร้าและเครียดเลยย ผมก็พยายามหาข้อมูลในบอร์ดนี้เกี่ยวกับวิธีการปรับแต่ง my.cnf ก็ลองปรับๆตามคำแนะนำตามกระทู้ในบอร์ดนี้ดู แต่มันก็ยังไม่นิ่งพอ ยังมีล่มเป็นช่วงๆเหมือนเคย ผมก็ไม่มีความรู้เกี่ยวกับเรื่อง server สักเท่าไหร่ ไม่รู้ว่าปัญหามันเกิดจากอะไรกันแน่ เว็บของผมเป็นระบบเขียนเองครับ เป็นระบบเว็บบอร์ดคล้าย smf Database ประมาณ 100M ตารางหลักที่มีการดึงข้อมูลบ่อยๆ มี record ประมาณ 16000 แถว คนออนไลน์พร้อมกันจะอยู่ที่ 400-500 คน แต่ปัจจุบันหลังจากที่ย้ายมาใช้ VPS ลดลงอยู่ที่ 200-300 คน ดูจาก histats นะครับ

my.cnf ที่ผม set ไว้ครับ

[mysqld]

max_connections = 500

log-slow-queries

safe-show-database

key_buffer = 128

sort_buffer_size = 4M

read_buffer_size = 2M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

join_buffer_size = 1M

table_cache = 1536

thread_cache = 8

thread_concurrency = 8

thread_cache_size = 128

tmp_table_size = 32M

max_tmp_tables = 32M

tmp_table_size = 32M

query_cache_type = 1

query_cache_size = 32M

query_prealloc_size = 16384

wait_timeout = 600

connect_timeout = 10

max_allowed_packet = 16M

max_connect_errors = 10

query_cache_limit = 1M

query_alloc_block_size = 16384

query_heap_table_size = 128M

log_show_queries

log_queries_not_using_indexes

รายละเอียด VPS ของผมคร่าวๆ

Ram: 2GB

Swap:4GB

CPU Cores: 8

สถานะ service ครับ

top - 23:53:57 up 5 days, 2:14, 1 user, load average: 2.72, 1.84, 1.85

Tasks: 361 total, 3 running, 357 sleeping, 0 stopped, 1 zombie

Cpu0 : 0.7%us, 2.7%sy, 0.0%ni, 56.5%id, 39.9%wa, 0.0%hi, 0.0%si, 0.3%st

Cpu1 : 4.7%us, 6.3%sy, 0.0%ni, 89.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

Cpu2 : 5.4%us, 2.3%sy, 0.0%ni, 92.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

Cpu3 : 1.1%us, 0.0%sy, 0.0%ni, 98.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

Cpu4 : 0.0%us, 0.5%sy, 0.0%ni, 99.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

Cpu5 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

Cpu6 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

Cpu7 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

Mem: 2097152k total, 1971812k used, 125340k free, 513748k buffers

Swap: 4194296k total, 6280k used, 4188016k free, 938716k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

18316 mysql 15 0 186m 18m 3316 S 10.6 0.9 167:16.35 mysqld

25462 root 16 0 0 0 0 S 1.3 0.0 0:00.24 pdflush

482 root 10 -5 0 0 0 S 0.3 0.0 3:01.01 kjournald

18361 nobody 15 0 97.8m 21m 1548 S 0.3 1.1 0:00.08 httpd

22120 nobody 15 0 97.8m 21m 1544 S 0.3 1.1 0:00.08 httpd

24099 nobody 15 0 97.8m 21m 1544 S 0.3 1.1 0:00.02 httpd

24276 nobody 15 0 97.8m 21m 1540 S 0.3 1.1 0:00.01 httpd

24876 root 15 0 13008 1536 948 R 0.3 0.1 0:00.38 top

25242 root 15 0 0 0 0 S 0.3 0.0 0:00.73 pdflush

1 root      15   0 10368  696  584 S  0.0  0.0   0:00.06 init

2 root      RT  -5     0    0    0 S  0.0  0.0   0:01.58 migration/0

3 root      34  19     0    0    0 S  0.0  0.0   0:00.10 ksoftirqd/0

4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0

5 root      10  -5     0    0    0 S  0.0  0.0   0:00.10 events/0

6 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 khelper

7 root      11  -5     0    0    0 S  0.0  0.0   0:00.00 kthread

9 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 xenwatch

10 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 xenbus

16 root RT -5 0 0 0 S 0.0 0.0 0:00.53 migration/1

17 root 34 19 0 0 0 S 0.0 0.0 0:00.17 ksoftirqd/1

18 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/1

19 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/1

20 root RT -5 0 0 0 S 0.0 0.0 0:01.42 migration/2

21 root 34 19 0 0 0 S 0.0 0.0 0:01.23 ksoftirqd/2

22 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/2

23 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/2

24 root RT -5 0 0 0 S 0.0 0.0 0:00.71 migration/3

25 root 34 19 0 0 0 S 0.0 0.0 0:00.44 ksoftirqd/3

26 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/3

27 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/3

28 root RT -5 0 0 0 S 0.0 0.0 0:00.53 migration/4

29 root 34 19 0 0 0 S 0.0 0.0 0:00.33 ksoftirqd/4

30 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/4

31 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/4

32 root RT -5 0 0 0 S 0.0 0.0 0:00.41 migration/5

33 root 34 19 0 0 0 S 0.0 0.0 0:00.32 ksoftirqd/5

34 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/5

35 root 10 -5 0 0 0 S 0.0 0.0 0:00.01 events/5

36 root RT -5 0 0 0 S 0.0 0.0 0:00.31 migration/6

[root@vps ~]#

รบกวนพี่ๆแนะนำหน่อยครับว่าสาเหตุหลักๆมันเกิดจากอะไรกันแน่ ผมไปไม่ถูกจริงๆครับ ไม่รู้จะแก้จากตรงไหนดี ขอขอบคุณทุกความคิดเห็นล่วงหน้าครับ

2

ขอดันหน่อยนะครับ

3

ขอบคุณทุกความคิดเห็นมากๆเลยครับผม

4

คงต้องทำ cache กับงานข้อมูลบ้างแล้วล่ะ และ con current online 500 คน หากดึง mysql ทุกหน้า ก็งานหนักเหมือนกัน อาจจะจำกัดด้วย timeout ของ apache แต่ก็จะเพิ่มโอกาสคนเข้าหลังๆเปิดไม่ค่อยได้

5

ผมลอง set httpd.conf แล้วครับ set ตามคำแนะนำตามกระทู้ในบอร์ดนี้อ่ะครับ แต่ก็ยังเป็นเหมือนเดิม average ของ cpu มันเพิ่มสูงขึ้นเรื่อยๆเลยครับ ผมต้อง restart mysql ตลอดทุกๆ 10นาทีเลยอ่าครับ ไม่เช่นนั้นเว็บก็ล่ม

พอจะแนะนำได้มั้ยครับว่ามันน่าจะเกิดจากอะไร หรือว่า ram ผมน้อยไป เพราะ ram มันเต็มบ่อยมาก

top - 02:38:49 up 6 days, 4:59, 2 users, load average: 30.63, 15.72, 25.60

Tasks: 502 total, 1 running, 500 sleeping, 1 stopped, 0 zombie

Cpu(s): 5.6%us, 33.7%sy, 0.0%ni, 16.2%id, 44.1%wa, 0.0%hi, 0.0%si, 0.4%st

Mem: 2097152k total, 2080692k used, 16460k free, 86592k buffers

Swap: 4194296k total, 63132k used, 4131164k free, 827612k cached

6

อาการ ram เต็ม

i/o ก็เต็ม

7

คงต้องขยับไปลงเครื่องแล้ว

8

คร่าว ๆ

ก็ลองเพิ่ม key_buffer เป็นสัก 256M ก่อนครับ (เห็นข้างบนไม่ใส่หน่วย มันจะกลายเป็น 128k หรือเปล่า)

เช็คให้แน่ใจว่าปิด binary log แล้ว (เอา log-bin ออก) เพราะมันทำให้ช้าถ้ามี update เยอะ ๆ

ค่าอื่น ๆ คิดว่าโอเคแล้วครับ ยกเว้น max connections ไม่น่าจะต้องใช้ถึง 500 เพราะว่า apache คุณไม่ควรจะมี process ถึง 500 ถ้าถึง ram เต็มค้างแน่ ๆ ครับ เพราะแรมแค่ 2G

สำหรับ RAM 2G ยังแนะนำให้ใช้ MaxClients, ServerLimit ไม่เกิน 300 แล้วตั้ง max connections เท่า ๆ กัน สัก 300 พอ

สุดท้ายก็ลอง เปิด mod_status เพื่อดูหน้า server-status หรือดู log ว่ามี request อะไรแปลก ๆ หรือ bot/worm ที่ไหนเข้ามาแอบ post/search หรือเปล่า มันทำให้เครื่องจุกได้

9

ขอบคุณมากๆครับพี่สำหรับคำแนะนำ เดี๋ยวผมลองก่อน ได้ผลยังไงเดี๋ยวรายงานอีกทีครับ (ดีใจมากมีคนมาช่วยตอบสะที อิอิ)

10

ถามอีกนิดนึงนะครับ ผม set ServerLimt กับ MaxClient ไว้ 512

มันมากเกินไปสำหรับ Ram 2 GB หรือเปล่าครับ ที่ผมเข้าใจตอนนี้เกี่ยวกับ MaxClient คือการกำหนดจำนวนผู้เข้าชมเว็บไซต์ในช่วงเวลานั้นๆไม่ให้เกิน 512 ip ใช่หรือไม่ครับ คือเว็บผมเคยมีคนออนไลน์พร้อมกัน 500-600 ก็มี รบกวนช่วยแนะนำอีกทีนะครับ

11

maxclients หมายถึงจำนวน process ย่อยของ apache ที่อนุญาตให้สร้างได้ครับ

ซึ่งโดยมากแล้วก็หมายถึงจำนวน “concurrent connection” สูงสุด “ณ เวลาใดเวลาหนึ่ง” ครับ

ไอ้ user online 500-600 คนบนหน้าเว็บคุณน่ะ มันในรอบ 15 นาทีครับ ซึ่งเอาจริงๆ เวลาเปิดเว็บเนี่ย

คนนึงจะ connect ค้างไว้ที่ server แค่ไม่กี่วินาทีเท่านั้นเอง โหลดเสร็จก็ตัดไปครับ

ค่าที่เหมาะสมคือ ปริมาณแรมในหน่วย MB หารด้วย 30-40 ครับ (เป็นค่าเฉลี่ยการใช้แรมของ apache + php)

========================

edit 1 ::

ย้ำอีกที จำนวน concurrent connection ไม่ได้หมายถึงจำนวน ip ที่เข้าใช้งานพร้อมกันนะครับ

เว็บปกติบนหน้าเว็บก็มีรูปมีอะไรอยู่หลายอย่าง browser ก็จะสร้าง connection

หลายๆ connection ไปหา server พร้อมกัน เพื่อให้โหลดได้เร็วขึ้นด้วยครับ

ทำให้จำนวน “คน” ที่เปิดเว็บได้พร้อมกัน “ณ วินาทีเดียวกัน” จะไม่ถึง maxclient แน่นอนครับ

12

ขอบคุณมากเลยครับ กระจ่างขึ้นมาเลย ความรู้ใหม่ ^^

13

เปิด option server-status apache เครื่องมือช่วยพิจารณาประกอบได้ดี ดู phpmyadmin menu process ประกอบ น่าจะแยกแยะออกว่า อะไรใช้มาก ใช้น้อย ช่วงเวลา peak สุด ค่อยๆปรับ