Hey all,

As some of you already know, I am a computer programmer... when it comes to computer hardware and server issues I am fairly new. I know the basics on how to set up a webserver, ftp servers, mail servers, dns, etc... but when it comes to troubleshooting issues with them I'm kinda lost.

I've had some great conversations with a few smart people on this site over PM's... but I thought I'd toss this question out to the public to give them a break and to see what other computer guru's we have :)

So anyways, we are on the new server now. I am monitoring the server performance to try and catch any issues early. I opened the server's performance monitor (perfmon.exe) and was monitoring the "Page Faults/sec". I'd have to assume that we want this number to be zero... anything above that isn't good.

Well, it's not zero. Far from it. It's average is showing 18,000 page faults per second. I'd say most of the time it ranges from 0 to 50.... but ever 3 seconds or so it shows crazy numbers like 150,000 page faults a second (which brings the average up).

Any guesses what could be causing this? The server is showing plenty of free memory.

I do notice that mysqld.exe is using 0 cpu but every few seconds it goes to 100% then back to zero. It must be related to mysql then.... no clue :(

http://technet.microsoft.com/en-us/library/bb742534.aspx
http://msdn.microsoft.com/workshop/server/iis/tencom.asp

How much ram does the box have?
What is the pagefile size set to?

From my.ini - what are these set to?

thread_cache_size
table_cache
innodb_buffer_pool_size
wait_timeout
key_buffer_size

The server is actually a VM with 3gigs of ram running windows 2008 server R2 standard. It has on average 2.2 gigs of available ram.

The pagefile size is set to 3071MB

From the my.ini :

thread_cache_size = 8
table_cache = 256
innodb_buffer_pool_size = 513M
wait_timeout = could not find this in the my.ini file
key_buffer_size = 264M

http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html

This should help you - I would try doing the following:

mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \
--table_cache=32 --read_buffer_size=8K \
--net_buffer_length=1K &

your key_buffer size is very high and might explain the paging.
lowering table_cache should also help.

That link you gave me shows a key buffer size of about 8 megs. You recommend me setting it to half a meg? I just want to make sure before I make the change. I also assume I need to restart mysql after running that command?

Keep reading that page down to where it talks about lots of connections and low memory. I do not know how many connections you need to have open at peak times. You could step down in increments until the page faults stop

==========copied from page=======================
If you have only 128MB of memory and only a few tables, but you still do a lot of sorting, you can use something like this:

shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M
If there are very many simultaneous connections, swapping problems may occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections.

With little memory and lots of connections, use something like this:

shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \
--read_buffer_size=100K &
Or even this:

shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \
--table_cache=32 --read_buffer_size=8K \
--net_buffer_length=1K &

Ya, I saw that in the document. I wasn't sure what they meant by "little memory". I assumed that meant mysql only had 256 megs or less.. but i have more than that.

Ok - well we also do not know what they mean by 'lots' of connections - here is a full list of tuning stuff from mysql: http://www.mysql.com/why-mysql/performance/index.html

There are two ways to approach it - slowly tweak down until the page faults reduce - or go low and tweak up until they start again. It is very difficult to know without a rough idea how many connections are being used at any one time.