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?

John
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 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
>>>
> 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,
>>ideally.
>>
>>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
>>memory.
>>
>>See
>>
>> http://jeremy.zawodny.com/mysql/mysql2.pdf
>>
>>for a bit of info on the difference between global and per-thread
>>memory in MySQL.
>>
>>Jeremy
>>--
>>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
> <mysql-unsubscribe-jkemp=streetmail.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> .
> 
> 


Thread
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