Hi Cliff, Either way for a production system I recommend using Linuxthreads with FreeBSD4 (also works on 5 but threads are much improved on 5). Please use the ports and make WITH_LINUXTHREADS=yes and others that use (see make pre-fetch in /usr/ports/databases/mysql-favorite-version) or http://jeremy.zawodny.com/blog/archives/000458.html for Jeremey's building hints. I would also examine your innodb configuration and buffer sizes and isolation level. These are separate from myisam.
Best of luck, Ken ----- Original Message ----- From: "Cliff" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, March 09, 2004 4:56 PM Subject: Re: InnoDB tables using 90% cpu > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
