Hello, We're working with a MySQL 5.0.18 on a Linux System (Kernel 2.6.5-7.97-smp). The Server got 2G of RAM and enough HDD-Space. We use several Tables on 2 Databases. Most of them were MyISAM-Tables and we encountered some problems: By 96% of the time we got reaction times in the range of 150 ms but some exceptions we got reaction times more than 400ms. By benchmarking the system we decided to change the important tables to InnoDB. Now 99% got reaction times of 4 ms but the rest got reaction times more than 600 ms.
we got mainly 3 tables in one system: 1 Input (one Reading Process) (5-10 % of Exceptions) <---- Still MyISAM 2 Output (1 writers 1 updaters and 1-2 reading Processes) (90-95% of Exceptions) <--- Those were changed to InnoDB The my.cnf was changed to the following Values: -------------------------START------------------------- connect_timeout=259200 #wait_timeout=10 key_buffer = 32M max_allowed_packet = 2M table_cache = 128 sort_buffer_size = 1M net_buffer_length = 16k read_buffer_size = 512K read_rnd_buffer_size = 1M myisam_sort_buffer_size = 16M # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /somewhere/mysql/innodbs/ innodb_data_file_path = ibdata1:50M:autoextend innodb_log_group_home_dir = /somewhere/mysql/logs/ innodb_log_arch_dir = /somewhere/mysql/logs/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 128M # ORIGINAL VALUE ! innodb_buffer_pool_size = 256M #innodb_additional_mem_pool_size = 48M # ORIGINAL VALUE ! innodb_additional_mem_pool_size = 64M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 30M # ORIGINAL VALUE ! innodb_log_file_size = 128M #innodb_log_buffer_size = 30M # ORIGINAL VALUE ! innodb_log_buffer_size = 60M #innodb_flush_log_at_trx_commit = 1 # ORIGINAL VALUE ! innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50 # NEW innodb_flush_method=O_DSYNC --------------- END -------------------------------- By running the benchmarks we only had exceptions of 4 ms, but in productive settings we have those exceptions of > 600ms ! Shall I increase the InnoDB-Values to reduce the exceptions ? Thanks in Advance Frank
