Web Hosting Talk : Web Hosting Main Forums : Dedicated Server : Optimizing MySQL for heavy insert/update statements?

[DeltaAnime]

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

[Coolraul]

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.

[hiryuu]

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.

[bqinternet]

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.

[DeltaAnime]

Oh, i completely forgot specs, heh.

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

~Francisco

[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.

[Steven]

Is the mysql on its own drive?

[bqinternet]

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.

[DeltaAnime]

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

[bqinternet]

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?

[Steven]

Is this a dedicated mysql server? Is apache also serving pages? What os? What kernel?

[Kiamori]

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.

[DeltaAnime]

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

[Kiamori]

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.

[DeltaAnime]

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?