List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 11 2000 12:21pm
Subject:discussion about MySQL on linux-kernel list
View as plain text  

>>>>> "vdb" == vdb  <vdb@stripped> writes:

vdb> Hi!
vdb> I saw this article on linux-kernel mailing list. Can someone comment on this?

vdb> ------------------------------------------------------------
vdb> Thread: Re: Linux responsiveness under heavy load

>> |> If I remember properly, MySQL does not use mmaped files yet; if it would, 
>> |> would that make a difference? Currently it may just be busy copying 
>> |> buffers (I don't know how much of it is a userland issue vs. kernel 
>> |> space). 
>> | 
>> |When your I/O subsystem can do 100Mbytes/second sustained then maybe, but 
>> |with the PC of today - wildly improbable. 
>> I understand this; I mentioned earlier that in my case, MySQL isn't even 
>> swapping (300MB physical memory vs. 45MB db). 

vdb> Its worth mentioning that MySQL uses a _really_ dumb locking strategy 
vdb> which starves writers and causes db performance to suck really badly 
vdb> when you have multiple concurrent readers and writers. We used to see 
vdb> it here, and it brings the machine to its knees. To see if you're 
vdb> hitting this, do "procinfo -d" and see if you're getting very large 
vdb> numbers of context switches / sec. 

Any threaded application with many threads that have to use the same
files from multiple threads will have to do a lot of mutex locks.
Any mutex locks will increase that likehood of context switches;  This
has nothing to do with how MySQL locks tables;  Actually the way MySQL
uses table locks will enforce less context switches than using any
other way of locks as any threads that gets a lock are likely to run a
much longer time as it has to go through less mutexes!

In a sense it's a kernel problem that read/writes are slow as there
any kernel call will have to issue internal locks, which could cause
contex switches.  Using memmap will ensure that fewer system calls are
issued and thus make MySQL faster (but this is a totally separate issue).

In a sence you are right that because of table locks, MySQL is not
optimal if you are using a lot of concurrent selects / updates where
the select's or update's spans many rows.  (SELECT / INSERT isn't a
problem anymore with MySQL 3.23).  This has however nothing to do with
context switches as the current method will normally ensure that
fewer contexts switches are made.

'vdb' has also misunderstood another thing;  By default MySQL doesn't starve
writers, it starves readers.  This forks very nicely (and gives higher
performance) if the readers/writes are doing things fast.

As an example of this, is that we had in earlier MySQL versions
the following code to increment statistics variables:


The above code caused on FreeBSD a LOT of contex switches!  Just by
removing the lock (and make the statisics_variable not 100 % correct),
made MySQL 10 times faster under heavy load!

The lower granularity you have on your locks (or the more system calls
you have to do) the slower your program will go under heavy load as
the likehood increases that a program is in a mutex when a context
switch happens.

My guess would be that in the case 'vdb' mentions he is not using
MySQL 3.23 and/or he gets the problem that while having a long time
lock on a table he issues a lot of new connections to MySQL, which has
to be processed.  In this case it's the new connections and not the
lock model that is giving him problems.

vdb> If this is the case, its not a kernel issue, its a stupid app, and I 
vdb> can give you a fairly crude workaround if you mail me in private. 

'Stupid' in this context means, at least for me, that there exists some
trivial solution to the problem that we haven't understood. I would
suggest that by calling MySQL a stupid app, you haven't grasped the
problem in detail.  I am of course happy if you can post code that
fixes this without causing problems for the applications for which
MySQL is already highly optimized.


PS: We are just now adding a new table handler that will have a
    totally different update/select/insert behaveour and should help
    fix applications as 'vdb'.  The code for this is of course not
discussion about MySQL on linux-kernel listvdb10 Mar
  • Re: discussion about MySQL on linux-kernel listTonu Samuel11 Mar
    • Re: discussion about MySQL on linux-kernel listVivek Khera13 Mar
  • discussion about MySQL on linux-kernel listMichael Widenius11 Mar