homepage Welcome to WebmasterWorld Guest from 54.227.11.45
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Hardware and OS Related Technologies / Webmaster Hardware
Forum Library, Charter, Moderator: open

Webmaster Hardware Forum

    
New server - mysql queries
wheel




msg:4379280
 6:52 pm on Oct 25, 2011 (gmt 0)

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




msg:4379284
 7:07 pm on Oct 25, 2011 (gmt 0)

don't use SSD drives for this.

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

wheel




msg:4379339
 9:31 pm on Oct 25, 2011 (gmt 0)

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




msg:4379406
 12:05 am on Oct 26, 2011 (gmt 0)

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




msg:4379428
 1:25 am on Oct 26, 2011 (gmt 0)

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




msg:4379452
 2:36 am on Oct 26, 2011 (gmt 0)


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




msg:4379458
 2:50 am on Oct 26, 2011 (gmt 0)

thanks a bunch, i appreciate it.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Hardware and OS Related Technologies / Webmaster Hardware
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved