Results 1 to 18 of 18
  1. #1
    Join Date
    Jan 2003
    Location
    Canada
    Posts
    4,845

    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!

    ~Francisco
    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

  2. #2
    Join Date
    May 2004
    Location
    Toronto, Canada
    Posts
    5,084
    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.
    André Allen | E: aallen(a)linovus.ca
    Linovus Holdings Inc
    Shared Hosting, Reseller Hosting, VPS, Dedicated Servers & Public Cloud | USA, Canada & UK - 24x7x365 Support

  3. #3
    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!

  4. #4
    Join Date
    Feb 2002
    Location
    New York, NY
    Posts
    4,612
    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/ ***

  5. #5
    Join Date
    Jan 2003
    Location
    Canada
    Posts
    4,845
    Oh, i completely forgot specs, heh.

    It's a dual xeon 3Ghz w/ 2GB of ram and 2 120GB sata's

    ~Francisco
    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

  6. #6
    Join Date
    Feb 2005
    Location
    Rochester, MN
    Posts
    916
    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.
    Area51.mn VPS, Dedicated & Colocated Servers.
    Area51 Computers Custom Servers & Gaming Systems. (Since 1998)
    NetAffect Email & Web Hosting Services. (Since 1996)
    Quality Systems & Service Since 1996

  7. #7
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,290
    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

  8. #8
    Join Date
    Feb 2002
    Location
    New York, NY
    Posts
    4,612
    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.
    Or a 15K Seagate Cheetah at 36GB, for about $160... Put several in RAID 10 for extra fun.
    Scott Burns, President
    BQ Internet Corporation
    Remote Rsync and FTP backup solutions
    *** http://www.bqbackup.com/ ***

  9. #9
    Join Date
    Jan 2003
    Location
    Canada
    Posts
    4,845
    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.
    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.

    ~Francisco
    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

  10. #10
    Join Date
    Feb 2002
    Location
    New York, NY
    Posts
    4,612
    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.

    ~Francisco
    Your configuration is probably not the problem. Perhaps co-location is your next move up if Sago can't give you SCSI drives?
    Scott Burns, President
    BQ Internet Corporation
    Remote Rsync and FTP backup solutions
    *** http://www.bqbackup.com/ ***

  11. #11
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,290
    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

  12. #12
    Join Date
    Feb 2005
    Location
    Rochester, MN
    Posts
    916
    Originally posted by bqinternet
    Or a 15K Seagate Cheetah at 36GB, for about $160... Put several in RAID 10 for extra fun.
    I'd rather use a quantum bigfoot or a floppy disc, I've had horrible luck with that brand of drives. Replacing a drive 3 times in one year costs more then a nice solid drive.
    Area51.mn VPS, Dedicated & Colocated Servers.
    Area51 Computers Custom Servers & Gaming Systems. (Since 1998)
    NetAffect Email & Web Hosting Services. (Since 1996)
    Quality Systems & Service Since 1996

  13. #13
    Join Date
    Jan 2003
    Location
    Canada
    Posts
    4,845
    Originally posted by thelinuxguy
    Is this a dedicated mysql server? Is apache also serving pages? What os? What kernel?
    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
    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

  14. #14
    Join Date
    Feb 2005
    Location
    Rochester, MN
    Posts
    916
    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
    seperate your sql from the rest that will help the most. I had the impression this was ded sql server.
    Area51.mn VPS, Dedicated & Colocated Servers.
    Area51 Computers Custom Servers & Gaming Systems. (Since 1998)
    NetAffect Email & Web Hosting Services. (Since 1996)
    Quality Systems & Service Since 1996

  15. #15
    Join Date
    Jan 2003
    Location
    Canada
    Posts
    4,845
    Originally posted by Kiamori
    seperate your sql from the rest that will help the most. I had the impression this was ded sql server.
    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?
    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

  16. #16
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,290
    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

  17. #17
    Join Date
    Feb 2005
    Location
    Rochester, MN
    Posts
    916
    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?
    with some fast drives and 1-2gb ram yes it should be good, or you could go amd =)
    Area51.mn VPS, Dedicated & Colocated Servers.
    Area51 Computers Custom Servers & Gaming Systems. (Since 1998)
    NetAffect Email & Web Hosting Services. (Since 1996)
    Quality Systems & Service Since 1996

  18. #18
    Join Date
    Jan 2003
    Location
    Canada
    Posts
    4,845
    Originally posted by Kiamori
    with some fast drives and 1-2gb ram yes it should be good, or you could go amd =)
    Sago's a intel farm

    I'll have ot see if maybe they can RAID some drives or something then.


    Thanks,

    ~Francisco
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •