Re: Switching to InnoDB turns out dissapointing

[Brent Baisley]

Coming in late on this thread. The testing on your laptop, are you just 
running the one query or are you somehow emulating the typical load you 
are trying to design for? As you said, you are trying to improve 
concurrency, so you'll need to compare MyISAM and InnoDB setups under 
load (i.e. the weekly run+typical activity).

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/mysql?unsub=1
>
>
>
-- 
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577