List:General Discussion« Previous MessageNext Message »
From:John Kemp Date:January 17 2002 3:15pm
Subject:Re: Memory
View as plain text  
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
Streetmail Inc.

Heikki Tuuri wrote:

> John,
> 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
> problems.
> 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
> See 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 site (bugs & fixes):
>>>This has me worried, but I haven't seen this behaviour on our site. We
> have
>>>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:
>   (the manual)
>           (the list archive)
> To request this thread, e-mail <mysql-thread96577@stripped>
> To unsubscribe, e-mail
> <>
> Trouble unsubscribing? Try:
> .

MemoryJohn Kemp17 Jan
  • Re: MemoryJeremy Zawodny17 Jan
Re: MemoryHeikki Tuuri17 Jan
  • Re: MemoryJohn Kemp17 Jan
  • Re: MemoryJeremy Zawodny17 Jan
    • Re: MemoryDan Nelson18 Jan
      • Re: MemoryJeremy Zawodny18 Jan
Re: Memoryalec.cawley17 Jan
Re: MemoryHeikki Tuuri17 Jan