List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 3 2000 9:28pm
Subject:Re: I need more speed...
View as plain text  
>>>>> "Rick" == Rick Moore <rick@stripped> writes:

<cut>

>> 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
gain speed..

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!

<cut>

>> > Analysis:
>> > - CPU rarely goes above 50%.
>> > - RAM is at 256M.  MySQL uses every available byte, but it probably
Rick> isn't
>> > offering much value for such a large database with random accesses.
>> > - Disk activity is very high.  The lights appear solid for lengthy
Rick> periods
>> > 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
Rick> handling
>> > 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
Rick> get
>> > 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
Rick> the
>> > RAM and CPU speed, do you think it'll get me from 10% full to 100% full?
Rick> Or
>> > 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!

Rick> Regards,
Rick> Rick

Regards,
Monty
Thread
I need more speed...Rick Moore3 Jan
  • Re: I need more speed...Tonu Samuel3 Jan
  • Re: I need more speed...Rick Moore3 Jan
  • Re: I need more speed...Tonu Samuel3 Jan
  • Re: I need more speed...Rick Moore3 Jan
    • Re: I need more speed...Dan Nelson3 Jan
    • Re: I need more speed...Benjamin Pflugmann3 Jan
    • Re: I need more speed...Michael Widenius4 Jan
      • RE: I need more speed...Steven Roussey5 Jan
        • Re: I need more speed...Benjamin Pflugmann5 Jan
          • RE: I need more speed...Steven Roussey5 Jan
            • Re: I need more speed...Benjamin Pflugmann5 Jan
          • RE: I need more speed...Steven Roussey5 Jan
            • Re: I need more speed...Benjamin Pflugmann5 Jan
            • RE: I need more speed...Michael Widenius5 Jan
              • RE: I need more speed...Steven Roussey6 Jan
                • OT: IP to Geographic areaCheng-Wei Cheng6 Jan
                  • RE: IP to Geographic areaJohn Zook6 Jan
  • Re: I need more speed...Sasha Pachev3 Jan
  • Re: I need more speed...Rick Moore3 Jan
  • Re: I need more speed...Tonu Samuel3 Jan
  • Re: I need more speed...Tonu Samuel3 Jan
    • LDAP package with mysql backendClickAgents4 Jan
      • Re: LDAP package with mysql backendRob McMillin4 Jan
      • LDAP package with mysql backendMichael Widenius4 Jan
        • Re: LDAP package with mysql backendChristopher E. Brown4 Jan
          • RE: LDAP package with mysql backendClickAgents4 Jan
            • RE: LDAP package with mysql backendChristopher E. Brown4 Jan
  • Re: I need more speed...Sasha Pachev4 Jan
    • Re: I need more speed...Michael Widenius4 Jan
      • how to..ClickAgents4 Jan
        • Re: how to..Benjamin Pflugmann4 Jan
  • Re: I need more speed...Rick Moore4 Jan
    • Re: I need more speed...Michael Widenius4 Jan
  • Re: As per your request; Re: I need more speed...Tonu Samuel5 Jan
  • Re: As per your request; Re: I need more speed...Rick Moore5 Jan
  • Re: As per your request; Re: I need more speed...Tonu Samuel5 Jan
    • FULL Text Indexjpabuyer5 Jan
      • Re: FULL Text IndexMark Zieg5 Jan
        • Re: FULL Text Indexjpabuyer5 Jan
      • RE: FULL Text IndexDan Ray5 Jan
        • RE: FULL Text Indexjpabuyer5 Jan
Re: I need more speed...Andy4 Jan
  • Re: I need more speed...Christopher E. Brown5 Jan
  • Re: I need more speed...Tonu Samuel5 Jan
Re: I need more speed...Andy4 Jan
  • Re: I need more speed...Michael Widenius4 Jan
    • Error: 2002 Can't connect to mysql using socket (111)Cheng-Wei Cheng5 Jan
      • Re: Error: 2002 Can't connect to mysql using socket (111)Sasha Pachev5 Jan