Please help tune my web linode and mysql linode.

Maybe post the node sizes and just make sure the mysql queries are going to the database node :P

Both nodes have 1024mb of ram, but I'm sure the queries are going to the database node as I don't have any databases installed on the web node…

Although I did double check as that thought crossed my mind as well.

Thanks!

MaxClients 96 seems too high, even for a Linode 1024. Although you still seem to have some RAM left in the web node, I'd suggest that you try cutting MaxClients in half. That way, fewer scripts will run at any given time, and each script will finish more quickly. Also, install php5-apc if you haven't already done so.

On the other hand, key_buffer 64K is ridiculously low for a Linode 1024 with nothing but MySQL on it. People used to recommend that you make this 1/2 of your total RAM. But this only matters if your tables are MyISAM, which they shouldn't be. Any medium-to-large forum site should be using InnoDB to reduce lock contention.

Disclaimer: Both of the above points are "rules of thumb", and they might not improve performance in your particular circumstances.

What, exactly, is taking most of the CPU? Is your application doing something silly, like retrieving a bunch of rows from SQL and then sorting/filtering them itself? Can you use something like memcached to decrease the number of times your application needs to sit and think a lot?

Ok I dropped MaxClients down to 50 and put the key_buffer up to 512M.

I changed query cache and table cache based on what I saw from mysqltuner

http://pastebin.com/nBrCnnUM

The memory usage got up to about 15% on the database server after that, but the site is responding even more slowly now. It is almost unbearable at times.

I got the following Disk IO warning as well.

Your Linode, alabama, has exceeded the notification threshold (1500) for disk io rate by averaging 6475.84 for the last 2 hours.

It will take anywhere from 10 to 50 seconds to navigate from the forum index to a forum listing and the same when using the "view new posts" feature.

At all times a handful of Apache processes are showing between 10 and 20% CPU usage a piece.

I'll admit I'm in over my skis here. But I'm eager to learn and even more eager to get my site back stable.

Also I am still using MyISAM for my forum database. The admins at IPBoard recommend against switching which goes against what I read everywhere else, but it is their software so that does make me hesitant.

I am using Memcache on the board.

@randrp:

I got the following Disk IO warning as well.

Your Linode, alabama, has exceeded the notification threshold (1500) for disk io rate by averaging 6475.84 for the last 2 hours.
Which server is "alabama"? The web server or the DB server?

You can use "iotop" to see which process is hitting the disk the most. You may need to install iotop first.

Try changing MaxClients back to the previous value. Does it help?

Does anything suspicious show up in Apache or MySQL's error logs? Does Apache's error logs contain any messages about MaxClients being reached/exceeded?

How many simultaneous users do you think you have? Is the site always slow, or is it only slow during peak hours?

Enable the slow query log on MySQL. Wait a while and see if anything shows up in the slow query log.

Could you post a screenshot of the "top" command on both servers?

Alabama is the DB server.

I did iotop on the DB server and watched it for a few minutes. It is a smattering of mysql processes with cumulative reads bouncing from 0 to 500K/s and even quite a bit of 3 and 4M/s. Writes were typically in the 50K/s range.

There are some writes in the 30K/s to 50K/s range on the web server side, but no spikes.

I moved MaxClients back and I did not really notice any effectiveness so it was probably just coincidence.

The MySQL error logs are empty and I just enabled the slow query log. Here is what I'm getting there so far. I'll run this again later.

http://pastebin.com/W1rfJayj

UPDATE http://pastebin.com/ZwRwBs71

The site is pretty much always slow at this point, but certainly more noticeable at some times than others. The very strange thing (at least to me) is that the site is very quick on the wordpress pages and when navigating from thread to thread on the forum but slower than molasses going from index to category and when using the new posts search.

The site typically reads between 125 and 200 users "Active in the past 15 minutes" during peak times of which 50 to 85 might be registered members.

The apache error log shows only two warnings regarding the MaxClients setting in the past 24 hours.

Here are the results of top

http://dl.dropbox.com/u/2318581/20111011-01.jpg

http://dl.dropbox.com/u/2318581/20111011-02.jpg

OK, those queries really are slow. They're taking between 12 and 30 seconds each. They're probably also responsible for the I/O spikes that you're seeing. Which probably means that your DB is doing unnecessary full-table scans.

First things first. Is there an index on the "authorid" and "topicid" columns of the "posts" table, and the "forum_id" column of the "topics" table?

You can use any MySQL administration tool such as phpmyadmin to check if there are indexes on those columns, or run the "SHOW INDEX FROM table_name" query using the MySQL command line.

If there isn't an index on those columns, you should create one for each, using the "CREATE INDEX" query (or by clicking the index button in a graphical admin tool such as phpmyadmin).

(Normally I'd ask for an EXPLAIN output too, but those queries look monstrous, and it would be difficult for you to fill all those N's with actual values for the test. So let's look at the usual suspects first. I'm suspecting that a missing index on the "topic_id" column is the culprit here. If that is not the case, we'll need an EXPLAIN output.)

Here is the results of the show index. It looks like they exist as best I can tell from reading this.

posts table http://pastebin.com/u5v7yvcL

topics table http://pastebin.com/L9NdQZYX

I also did an alter table on those this morning to rebuild them… Can't tell that it made any difference though.

Here is an update of the slow queries log

http://pastebin.com/dV77Z1BZ

Let me know what you'd like me to do with EXPLAIN and I'll be happy to try it.

Thanks!

Wow, those multi-column indexes look crazy.

Let's try this relatively simple query:

EXPLAIN SELECT author_id, topic_id FROM posts WHERE queued=N AND author_id=N AND topic_id IN(N,N,N);

The difficulty with EXPLAIN is that it needs actual values in place of those N's.

Browse your database and pick what seems to be the most common value for "queued". Insert it in place of the first N. (If it's not a number, enclose it in single quotes.) Also pick an author ID; use somebody who posts a lot. Replace the second N with the author ID. Finally, pick three topic IDs, preferably topics in which the author you picked has posted in. Use them to replace the last three N's. (You might be able to grab topic IDs from the URL. For example, the topic ID for this thread is 7896.)

Please post the result of your query. If you get an "Impossible WHERE" error, try using different values for the N's.

BTW, did you really pay $149 to use IPBoard? :o That's a lot of money for a forum with a nice theme. Do they have a customer support line where you can ask for help? Try sending them your slow query log.

Also, try meddling with "sortbuffersize" in my.cnf. (If it doesn't exist, add it.) The default value, I think, is 2M. Increase it a bit (4M or 8M) and see if it helps with your query times. You've got some very large indexes there…