Advertisement:

Author Topic: New SQL Server  (Read 286127 times)

Offline Bas

  • Jr. Member
  • **
  • Posts: 371
  • Gender: Male
  • Ex MSI forum Admin.
    • HardwareGurus.net
Re: New SQL Server
« Reply #140 on: September 04, 2009, 12:53:16 PM »
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.
Greetings Bas.

Offline

  • SMF Friend
  • SMF Legend
  • *
  • Posts: 61,633
Re: New SQL Server
« Reply #141 on: September 04, 2009, 03:54:22 PM »
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)?
Damaged people are dangerous. We know we can survive.

Offline Sabre™

  • SMF Hero
  • ******
  • Posts: 2,523
  • Gender: Male
  • IF IT AINT BROKE, I CAN FIX IT TILL IT IS!!
Re: New SQL Server
« Reply #142 on: September 04, 2009, 06:35:47 PM »
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.
Do NOT give admin and/or ftp details to just anybody, see if they are trust worthy first!!  Do your homework ;)


Offline rsw686

  • Sophist Member
  • *****
  • Posts: 1,008
    • The Reptile File
Re: New SQL Server
« Reply #143 on: September 04, 2009, 06:42:27 PM »
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.
The Reptile File
Everything reptile for anyone reptile friendly

Aquaria Talk
Community for freshwater and saltwater aquariums enthusiasts

Offline

  • SMF Friend
  • SMF Legend
  • *
  • Posts: 61,633
Re: New SQL Server
« Reply #144 on: September 04, 2009, 06:42:47 PM »
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.
Damaged people are dangerous. We know we can survive.

Offline dschwab9

  • IT Director/Server Admin
  • Server Team
  • SMF Hero
  • *
  • Posts: 5,320
  • Gender: Male
    • Technology, Ministry and Other Ramblings
Re: New SQL Server
« Reply #145 on: September 05, 2009, 12:11:48 PM »
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.

Offline Bas

  • Jr. Member
  • **
  • Posts: 371
  • Gender: Male
  • Ex MSI forum Admin.
    • HardwareGurus.net
Re: New SQL Server
« Reply #146 on: September 06, 2009, 04:44:21 AM »
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.
Greetings Bas.

Offline Yigal

  • SMF Hero
  • ******
  • Posts: 2,703
  • Gender: Male
  • Om Nom Nom?
Re: New SQL Server
« Reply #147 on: September 08, 2009, 03:05:08 AM »
11GB RAM

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

I have 4 :P

The server has 12GB not 11 :P
Yigal V.
Contact me by PM

do yourself a favour and not annoy support helpers by spamming or messaging for support
1.x Support | 2.x Support | 5Mods

Offline dschwab9

  • IT Director/Server Admin
  • Server Team
  • SMF Hero
  • *
  • Posts: 5,320
  • Gender: Male
    • Technology, Ministry and Other Ramblings
Re: New SQL Server
« Reply #148 on: September 09, 2009, 05:29:01 AM »
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.

Offline Hell Raiser

  • Jr. Member
  • **
  • Posts: 384
  • Gender: Male
Re: New SQL Server
« Reply #149 on: September 09, 2009, 05:38:27 AM »
11GB RAM

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

I have 4 :P

The server has 12GB not 11 :P

O...oO you have 4GB  :P

Throw some GB's on me  :P
Need Help with SMF ? Ask me may be i can help you out




Offline Mark Rose

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,498
  • Gender: Male
  • Performance Freak
Re: New SQL Server
« Reply #150 on: September 13, 2009, 02:16:54 AM »
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. :)
Hello, IT. Have you tried turning it off and on again?

Offline Mark Rose

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,498
  • Gender: Male
  • Performance Freak
Re: New SQL Server
« Reply #151 on: September 13, 2009, 02:31:13 AM »
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 ;)
Hello, IT. Have you tried turning it off and on again?

Offline Bas

  • Jr. Member
  • **
  • Posts: 371
  • Gender: Male
  • Ex MSI forum Admin.
    • HardwareGurus.net
Re: New SQL Server
« Reply #152 on: September 16, 2009, 05:46:52 AM »
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 ;D
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.
« Last Edit: September 16, 2009, 05:56:24 AM by Bas »
Greetings Bas.

Offline Mark Rose

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,498
  • Gender: Male
  • Performance Freak
Re: New SQL Server
« Reply #153 on: September 16, 2009, 08:53:37 AM »
I'd love to have another 50,000 pageviews. I'd watch my CPU usage go up 3% :D
Hello, IT. Have you tried turning it off and on again?

Offline Bas

  • Jr. Member
  • **
  • Posts: 371
  • Gender: Male
  • Ex MSI forum Admin.
    • HardwareGurus.net
Re: New SQL Server
« Reply #154 on: September 16, 2009, 09:38:59 AM »
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 :P)
Greetings Bas.