Results 1 to 18 of 18
-
04-16-2005, 03:32 PM #1Poooooonnyyy :*
- Join Date
- Jan 2003
- Location
- Canada
- Posts
- 5,073
Optimizing MySQL for heavy insert/update statements?
Hello Everyone!
I currently run a pretty heavy mysql server that runs with the following specs
su-3.00# mysqladmin status
Uptime: 8587 Threads: 19 Questions: 3710907 Slow queries: 120 Opens: 2772 Flush tables: 1 Open tables: 2087 Queries per second avg: 432.154
su-3.00# uptime
3:31PM up 13 days, 2:20, 1 user, load averages: 7.97, 9.74, 9.47
with the following conf file - http://deltaanime.net/my.cnf
We've compiled mysql with all the nice modes we could find, but it still doesn't help enough We've also put indexes in every table that could use them, but still it's not helping us out
Anyone recommend any conf file changes that might help with the load? =\
Thanks!
~FranciscoBuyVM - OpenVZ & KVM Based VPS Servers - Chat with us
- All popular VPN methods supported
- Affordable offloaded MySQL & DDoS protection
- 5GB backup space, unmetered private LAN bandwidth & native IPv6 included. All with a strong serving of pony
-
04-16-2005, 04:45 PM #2Retired Moderator
- Join Date
- May 2004
- Location
- Toronto, Canada
- Posts
- 5,105
Sorry bud, not my forte. I looked at the mysql config file and didnt see anything obvious.
Can I ask, the specs of the machine itself?
Is it dedicated to mysql or is a shared use box?
Multiple mysql databases or just one or two and if so whats the product?
Also finally, shouldnt this be in the technical help forum? Maybe there you would get some more help.CloudNexus Technology Services
Managed Services
-
04-16-2005, 06:46 PM #3Web Hosting Master
- Join Date
- Jan 2003
- Posts
- 1,715
Without knowing the specs of the hardware or the database, I'd say that's a pretty decent query rate. Although InnoDB or BDB will give you better locking performance, lock contention doesn't drive up the load average like that. Do a 'vmstat 5 5' and we can see if the bottle neck is CPU or RAM.
Game Servers are the next hot market!
Slim margins, heavy support, fickle customers, and moronic suppliers!
Start your own today!
-
04-16-2005, 07:20 PM #4Backup Guru
- Join Date
- Feb 2002
- Location
- New York, NY
- Posts
- 4,618
Based on the loads, it looks like more of a hardware issue. You probably either need more RAM, or your hard drives aren't keeping up. For heavy write situations, a good SCSI drive (or multiple in a RAID10 configuration) should do well.
Scott Burns, President
BQ Internet Corporation
Remote Rsync and FTP backup solutions
*** http://www.bqbackup.com/ ***
-
04-16-2005, 08:26 PM #5Poooooonnyyy :*
- Join Date
- Jan 2003
- Location
- Canada
- Posts
- 5,073
Oh, i completely forgot specs, heh.
It's a dual xeon 3Ghz w/ 2GB of ram and 2 120GB sata's
~FranciscoBuyVM - OpenVZ & KVM Based VPS Servers - Chat with us
- All popular VPN methods supported
- Affordable offloaded MySQL & DDoS protection
- 5GB backup space, unmetered private LAN bandwidth & native IPv6 included. All with a strong serving of pony
-
04-16-2005, 09:51 PM #6Artificial Intelligence
- Join Date
- Feb 2005
- Location
- Minnesota
- Posts
- 973
My guess would be it's your hard drives. If you want to stay sata, try Western Digital Raptor drives they have less then half the access times of most sata drives at 4.5ms 10k/rpm(36GB or 74GB). If you want faster and larger yet try Maxtor Atlas II SCSI Drives 3.0ms 15k/rpm(147GB) The Atlas II is a little spendy though at about $1300-1500USD. Your best bet is using some wd raptor drives in a raid set.
███ AfterNorth Innovative solutions for tomorrow, today.
███ 0spam.org AntiSpam for Service Providers ♪
███ DotNetInvoice Online Billing Solutions ♫
███ Professional Services Since 1996
-
04-16-2005, 10:09 PM #7Problem Solver
- Join Date
- Mar 2003
- Location
- California USA
- Posts
- 13,681
Is the mysql on its own drive?
Steven Ciaburri | Industry's Best Server Management - Rack911.com
Software Auditing - 400+ Vulnerabilities Found - Quote @ https://www.RACK911Labs.com
Fully Managed Dedicated Servers (Las Vegas, New York City, & Amsterdam) (AS62710)
FreeBSD & Linux Server Management, Security Auditing, Server Optimization, PCI Compliance
-
04-16-2005, 10:11 PM #8Backup Guru
- Join Date
- Feb 2002
- Location
- New York, NY
- Posts
- 4,618
Originally posted by Kiamori
If you want faster and larger yet try Maxtor Atlas II SCSI Drives 3.0ms 15k/rpm(147GB) The Atlas II is a little spendy though at about $1300-1500USD. Your best bet is using some wd raptor drives in a raid set.Scott Burns, President
BQ Internet Corporation
Remote Rsync and FTP backup solutions
*** http://www.bqbackup.com/ ***
-
04-16-2005, 10:12 PM #9Poooooonnyyy :*
- Join Date
- Jan 2003
- Location
- Canada
- Posts
- 5,073
Originally posted by Kiamori
My guess would be it's your hard drives. If you want to stay sata, try Western Digital Raptor drives they have less then half the access times of most sata drives at 4.5ms 10k/rpm(36GB or 74GB). If you want faster and larger yet try Maxtor Atlas II SCSI Drives 3.0ms 15k/rpm(147GB) The Atlas II is a little spendy though at about $1300-1500USD. Your best bet is using some wd raptor drives in a raid set.
Was kinda hoping for a solution to the conf file, if there was even anything wrong with it.
~FranciscoBuyVM - OpenVZ & KVM Based VPS Servers - Chat with us
- All popular VPN methods supported
- Affordable offloaded MySQL & DDoS protection
- 5GB backup space, unmetered private LAN bandwidth & native IPv6 included. All with a strong serving of pony
-
04-16-2005, 10:14 PM #10Backup Guru
- Join Date
- Feb 2002
- Location
- New York, NY
- Posts
- 4,618
Originally posted by DeltaAnime
SCSI's and such would rock, but as far as i can tell, sago doesn't do them very often =\
Was kinda hoping for a solution to the conf file, if there was even anything wrong with it.
~FranciscoScott Burns, President
BQ Internet Corporation
Remote Rsync and FTP backup solutions
*** http://www.bqbackup.com/ ***
-
04-16-2005, 10:17 PM #11Problem Solver
- Join Date
- Mar 2003
- Location
- California USA
- Posts
- 13,681
Is this a dedicated mysql server? Is apache also serving pages? What os? What kernel?
Steven Ciaburri | Industry's Best Server Management - Rack911.com
Software Auditing - 400+ Vulnerabilities Found - Quote @ https://www.RACK911Labs.com
Fully Managed Dedicated Servers (Las Vegas, New York City, & Amsterdam) (AS62710)
FreeBSD & Linux Server Management, Security Auditing, Server Optimization, PCI Compliance
-
04-16-2005, 10:21 PM #12Artificial Intelligence
- Join Date
- Feb 2005
- Location
- Minnesota
- Posts
- 973
Originally posted by bqinternet
Or a 15K Seagate Cheetah at 36GB, for about $160... Put several in RAID 10 for extra fun.███ AfterNorth Innovative solutions for tomorrow, today.
███ 0spam.org AntiSpam for Service Providers ♪
███ DotNetInvoice Online Billing Solutions ♫
███ Professional Services Since 1996
-
04-16-2005, 10:21 PM #13Poooooonnyyy :*
- Join Date
- Jan 2003
- Location
- Canada
- Posts
- 5,073
Originally posted by thelinuxguy
Is this a dedicated mysql server? Is apache also serving pages? What os? What kernel?
Runs FreeBSD 5, custom compiled kernel
~FranciscoBuyVM - OpenVZ & KVM Based VPS Servers - Chat with us
- All popular VPN methods supported
- Affordable offloaded MySQL & DDoS protection
- 5GB backup space, unmetered private LAN bandwidth & native IPv6 included. All with a strong serving of pony
-
04-16-2005, 10:22 PM #14Artificial Intelligence
- Join Date
- Feb 2005
- Location
- Minnesota
- Posts
- 973
Originally posted by DeltaAnime
It's a webserver, so it's doing the whole mix. The MySQL is running a anime torrent site, so it takes a thrashing.
Runs FreeBSD 5, custom compiled kernel
~Francisco███ AfterNorth Innovative solutions for tomorrow, today.
███ 0spam.org AntiSpam for Service Providers ♪
███ DotNetInvoice Online Billing Solutions ♫
███ Professional Services Since 1996
-
04-16-2005, 10:26 PM #15Poooooonnyyy :*
- Join Date
- Jan 2003
- Location
- Canada
- Posts
- 5,073
Originally posted by Kiamori
seperate your sql from the rest that will help the most. I had the impression this was ded sql server.
If i had something like a 2.8GHz p4 with a gig or two of ram, would this suffice with my mysql loads?BuyVM - OpenVZ & KVM Based VPS Servers - Chat with us
- All popular VPN methods supported
- Affordable offloaded MySQL & DDoS protection
- 5GB backup space, unmetered private LAN bandwidth & native IPv6 included. All with a strong serving of pony
-
04-16-2005, 10:28 PM #16Problem Solver
- Join Date
- Mar 2003
- Location
- California USA
- Posts
- 13,681
I read this article a few weeks back and i found it very interesting:
http://www.samag.com/documents/s=114...108q/0108q.htm
They recommend enabling asynchronous file system, its not as reliable as the stock synchronous mode. But it may help in your situation. Theres some tweaks at the end of the article, i have not had a chance to test out.
"The total run time for the hard drive test for each OS was: Linux: 542 seconds, Windows 2000: 613 seconds, FreeBSD tuned: 630 seconds, FreeBSD untuned: 2398 seconds, and Solaris: 3990 seconds."Steven Ciaburri | Industry's Best Server Management - Rack911.com
Software Auditing - 400+ Vulnerabilities Found - Quote @ https://www.RACK911Labs.com
Fully Managed Dedicated Servers (Las Vegas, New York City, & Amsterdam) (AS62710)
FreeBSD & Linux Server Management, Security Auditing, Server Optimization, PCI Compliance
-
04-16-2005, 10:29 PM #17Artificial Intelligence
- Join Date
- Feb 2005
- Location
- Minnesota
- Posts
- 973
Originally posted by DeltaAnime
Hrm, i guess i gotta talk to sago and see if i can work out a nice price on one.
If i had something like a 2.8GHz p4 with a gig or two of ram, would this suffice with my mysql loads?███ AfterNorth Innovative solutions for tomorrow, today.
███ 0spam.org AntiSpam for Service Providers ♪
███ DotNetInvoice Online Billing Solutions ♫
███ Professional Services Since 1996
-
04-16-2005, 10:46 PM #18Poooooonnyyy :*
- Join Date
- Jan 2003
- Location
- Canada
- Posts
- 5,073
Originally posted by Kiamori
with some fast drives and 1-2gb ram yes it should be good, or you could go amd =)
I'll have ot see if maybe they can RAID some drives or something then.
Thanks,
~FranciscoBuyVM - OpenVZ & KVM Based VPS Servers - Chat with us
- All popular VPN methods supported
- Affordable offloaded MySQL & DDoS protection
- 5GB backup space, unmetered private LAN bandwidth & native IPv6 included. All with a strong serving of pony