I could really use some help optimizing my two Linodes. Both the mysql linode and apache/php linode are Linode 1024 plans.

My site has a Wordpress homepage, but the primary source of my traffic is the IPBoard forum.

I am experiencing really slow load times on my forum when navigating from the forum index into a category and also when running a 'view new posts' query.

My web linode is consistently averaging 50-60% cpu usage and ram usage is running around 60-70% as well.

My database linode seemingly isn't doing anything... The cpu average is around 0 and the ram usage is about 5-7%. I definitely feel like I have something or multiple somethings misconfigured.

Apache Config

mysql config

Advice very much appreciated!

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

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?

_________________

/* TODO: need to add signature to posts */

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.

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 "author_id" and "topic_id" 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? 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 "sort_buffer_size" 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...