No problem: Firstly, how are you measuring your updates on a single table? I took a few binary logs, grepped out for things that changed the table, counting the lines (using wc) and then dividing by the # of seconds the binary logs covered. The average for one table was 108 updates per second.
I'm very intrigued as to how you came up with 2-300 updates per second for one table. . . did you do it that way? If not, how did you do it? (We are a VERY heavily trafficked site, having 18,000 people online and active, and that accounts for the 108 updates per second. So if you have more traffic than that. . .wow!) my.cnf: [mysqld] old-passwords tmpdir = /tmp/ datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock port = 3306 key_buffer = 320M max_allowed_packet = 16M table_cache = 1024 thread_cache = 80 ft_min_word_len = 3 # Log queries taking longer than "long_query_time" seconds long_query_time = 4 log-slow-queries = /var/lib/mysql/slow-queries.log log-error = /var/lib/mysql/mysqld.err # Try number of CPU's*2 for thread_concurrency thread_concurrency = 12 interactive_timeout = 28800 wait_timeout = 30 max_connections = 2200 max_connect_errors = 128 # Replication Master Server (default) # binary logging is required for replication log-bin server-id = 15 binlog-do-db = manhunt binlog-do-db = phpAdsNew binlog-do-db = mobile max_binlog_size = 2G # InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:3G;ibdata2:3G; innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_files_in_group = 2 innodb_log_arch_dir = /var/lib/mysql/ innodb_buffer_pool_size = 5G innodb_additional_mem_pool_size = 40M innodb_log_file_size = 160M innodb_log_buffer_size = 80M innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50 innodb_thread_concurrency = 8 innodb_file_io_threads = 4 # Query Cache Settings query_cache_size = 32M query_cache_type = 2 -------------------------------------------------------------------------- table info for the table in question: Name: Sessions Engine: InnoDB Version: 9 Row_format: Dynamic Rows: 10600 Avg_row_length: 792 Data_length: 8404992 Max_data_length: NULL Index_length: 24297472 Data_free: 0 Auto_increment: NULL Create_time: 2005-12-01 15:04:52 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: InnoDB free: 317440 kB -------------------------------------------------------------------------- We're running MySQL Version 4.1.12 on Fedora Core 3 x86_64. ------------------------------------------------------------------------- The hardware is a Dell 2850 with 4 Intel(R) Xeon(TM) CPU 3.40GHz processors, 1024 KB cache size. Total RAM: 8199448 kB (8G) ------------------------------------------------------------------------- We're quite beefy because of the amount of queries per second (3000) and updates. We are not aware of locking issues. Our customers are quite vocal (there are 8 folks in IT, and over 20 in Customer service!) so when things are slow we know about it. Sincerely, Sheeri Kritzer On 1/27/06, Jan Kirchhoff <[EMAIL PROTECTED]> wrote: > that sounds interesting since we couldn't get good performance using > innodb in our case - but thats a few years ago. things may have changed? > I'll definitely give it a try next week, too. > Could you give me more information on your system? hardware, size of the > table, average number of updates/sec? > > thanks for your suggestions > Jan > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]