Hi! I run MySQL 3.23.49 with InnoDB tables on a AlphaServer 4100 with 4 x 600 MHz EV5.6 CPUs with 8 MB cache. The machine has 4 GB memory.
There are about 8 tables. The tables are small in size (only 1-5 GB each) but has alot of rows (usually > 4 million each). There are only a few concurrent select users. Inserts are done by batch from about 70 systems every 5 minute. The systems are spread out during those 5 minute so the inserst come in a constant flow of about 10 per second in average. bin-log is turned off as the machine is unable to keep up with the insert rate with logging turned on. I have a couple of questions related to the cache settings in my.cnf. I need to tune it to give as good a select performance as possible, and also ensure that insert is as fast as possible. The machine ins't doing much else, so I can use close to all memory. Except for the default MySQL table with users etc., I only have InnoDB tables. In the my-huge.cnf example there are some of the settings : set-variable = key_buffer=384M set-variable = max_allowed_packet=1M set-variable = table_cache=512 set-variable = sort_buffer=2M set-variable = record_buffer=2M set-variable = thread_cache=8 set-variable = thread_concurrency=8 set-variable = myisam_sort_buffer_size=64M How many of these needs to be big in relation to a InnoDB only setup ? ...and can I reduce some of them without problems ? I assume the key_buffer isn't used at all since InnoDB has its own, right ? ...and then the InnoDB specific. They are equal (and very low) on all example files : set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 What would be a good startup for these given my current hardware & database ? The database layout and a online interface is available at http://statdb.dassic.com/ for those who want to see the table & index design etc. etc. It's not the mentioned machine, but it runs the same database & solution. Recommendations related to the solution are welcome...it's OpenSource. /Jesper --------------------------------------------------------------------- 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
