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, <[EMAIL PROTECTED]> >>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 <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > . > > --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
