3

I have a Ubuntu 14.04 server running half a dozen wordpress sites. Total traffic to the server is on the order of 30k page views a day.

Over the past couple months, with page views and network traffic roughly flat, my disk I/O has been trending up significantly. It has doubled over the past month. When I run iotop, I can see that mysqld (v. 5.5) is constantly writing to disk at a rate of around 5MB per minute. I can't think of anything happening on any of the sites that would generate so much writing.

I have spent a lot of time reading mysql tuning tutorials and adjusting variables (mostly to do with tmp table size, and query cache parameters) and nothing I have tried has made any difference. My current guess is that there is probably a bad query in one of the wordpress plugins in use. But there are a lot of plugins spread over half a dozen wordpress installs and I don't know where to look.

Is there some way that I can pinpoint what exactly is causing mysqld to be writing to the disk so much?

UPDATE : This question was explored in better detail and answered thoroughly here.

Community
  • 1
pjv
  • 191
  • 2
  • 7
  • I would appreciate if whoever downvoted my question would leave a comment saying why. – pjv Jan 15 '15 at 16:16

3 Answers3

1

Two ideas off the top of my head.

  1. mytop - a top clone of MySQL
  2. iotop - a python top-like program specifically for io
  • thanks, i mentioned using iotop in my question - that's how i know that it is mysqld that is doing the disk io. i didn't mention it but i am also already using mytop (which is a very useful suggestion). if i am currently right about what is causing the io, the queries go by so fast that mytop doesn't catch them. but it is a great little utility. – pjv Jan 15 '15 at 16:47
  • re: iotop, ya, I saw that after I posted. Didn't read carefully before answering. re: mytop: -s or -delay seconds How long between display refreshes. Default: 5 –  Jan 15 '15 at 17:03
  • changed it to 1 and saw some things i wasn't seeing with 5, but still no smoking gun. one thing i saw might have led me in the right direction. i'll find out early tomorrow morning when i can delete a bunch of records in a bloated table. – pjv Jan 16 '15 at 01:27
0

Take a look at the mysql processlist. mysql -p -e "show processlist". Or even a "show full processlist". It will give you a list of currently running queries.

drookie
  • 228
  • 3
  • 12
  • thanks. unfortunately whatever is causing mysqld to write must be a very quick query and it doesn't linger in the process list. right now i am thinking it might be a query that is fast but causes a large tmp table write over and over with each request on one of the wordpress sites. if/when i confirm, i'll write up the answer. – pjv Jan 15 '15 at 16:50
0

This question was explored in better detail and answered thoroughly here.

Community
  • 1
pjv
  • 191
  • 2
  • 7