Matthias, if you can tolerate losing a few last transactions in a power outage or an OS crash, you can set
innodb_flush_log_at_trx_commit=2 Have you shut down mysqld and restarted it after populating the tables? MySQL only updates index cardinality statistics when you run ANALYZE TABLE or restart the mysqld server. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ ----- Original Message ----- From: <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Wednesday, October 01, 2003 6:04 AM Subject: InnoDB speed problems > > Hi all, > > Because I want to use transactions in the future I have converted all > tables of a copy of our production database server (1800+, 512 MB RAM, > Linux) to InnoDB format. No problem until now. First, let me show you > settings in my.cnf: > > key_buffer = 16M > table_cache = 128 > sort_buffer_size = 1M > read_buffer_size = 1M > myisam_sort_buffer_size = 64M > thread_cache = 8 > thread_concurrency = 8 > > innodb_buffer_pool_size = 256M > innodb_additional_mem_pool_size = 20 > innodb_log_file_size = 64M > innodb_log_buffer_size = 8M > innodb_flush_log_at_trx_commit = 1 > innodb_lock_wait_timeout = 50 > > Question: Is sort_buffer_size and read_buffer_size relevant to InnoDB? > > All these settings seem to be fine for me. With MyISAM I have used a > key_buffer of 256M and sort_buffer_size of 4M which procuded very fast > database accesses. mytop's output: > > MySQL on localhost (4.0.15-standard-log) up 0+23:14:39 [04:23:24] > Queries: 5.7M qps: 72 Slow: 34.0 Se/In/Up/De(%): 63/10/15/05 > Cache Hits: 1005.2k Hits/s: 12.3 Ratio: 27.3% > Key Efficiency: 100.0% Bps in/out: 8.0k/33.8k > > But now everything is slow, I don't know why. Even without load each > query takes a bit longer. Shouldn't it be vice versa? Then I did some > load testing: CPU usage and system load raised by 100 percent. That's > not normal for me, does InnoDB need more power, more momory? While > testing MySQL was able to handle all the queries but, well, not as > fast as I would like to have it in productive environment. > > I have also noticed that some more complex queries (select with 4 > joins and 2 orders) last much too long. With MyISAM everything was < > 0.5s but now I sometimes have a strange one that is listed for several > houndred seconds (?!) in the process list. That's not normal, isn't > it? Something strange is going on here and I do not have a clue what I > could be. Playing around with the settings and raising InnoDB's pool > size to 80% of memory didn't change anything. > > So, I'm not familiar with this great InnoDB thing, maybe you have some > ideas. :) > > Thanks in advance! > Matthias > > > > > > > > > > > -- > 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]
