New SQL Server

[Bas]

I do agree RAID10 is faster if you don't have enough drives.

But I do not agree on the number of cores, it's already proven that many cores without the much needed memory band-weight is useless.
For everything the CPU does it's pulling data from the memory and pushing the result back.
Caching does a bit, but upto a point, it still needs to write it to the memory...and in the end to the harddisk.

I doubt you are even able to get all cores fully loaded at all with MySQL...sure a CPU bench running in cache could, but real life....no way.

This problem isn't new, it was already there at the 486DX100 ages, the CPU was too fast for the memory and hardly did any better then the 486DX66....and in those days the memory speed wasn't that far back on the CPU speed.
8 cores, with terribly limited band-weight? No way...sorry...waste of money on the CPU, 4 core would have worked just the same.

[Arantor]

For many typical apps, I've no doubt you may be correct, Bas, but this thread has seen comment from the admin of this forum, and indeed I know the admin of one of the largest SMF forums worldwide has seen this thread too.

For the load that SMF generates, how it generates it and how it works in terms of MySQL it is simply more CPU intensive than other apps, when you have properly built indexes stored in memory, plus memory tables.


Out of interest, you mention your dedicated server: how big's your forum (we can't see it, the link appears to be broken)?

[Sabre™]

Out of interest, you mention your dedicated server: how big's your forum (we can't see it, the link appears to be broken)?

Typo in his link, [THIS] seems to be it.

[rsw686]

If http://forum-en.msi.com/ is running on the server in Bas's sig there is something not configured right as the page load time was 6.5 seconds.

[Arantor]

Thanks Sabre. Interesting.

A forum of 890k posts is no lightweight, but the load is slightly different for 890k posts/83k members vs 2m posts / 200k members, especially considering the extra things built into this site that do hook into the forum code, such as the mod site, the support site and so on.

The load on this forum is not the same as it is on a straight forum as a result and more of the operations here are thus more CPU intensive (particularly around the mod site) than I/O intensive.

[dschwab9]

The number of cores is the primary reason we upgraded. Our old database server was a quad core and our slow downs were due to all the cores being maxed out at peak times. We didn't have any issues with disk IO. The new server with additional cores fixed the problems.

[Bas]

If http://forum-en.msi.com/ is running on the server in Bas's sig there is something not configured right as the page load time was 6.5 seconds.

That's not a normal load time, but it can happen when many people are searching at the same time.
We need the max possible search index and that almost doubles the database size, and when many people use it, it can slow it down a bit sometimes.
If you have a forum without a lot of searching that is a lot easier to configure and way less hardware is needed.

[Yigal]

11GB RAM

Short it down to 8GB and let me borrow other 3GB's of RAM for my PC

I have 4

The server has 12GB not 11

[dschwab9]

That's not a normal load time, but it can happen when many people are searching at the same time.
We need the max possible search index and that almost doubles the database size, and when many people use it, it can slow it down a bit sometimes.
If you have a forum without a lot of searching that is a lot easier to configure and way less hardware is needed.

You should seriously consider using sphinx if you're not already. We run a dedicated Sphinx server for this site and searches happen very fast with no measurable load on the web or database servers.

[Hell Raiser]

11GB RAM

Short it down to 8GB and let me borrow other 3GB's of RAM for my PC

I have 4

The server has 12GB not 11

O...oO you have 4GB 

Throw some GB's on me 

[Something like that]

The number of cores is the primary reason we upgraded. Our old database server was a quad core and our slow downs were due to all the cores being maxed out at peak times. We didn't have any issues with disk IO. The new server with additional cores fixed the problems.

If you're running more than 4 InnoDB threads, you should update to MySQL 5.4 as it fixes the scalability issues. Otherwise, if you are running more than 4, reducing to 4 InnoDB threads will actually improve performance.

[Something like that]

As far as traffic goes, so far this month we have served 54.4 million page views and 92 million hits from our main datacenter. The main SQL server is averaging 230 queries per second.

The challenge is the growth has been pretty big - doubled in the last month or so. Very hard to keep up with feeding that with more hardware for an organization run by volunteers to produce a free product 

Damn. I do only 4.5 million page views per month, not 60 (though 30 million PHP requests a month). And I only average 90 queries per second. But I do it all in 1 GB of RAM

[Bas]

That's not a normal load time, but it can happen when many people are searching at the same time.
We need the max possible search index and that almost doubles the database size, and when many people use it, it can slow it down a bit sometimes.
If you have a forum without a lot of searching that is a lot easier to configure and way less hardware is needed.

You should seriously consider using sphinx if you're not already. We run a dedicated Sphinx server for this site and searches happen very fast with no measurable load on the web or database servers.

Derek, I have found the reason.
It was Microsofts poor MSNbot 2.0b, it misbehaves and ignores robots.txt so I completly banned that bot via .htcaccess on their IP's.
That bot is hitting our server extremely hard, probably other sites like this one too.
Simply entered this into it:

#Block Inktomi and MSNbot 2.0b
order allow,deny
deny from 74.6
deny from 72.30
deny from 65.55.207
deny from 65.55.106
deny from 65.55.107
allow from all

And hoppa, problem solved
One does not want misbehaving bots...and Inktomi and MSNbot 2.0b are the worst on the planet.

Yesterday MSNbot 2.0b hit our website with about 50000 pageviews! That 1 every 2 seconds.
Even after I set crawldelay to 120 seconds, all others obey my setting, MSNbot 2.0b does not, and I suspect it to do even near 90k a day, as the peaks are very high.
Not even Google does anywhere close to that.

[Something like that]

I'd love to have another 50,000 pageviews. I'd watch my CPU usage go up 3%

[Bas]

The load wouldn't go up that much, but it puts useless stress on the database/harddisks as well as useless bandweight.
I rather have it feed pages quickly to users then feeding some troll MSNbot 2.0b. (=Bing that nobody cares about anyway )