>>>>> "Rick" == Rick Moore <rick@stripped> writes:
>> LOCKs can ge good in your case. You should experiment with them. Having
>> LOCK and 10 inserts is much faster than just having 10 inserts. Also
>> parsing is CPU intensive task. Can you optimize your application to put
>> many inserts into one line: INSERT INTO xxx VALUES (1,2,3), (4,5,6),
Rick> Yep-- I used locks at one point. They work well to optimize a single
Rick> thread's access, but the benefit doesn't outweigh the loss of having 14
Rick> other threads completely idle.
Note that you don't have to do a LOCK to do a multiple row insert to
Rick> I can't do multiple INSERTs because I either 1) need to know whether each
Rick> individual insert was successful or 2) need the insert_id value.
>> > - KEY_BUFFERS is the only option set. I set it to all available RAM.
>> This can be bad because memory gets fragmented. Usually symptoms are
>> like this: System is fast after start-up but will be slower and slower
>> and Slooooowwwer until next restart. Try to reduce it.
Rick> Really? That's enlightening. Do you have a size recomendation?
Put this to half of your available memory; Your OS should use the
rest of the memory for caching of row data..
Have you checked with 'mysqladmin extended-status' how good your key
cache utilization is? The above command should also give you some
indication of what is really your bottleneck!
If you have lots of memory, you should remove your swap to ensure that
you don't accidently start swapping!
>> > Analysis:
>> > - CPU rarely goes above 50%.
>> > - RAM is at 256M. MySQL uses every available byte, but it probably
>> > offering much value for such a large database with random accesses.
>> > - Disk activity is very high. The lights appear solid for lengthy
>> > of time.
>> Do you have IDE or SCSI hd-s? There are many tricks what can be done
>> with IDE. I do not know much about SCSI and FreeBSD. Under linux I speed
>> up disks by using "hdparm -u1W1c1m16k1K1 /dev/hda". Check out these
>> parameters on your system. On SCSI they porably doesn't help but you can
>> try them.
Rick> IDE, but thinking of switching to SCSI. I've seen threads on this mail list
Rick> discussing hdparm under Linux. Unfortunately, it doesn't seem to exist on
Rick> FreeBSD or I don't know the equivalent... Perhaps some FreeBSD guru can
Rick> enlighten me...
SCSI would probably help, but it sounds like you are in more need of
RAM than faster disks. How much RAM do you have?
>> > - Interrupt activity is very high. About 60% of CPU activity is
>> > interrupt requests. Almost all the interrupts are for disk activity.
>> At least on IDE this means that processor can't do anything until you
>> issue -u1 with hdparm.
Rick> Ouch. Hope it isn't that way on FreeBSD.
FreeBSD has also some problems with mutexs handling and will easily do
a full context switch if two threads hits the same mutex at the same
time (which gets ever more likely as you get more threads). One way
to fix this would be to introduce 'fast' mutex that lets a thread
steal a mutex if no-one has the mutex, even if someone is waiting for
it. This could give you a boost of 10-30 % on FreeBSD, as FreeBSD has
very slow context switch handling!
>> > I'm not completely out of optimization / tuning ideas, but these won't
>> > me as far as I need to go:
>> > Any comments would be appreciated. It really seems like it's time to
>> > upgrade the hardware. If I double the speed of the hard disks, increase
>> > RAM and CPU speed, do you think it'll get me from 10% full to 100% full?
>> > do you think I'll run into trouble again along the way?
I would start by just upgrading the RAM and see if this helps.
(If course the new RAMBUS memory one can get to new memory boards are
MUCH faster than old SDRAM (up to 4 times faster).
>> Depends, what is important to you and for what you can spend money.
>> Optimization is also taking time and money. Sometime it's cheaper to buy
>> some RAID box. In my life I try to buy not too expensive things. I
>> prefer IDE disks with software RAID on them if money counts. If not,
>> then it's time to look onto DPT or something similar.
I don't want to start a WAR here, but it would be really interesting
to know how Linux would work in your situation; FreeBSD may be
faster in handling disks, but Linux should be better to context
switch. I don't really know which thing is more important for you :(
(Linux is much better than FreeBSD on handling multiple CPU:s, but at
least for the moment the CPU doesn't seem to be your bottleneck.)
Another issue, have you taken a look at the HEAP tables in 3.23 ? Can
you have any use of these?
Rick> I understand. Thanks again Tonu, I really appreciate your help!