Heikki & Jeremy,
Thanks again for your help. I'll try it out and see how things go. I
will just note that in most of the MySQL documentation talks about how
one can set key_buffer or innodb_buffer_pool up to 75% of your maximum
memory, which in the case of our db machines is 4GB. I have been
monitoring our memory usage under load, and even with the numbers I
listed below, I have not yet seen a server crash from 200 simultaneous
connections, which has been our peak so far. We did see this a while
back on an earlier version (we're now running 3.23.45-max) but our
server has been running like this for 45 days and counting.
So I guess this is more of a case (as Jeremy seemed to be suggesting)
that this *might* happen if we have a lot of simultaneous, tricky
queries (when we'd see lots of sort_buffer and record_buffer get
allocated for each connection/thread)
This also implies that if you're mostly using Innodb tables for querying
that you probably wouldn't see this happening, because:
1) Innodb doesn't lock the whole table - so simultaneous queries on the
same table will get done faster, and thus memory will be freed up quicker.
2) Does querying or updating the Innodb tablespace effect record_buffer
and sort_buffer anyway?
The more I think about it, the more I realize that since we started
doing work with Innodb tables, our database memory problems have
basically gone away.
Any thoughts on that?
Director, Software Development
Heikki Tuuri wrote:
> I suggest setting record_buffer to 1 MB. Disk reads in blocks of 1 MB are
> probably as fast as in blocks of 10 MB.
> Also set sort_buffer to 1 MB, and only increase it if there are performance
> The maximum process space of Linux x86 is 2 GB, and better play safe.
> Jeremy, I think some Intel x86 processors support segmented memory above > 4
> GB. Is that supported in Linux?
> Jeremy, also thanks for your article in the latest Linux Magazine. I too
> learned something about tuning MySQL :).
> Best regards,
> Heikki Tuuri
> Innobase Oy
> Order technical MySQL/InnoDB support at https://order.mysql.com/
> See http://www.innodb.com for the online manual and latest news on InnoDB
> Jeremy Zawodny wrote in message ...
>>On Wed, Jan 16, 2002 at 09:46:48PM -0500, John Kemp wrote:
>>> From the innodb.com site (bugs & fixes):
>>>This has me worried, but I haven't seen this behaviour on our site. We
>>>innodb_buffer_pool = 1100Mb
>>>key_buffer = 400 Mb
>>>record_buffer = 10Mb
>>>sort_buffer = 20Mb
>>>max_connections = 220
>>>which according to this formula gives me 1100 + 400 + (220 * (20 + 10))
>>>+ ( 220 * 1) = 8320Mb at max capacity. At roughly half capacity (96
>>>connections) we're using only 1390Mb, so I'm finding it hard to believe
>>>it's going to scale that badly right now. We "only" have 4Gb memory on
>>>our linux-based database machine right now - should I be upgrading? ;-)
>>>Does anyone have any information that either supports or refutes the
>>>statement above? I'd be interested if you did....
>>Well, the sort_buffer and record_buffer will only be allocated on an
>>as-needed basis. And they'll exist for very short periods of time,
>>So your 1390 comes mainly from innodb_buffer_pool + key_buffer which
>>are the two "global" buffers (non-thread-specific) that are involved.
>>You'll likely see a single MySQL thread peek above that on occasion,
>>but you'd need things to get pretty bad before you eat up all your
>>for a bit of info on the difference between global and per-thread
>>memory in MySQL.
>>Jeremy D. Zawodny, <jzawodn@stripped>
>>Technical Yahoo - Yahoo Finance
>>Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
>>MySQL 3.23.41-max: up 14 days, processed 336,064,611 queries (270/sec. avg)
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
> To request this thread, e-mail <mysql-thread96577@stripped>
> To unsubscribe, e-mail
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php