The query is running dramatically slower than the MyISAM query, sometimes even causing mysql to freeze for a while. I searched this list and found a few people saying that on FreeBSD mysql should be compiled using linux pthreads if you are using InnoDB or else I would get this exact problem. Has this been resolved or is should I recompile? I am using native freebsd threads.
----- Original Message ----- From: "Sasha Pachev" <[EMAIL PROTECTED]> To: "Cliff" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, March 08, 2004 9:18 AM Subject: Re: InnoDB tables using 90% cpu > Cliff wrote: > > Hi, I have a whole database I wanted to convert to InnoDB from MyISAM, but > > do not want to use alter table because of the problems I had last time. I > > made a whole dump of the table using mysqldump and changed all of the table > > create definitions from MyISAM to InnoDB. Theoretically this should be just > > like creating a new innodb table from scratch and inserting new records. > > However, while the MyISAM tables used ~30% of the cpu usage on a query, > > InnoDB runs anywhere from 50-90% depending on the query. The databases > > combined are approximately 200MB. Here is my cnf file: > > > > [mysqld] > > basedir=/mysql > > long_query_time=3 > > log-slow-queries=/tmp/slowmysql.log > > innodb_data_home_dir = > > innodb_data_file_path = /mysql/data/innodb_data:300M:autoextend > > set-variable = innodb_buffer_pool_size=300M > > set-variable = innodb_additional_mem_pool_size=20M > > set-variable = innodb_log_file_size=150M > > set-variable = innodb_log_buffer_size=8M > > innodb_flush_log_at_trx_commit=0 > > > > This is mysql 4.0.18 on freebsd 4.8-STABLE. We have 1GB of ram which should > > be plenty to run the large queries that we are doing. Thanks in advance. > > 50-90% CPU vs only 30% could be actually an improvement ( less disk I/O, and > relatively more time to get the data). The question is - does the query actually > take less time? If not, it could be because a certain optimization available > with MyISAM is not available with InnoDB. Isolate the trouble query, and do an > EXPLAIN. > > -- > Sasha Pachev > Create online surveys at http://www.surveyz.com/ > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
