Hi. On Thu 2002-09-05 at 09:09:07 -0500, [EMAIL PROTECTED] wrote:
> For clarity sake, assume the following: > > Red Hat Linux 7.1 > 2.4.8 kernel > MySQL 3.23.42 > MyISAM databases > 3GB RAM > P3/700 x 4 > 15GB database spanned across ~200 tables > > Key_reads / Key_read_request = 0.00059875 > Key_write / Key_write_requests = 0.81324723 > > > 1) Since the key_buffer variable defines the total amount of indexed > data in memory, then the key_buffer size and index size of my tables > should be releated. Total index size of all relevant tables is 440MB. > Should the key_buffer size be >, <, = or a percentage of this number? Usually it makes no sense to make key_buffer greater than the index size, except if you have relatively fast-growing tables. There is no percentage to be recommended, because the ideal index size depends on your usage pattern. For example, for one of my databases (14GB data), the index files are 3GB. With a key_buffer of 500MB, the key efficiency is 97.48% (i.e. Key_reads / Key_read_request = 0.0252), because old rows are seldom read. Your current setting has a really suffiently large key_buffer (efficiency 99.94%). OTOH, if this is a MySQL-only machine, 3GB are plenty and 100MB more or less used do not really matter (regarding free memory), so I would simply set it to use about 400MB are forget about it. If memory is would be tight, I would usually set it so some guessed value (e.g. half of the index size, here: 200MB), and look what the efficiency is afterwards. Repeat, until you have found an effiency / memory usage ratio you feel comfortable with. > 2) How does performance directly relate to the number of open tables? > Yes, these open tables take memory, and a FLUSH TABLES would adequately > free this memory, The memory blocked by open tables is only marginal, I think. With 200 tables, I see no reason to not set the table cache so that it allows all tables to be opened. > but then the tables that are used most frequently would need to be > reopened. No. The tables that are used less frequently would need to be reopened. MySQL would try hard to not close the most frequently opened tables. Therefore it is the same as with key_buffer above. If you want to trim this down, play around until you find a setting that has a reasonable rate of (re-)opening tables. > Am I looking at memory vs. CPU in this case? Provided I have enough > RAM, wouldn't it make sense to leave all the tables open? I don't think, that the main issue you deal with is memory. I may be wrong here, but I think the most restricting limit is the OS here, i.e. how many files may be open (up to 3 per table, IIRC) at one time and how your OS can cope with a lot of open files resp. opening lot of files. > I'm sure I'll think of more later on. By the way, here are the pages I > used to ramp up my performance knowledge: > http://www.mysql.com/doc/en/SHOW_VARIABLES.html > http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:19873 > http://www.linux-mag.com/2001-12/mysql_01.html Regards, Benjamin. -- [EMAIL PROTECTED] --------------------------------------------------------------------- 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
