If the concurrency you are trying to improve is caused by your weekly runs, I would try doing replication. Your summarization queries would run against the replicated machine and create a text file for batch import/update into the table you need to update. That way you remove the load from the main machine caused by the summary queries.
On Mar 2, 2005, at 11:23 AM, Alfredo Cole wrote:
El Mar 01 Mar 2005 18:29, Heikki Tuuri escribió:Alfredo,
I have changed my my.cnf to try and include the suggestions from the list, as
much as possible and try to run my program again. It now reads like this:
innodb_data_file_path = ibdata1:2G;ibdata2:2G:autoextend set-variable = innodb_buffer_pool_size=256M set-variable = innodb_additional_mem_pool_size=32M set-variable = innodb_log_file_size=64M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 set-variable = innodb_lock_wait_timeout=50
The MyISAM database is 1Gb in size, and the resulting InnoDB table space is 4
Gb.
In my laptop (I have to test here before I decide to implement in the server),
the time has dropped from 4 hours to 70 minutes. With MyISAM tables, it takes
12 minutes. Although the reduction in time is substantial, I still think it
should be better. InnoDB is supposed to provide transactions and row level
locking, which I need to improve concurrency, "without compromising the speed
of MySQL". We have other processes that need to be run on a weekly basis that
do a lot (45,000 records need to be examined) of select sum() for a one year
period, from a table with 1 million+ rows and then updates a table with
800,000 rows, and so far it takes around 12 hours, even after enclosing
between BEGIN and COMMIT statements. Could be entirely our fault. Have to
check that too. Will try with this new setup and see how it goes.
Thank you all.
-- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
