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

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to