New server - mysql queries

[wheel]

I've got a friends site on my server that is absolutely killing my server as the result of mysql queries. I don't think there's much fix for tuning, the site just needs to do a lot of difficult queries, and they've developed a lot of traffic through the years.

Looking to buy a new server to alleviate the lags as the result of the mysql queries.

Current, 2X Xeon with hypgerthreading, 8 gigs of ram, raid scsi hd's.

Do you think this is going to fix the problems going forward:
2X AMC 8 core CPU's.
8 gigs of DDR 3 ram
SSD hard drive (either sata or pcie-x interface)

Does that fix my problems, and make the rest of my sites go back to serving instantly again? I've always prided myself on instant web site serving and need to get back to that happy place.

[J_RaD]

don't use SSD drives for this.

raid the fastest spinning 6.0G sata drives you can find.

[wheel]

Really? I know you know more about this than me, but how do you get sata as being faster than something like a pcie ssd card? I thought they were downtown when it came to speed.

And if I go sata, don't I need a mobo that will handle 6.0g as well? (and what is that, sataIII?)

Well, I just googled. Sata III is 6g, so about 600mb/s. and that's comparable to the 500mbs that pcie ssd cards get I think. So you're right. And I gather sata drives are both cheaper and more reliable than current ssd.

Thanks!

[coopster]

I don't think there's much fix for tuning, the site just needs to do a lot of difficult queries

You would be surprised. I helped a company take an extreme query from 92 seconds to .002 ms just by analyzing, restructuring and indexing. It's worth investigating.

[wheel]

Yeah, I've got them looking into that right now. I added these two lines to my.cnf:
#long_query_time = 1
#log-slow-queries = /var/log/mysqld/mysql-slow.log

And that showed the specific queries causing the problem. Basically one page where there's a huge pig of a query to generate all the data, and a very popular page.

If he gets it down faster, maybe no new server needed. if not, looks like I'm in for a long overdue server upgrade.

I may have todo some more research on the SATA III because it seems like the drives won't actually push through big volumes as fast as an SSD drive.

[J_RaD]

a pcie ssd card? I thought they were downtown when it came to speed.

And if I go sata, don't I need a mobo that will handle 6.0g as well? (and what is that, sataIII?)

Well, I just googled. Sata III is 6g, so about 600mb/s. and that's comparable to the 500mbs that pcie ssd cards get I think. So you're right. And I gather sata drives are both cheaper and more reliable than current ssd

SSD's don't like to be I/O thrashed, well writes really. Lots of writes all the time will shorten their life span to as low as 3-5 years. So yea cheaper and will last lots longer....and with RAID you can get pretty close to SSD speed.

SSD drives are awesome but they have these drawbacks that will mean spinning disks will be around for a very long time. Even machines that use an SSD will still have the good old spinning platters in them for large data storage and all the high write system files to live.

look up the WD VelociRaptor, they make a 10k RPM 6G sata drive, just mount them so they can be cooled properly.


andddddd this is just throwing some wild idea out there, but a RAMDisk, yea i know it sounds like 1994 butttttttt ram is cheap, we have lots of it, they are actually still used, and they'd shred a DB! but volatile yes.

[edited by: J_RaD at 2:51 am (utc) on Oct 26, 2011]

[wheel]

thanks a bunch, i appreciate it.