Performance of an innodb compared to memory-table - part2

[kirchy]

Hi,

I sent a mail to this list almost a year ago asking if our current way of keeping some of
our tables memory could be improved by using the cluster-engine since we were getting
problems with too long locks.
A few people suggested I should use innodb and said it should be able to handle the load. 
Back then, I ran out of time and we desperatly needed the servers for production, so I
stayed with memory-tables.

Now I have 4 new servers (Dual-Dualcore-Opteron, 8gig ram, raid10 on 4 disks for the
database and raid1 on 2 disks for the replication logs and innodb-logfiles) and a week to
play around with them.

A brief summary of our databases: total of almost 200gb, maybe 400-500 tables, most of
them small (< 1 million rows), 4 really big ones with historical data (200-400 million
rows, but only an Avg_row_length of 100-180) and 6 critical tables that are currently
MEMORY tables. These 6 tables are only around  700.000-1 million rows each
(Avg_row_length 500-600), but at least 95% of our updates/inserts go in one of these
tables with "INSERT INTO... VALUES (),(),() ON DUPLICATE KEY UPDATE ...  ". Depeding on
the time of the day, this can be up to 20 of these mass-inserts per table per second
changing more than 200.000 rows. We write more than 30GB of binlogs every day.

This is where the problem is: The mass of binlogs and catching up with replication-slaves.
The new servers are slaves of our production systems right now. I changed all tables to
innodb on one of the the new servers and it is much faster now when I run a bunch of big
statistical queries on it (using the big tables as well as the heavy-updated ones) - it
only takes 1:30h compared to >3h, but when I stop the SQL slave-thread for 3 hours,
the innodb-only server needs twice as long to catch up (a little more than 2 hours
compared to 55 minutes with memory-tables). Updating tables is not fast enough...

my config:


innodb_buffer_pool_size=4500M
innodb_support_xa = 0

innodb_additional_mem_pool_size=16M
innodb_log_group_home_dir = /var/lib/mysql2/innodb/
innodb_log_arch_dir = /var/lib/mysql2/innodb/
innodb_log_buffer_size=32M
innodb_flush_log_at_trx_commit=0
innodb_lock_wait_timeout=120

innodb_log_file_size=512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_thread_concurrency = 16
innodb_file_per_table
innodb_data_home_dir =

innodb_data_file_path = /var/lib/mysql/ibdata:100M:autoextend
innodb_autoextend_increment = 100M

any suggestions? It would be great if there is a way to speed up updates a little and
everything would be perfect...

Jan
-- 
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! 
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer