Windows Server 2008 + Mysql Optimization

[sk8er_boi]

I have my forum running on Windows 2008 + Mysql.
Server was fast at first with same load but it got slower. I don't know what is the cause. I need someone to fix and optimize or find out why is my server is slow.

[Mat Barrie]

There's a few questions which might help get to the bottom of this-

[*]Are you using Apache or IIS? [*]Do you have PHP loaded as CGI or SAPI? [*]If CGI, are you using FastCGI? [*]Do you have cache (XCache, WinCache) installed and running? [*]What version of PHP and MySQL are you using? [*]What are the server specifications? [*]What's the PHP memory limit set to in php.ini?

[Dmacleo]

also is it 2008 or 2008r2.
you'd be surprised how many just say 2008.

[sk8er_boi]

There's a few questions which might help get to the bottom of this-

[*]Are you using Apache or IIS?

[*]Do you have PHP loaded as CGI or SAPI?

[*]If CGI, are you using FastCGI?

[*]Do you have cache (XCache, WinCache) installed and running?

[*]What version of PHP and MySQL are you using?

[*]What are the server specifications?

[*]What's the PHP memory limit set to in php.ini?

also is it 2008 or 2008r2.

you'd be surprised how many just say 2008.

1. IIS 2. CGI 3. FastCGI 4. I have wincache installed. 5. Php 5.3.13 Mysql 5.5 6. Intel Xeon x3210 quadcore cpu.4gb ram. 7. 128M Yes it's r2

[Nevo]

Get a Linux Operating System :P

[General.V2]

In IIS 7.5, what is the folder directory you are pointing your site to for your forum?
For example C:inetpubwwwroot

If your site is pointing to a network share, such as server1drive1forum then that may be the issue.

Another thing to try is changing the mysql host from localhost to 127.0.0.1
Using localhost occasionally has problems on windows because of the way you may have your local dns server setup as.

[sk8er_boi]

In IIS 7.5, what is the folder directory you are pointing your site to for your forum?

For example C:inetpubwwwroot

If your site is pointing to a network share, such as server1drive1forum then that may be the issue.

Another thing to try is changing the mysql host from localhost to 127.0.0.1

Using localhost occasionally has problems on windows because of the way you may have your local dns server setup as.

No it is on a physical path. Also i changed localhost to 127.0.0.1 in conf_global.php. Don't think it improved something.

[General.V2]

You may want to go to the Windows performance monitor in the control panel to make sure your hard drive isn't being overloaded from running both a high traffic forum and high traffic mysql server. If you don't have a ssd, that may be the case here.

If you look at the link below, it looks as if your server hangs a longer than usual amount of time on every file request, both scripts and images.
http://www.webpagetest.org/result/120919_6X_6C3/1/details/cached/

This may be because the overloaded hdd issue stated above. At the above link, you will notice there is a lot of green on the first request, that means the server doesn't recognise the request until after the seconds it shows.

Not by any means am I saying this is the problem for certain. Just make sure your not overloading your hard drive. If you are not, then post back and I'll troubleshoot some more.

[Grumpy]

6. Intel Xeon x3210 quadcore cpu.

My knowledge of windows server administration hardly surpasses that of a hamster...
but. that cpu is really old. It's the cheapest quad core of 5.5 years ago. A low end dual core of today's CPU will beat its performance by a multiple of 2, quite literally. Might wanna consider it as a time to switch things up.

[sk8er_boi]

You may want to go to the Windows performance monitor in the control panel to make sure your hard drive isn't being overloaded from running both a high traffic forum and high traffic mysql server. If you don't have a ssd, that may be the case here.

If you look at the link below, it looks as if your server hangs a longer than usual amount of time on every file request, both scripts and images.

http://www.webpagete...details/cached/

This may be because the overloaded hdd issue stated above. At the above link, you will notice there is a lot of green on the first request, that means the server doesn't recognise the request until after the seconds it shows.

Not by any means am I saying this is the problem for certain. Just make sure your not overloading your hard drive. If you are not, then post back and I'll troubleshoot some more.

Hi

I don't have a ssd disk. How can i exactly check if i had disk problems?

My knowledge of windows server administration hardly surpasses that of a hamster...

but. that cpu is really old. It's the cheapest quad core of 5.5 years ago. A low end dual core of today's CPU will beat its performance by a multiple of 2, quite literally. Might wanna consider it as a time to switch things up.

Server was superfast when i first got it. And my traffic was almost the same. I have no option to switch anything until january. Because its a bundle server.

[Dmacleo]

you have enough memory dedicated to sql on the box?
also if you are running a "Green" drive or a variable speed drive dump it.

[sk8er_boi]

you have enough memory dedicated to sql on the box?

also if you are running a "Green" drive or a variable speed drive dump it.

It's a GB0500EAFYL HP 500GB 3G SATA 7.2K RPM 3.5 disk. Got hotplug and server stuff. I don't think its bad.

What setting do you mean exactly?

[Grumpy]

If you're still looking for assistance, you might want to try the management section in WHT forums. A bigger pool of potential service there.

[Dmacleo]

been so long since I ran mysql on windows that my memory is fuzzy, but I thought there was way to allocate (prioritize) memory like there is on ms sql.

[Mat Barrie]

1. IIS

2. CGI

3. FastCGI

4. I have wincache installed.

5. Php 5.3.13 Mysql 5.5

6. Intel Xeon x3210 quadcore cpu.4gb ram.

7. 128M

Yes it's r2

Just to hone in on that mention of wincache there - is your forum configured to actually use it? If you don't have "$INFO['use_wincache'] = 1;" in your conf_global.php, then it is not actually using wincache for caching and you have an opportunity for improvement right there.

Get a Linux Operating System :tongue:

This is not constructive in the slightest. He is running Windows, and is asking for advice on optimising it. Not snarky comments to change OS because you don't like what he's running.

Hi

I don't have a ssd disk. How can i exactly check if i had disk problems?

Open Control Panel > Performance Information and Tools. Click "Advanced tools" from the sidebar, and select "Open Performance Monitor". Once open, go to Monitoring Tools > Performance Monitor, and click on the + symbol. This will open the Add Counters dialog. In the list box, scroll down to LogicalDisk and click on that, which adds all the counters for disks in to the box below it. Select the Avg Disk Write Queue Length, and under instances select the drive that contains MySQL data and web pages. Click on "Add >>" and repeat for the Avg Disk Read Queue Length counter. Then click OK. If all is well with your server, the graph should stay quite low and relatively static.

I can't speak for what is an optimal value unfortunately, but if it's really high or spikes a lot, that's an indication that your server may be running slow as a result of slow disk access. Fixing this may require replacing the disk, or it could just be a matter of giving the disk a good old defragment (if your data is scattered, the disk needs to seek to it every time it must pull it up. NTFS automatically defragments when idle, but idle time isn't exactly something web servers have a whole lot of).

Server was superfast when i first got it. And my traffic was almost the same. I have no option to switch anything until january. Because its a bundle server.

Although you may gain from an upgrade, that specification should be sufficient to run what you are quite happily for now.

been so long since I ran mysql on windows that my memory is fuzzy, but I thought there was way to allocate (prioritize) memory like there is on ms sql.

Sort of. You allocate memory in my.ini/my.cnf to the storage engines directly (this is irrelevant on MSSQL as there's only one engine). Some handy tips on optimising this can be found by searching Google for MySQL memory allocation. This page seems quite good. However, it needs to be kept in mind that memory allocated to MySQL cannot be used by IIS/PHP - which could result in heavy swapping under high usage scenarios. Most allocation tips seem to be geared toward dedicated database servers, which most of us don't have the luxury of having.

[Dmacleo]

was wondering if it would be in the mycnf settings for this but forgot to look, wasn't sure if it was the same on mysql for windows or not.
and I am wondering if the swapping IS the actual issue here.
would explain the symptoms.
also check advanced system properties (rt click my computer) and under perfromance tab make sure background services is chosen. it SHOULD have defaulted to this but you never know.
also check your page file settings in virtual memory. its best if its on a drive that does not also house the C partition but if it HAS to be that drive then C partition is best. if setting swap file on other partitions on same physical drive that OS is installed on then head seek can be issue.