mysql increase queries per second avg

my queries per second avg are very very low

Queries per second avg: 6.591
most other linux machines are at 20 with an i686

irrelevant, unless you're sure they're running the same level of traffic.

At first glance I'd say that for any two machines with QPS of 6.5 and 20, the machine with 20 has about 3 times as many clients requesting data.

In other words, the number of queries per second is based largely on the number of requests MySQL gets, not how long it takes to process them.

Now if you're saying that each server gets the same number of requests from the same number of clients, and that the Mac takes 3 times longer to process them, that's a different issue, but that is not a claim supported by the data at hand.

what is average for mac on queries per second?

Hmm, an iMac G3 running 10.0 on 128MB of RAM, or a dual 2.3GHz G5 with 16 GB of ram?
What size tables? how many client connections? What kind of queries? SELECT? INSERT? UPDATE?
What about the disk subsystem you're running on?
What database file format are you using? InnoDB? MyISAM?
In other words, too many variables to answer directly.

It really doesn't matter what other people are getting because environment is a major factor. I can tell you, for example, that most of my MySQL machines hum along at about 200-300 queries per second all day every day, and they're not busy - I specifically keep them well under capacity so that I can deal with traffic spikes - they peak around 2000 queries per second depending on what they're doing, By that measurement it blows your Linux machine out of the water, but that still doesn't mean anything since the environment is very different.

Install Mysql Administrator, it has a nice pane called Health. I will recommend you to check status variables, it will get you valuable information.

I run mysql on my g5 with nice traffic - 70 concurrent connections. Here is my settings:

<code>
join buffersize 131072
key buffersize 67108864
query alloc_blocksize 8192
query cachelimit 4194304
query cachesize 2097152
query preallocsize 8192

</code>

Your load average is way too high, as is the System CPU time. Your machine shouldn't be spending 72% of its time in System, and a 5-minute load average of 5.86 is way high, too.

Since it's only spending 11% in user space I'm guessing you're not stressing the web server side of things, but something is amiss on your machine that's causing it to spend too much time on system tasks.

What other processes are consuming any significant time in top. What kind of disk setup are you running there (it might be disk access latency that's bogging you down).

That activity looks a lot better. It appears that something is spiking the load. It would be worthwhile tracking it down.

With regards to iostat you'll find it more useful if you add some numbers to the end. As it stands, iostat will give you the average over the machine's uptime, which isn't really relevant most of the time. Instead, add values to repeat the information periodically, for example iostat 5 would repeat the iostat display every 5 seconds, giving you a better feel for how much traffic is going on. Adding a second digit tells iostat to output that many samples, e.g. iostat 5 10 will output 10 lines, 5 seconds apart, and then quit.

Key buffer usage is: 61.71 MB of 1GB

why isn't it using the full gig I gave it?

How big is your database? MySQL is only going to use as much memory as needed to cache the active data. If your entire database is only 50MB then I wouldn't expect MySQL to use much more than that.

I'd only expect MySQL to use 1GB is when you have more than that amount of data active on the system.

2-3GB for a discussion board data is a decent size, but MySQL is only going to cache recently-requested data, so it's entirely possible that 60MB is all that it needs (you can fit a lot of posts in 60MB), so I'm not at all worried about that.

So your problem is your web server.

Unless you're doing an extreme amount of traffic, httpd should not be using 95% of the cpu.

I have seen other reports of httpd consuming large amounts of CPU time, often related to the performance cache. It might be worth turning off the performance cache (as counter-intuitive as that might seem). Other than that, enabling apache's status module might give you some insight as to what it's doing.

If the cpu load is caused by web traffic then the problem is the discussion board software you're using. It sounds horribly inefficient if it's taking that much CPU power to run a discussion board for 3,000 visitors/day - that's a significant number of users, but it isn't extreme.

I don't know the discussion board you're using, but it sounds like it needs some serious code optimization. It can take a lot of work to properly optimize a discussion board.

By comparison I run several boards much larger than that, and my cpu loads are in the teens. Mind you, I do run separate application and database servers, so they're not on the same hardware, but even so I think the code needs tweaking.

Do you have KeepAlive On in httpd.conf?

Yes the boards are coded very very badly.

If each connection consumes 8% of the CPU then you're not going to handle more than 12 active connections. No wonder your MySQL query rate is slow.

Time to either crawl that code and find out what it thinks it's doing, or switch to a better board product.