Memory optimization (my.cnf options). Please shed some light!

[Maximo Migliari]

Hello there,

I have moved from a Dual PIII--866 Server with linux redhat and 384MB of RAM, to a dual Athlon 1.5Ghz with FreeBSD 4.7-Stable installed and 1 GB of RAM.

Something I noticed, with the help of a few of you on the list, is that the configuration options I had done in my.cnf, specifically those dealing with memory, behaved very differently from one machine instalation to the other.

When importing a medium-sized database (150 MB) insto the the more powerfull FreeBSD machine, with the same my.cnf memory settings as the old Linux machine, the server crashed complaining about memory usage. Indeed, the error file gave warnings that the server crashed due to lack of memory - that mysql was trying to use 8 GB when I have only 1 GB. However, the SAME my.cnf on linux would not give me these errors. Its as if Linux had some kind of feature that would not allow a program to use more memory than the OS can allocate to it. FreeBSD "trusted" my configuration and the server crashed. I have reduced my memory settings in my.cnf and everything is working fine now.

Anyway, the point of this whole e-mail is to ask a few things about memory settings under my.cnf. The manual at mysql.com is very detailed, however, there are certain things that are vague to me now, especially since InnoDB was introduced.

Since InnoDB is a separate table manager, it has its own memory settings - but there are some things which to me are extremely ambiguous. For example, when you set the key_buffer to a specific value, does that also count for InnoDB, or are ALL memory settings for InnoDB separate?
Do the table_cache, record_buffer and sort_buffer also affect InnoDB??

Its very hard "tuning" mysql with so many variables and so little specific information in the documentation - especially when you are using BOTH MyISAM and InnoDB.

For example, what is better, to have a huge key_buffer and fewer maximum connections, or to have higher maximum connections and less memory for the key_buffer?

My website, during its peak produces arround 50-60 queries a second. Since my database is around 170 MB, and I have 1 GB of RAM, I would like to have it all in memory to speed things up. If need be, I would convert all my tables to InnoDB and have less memory allocated to MyISAM-specific things. There is very little documentation that explains this.

Perhaps Paul Dubois, who is versed in the documentation could shed some light into this matter? Perhaps some of you guys could write what you understand and know, what works for you with regards to memory optimization?

Thanks in advance for your help!
Maximo Migliari.
www.cheathouse.com

---------------------------------------------------------------------
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