MySQL Forums
Forum List  »  Data Warehouse

Re: datawarehouse ETL rolls back after 5 GB of data transfer
Posted by: Rick James
Date: May 20, 2010 11:05PM

Re-installation is not required to change parameters. Instead change my.cnf and restart the server. (Some thing can be set on the fly, but not all.)

InnoDB? How much RAM? Change innodb_buffer_pool_size to be 70% of available RAM.

SHOW CREATE TABLE -- need to see what fields and indexes you have.

It is a mistake to build a data warehouse "Fact" table with lots of indexes. You have already found out one big reason -- INSERTs get slower and slower. That is because each index has to be updated. An index on a random-valued column will have to read-modify-write a random part of the index, often requiring disk I/Os.

So, how to you avoid having lots of indexes? Summary tables. What do your "report" SELECTs look like?

Do you have RAID?

SHOW VARIABLES LIKE 'innodb%';
There may be a few others that could be tweaked.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: datawarehouse ETL rolls back after 5 GB of data transfer
3918
May 20, 2010 11:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.