Hristo,
if you are doing INSERTs, UPDATEs, or DELETEs, try setting
innodb_flush_log_at_trx_commit=2
But read the caveats in the manual. You can also set innodb_buffer_pool_size bigger.
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php
----- Original Message ----- From: "Hristo Chernev" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Thursday, December 02, 2004 7:40 PM
Subject: Very Slow preformance of mysql 4.1.7 innodb
We are considering migration to mysql 4.1. innodb. So I've made some tests comparing innodb and myisam performance. Innodb was very very slow, so I suspect something is wrong. It can't be so bad!
Mysql 4.1.7 on Linux (binary from www.mysql.com).System - Duron 800MHz, 500M=
B
RAM. Myisam database is 6GB. After converting to innodb with ALTER TABLE it =
is
already 12GB. Setup with multiple tablespaces. Here is the config:
[mysqld] port =3D 3307 socket =3D /usr/local/mysql/mysql.sock pid-file =3D /usr/local/mysql/mysql.pid datadir =3D /data skip-bdb skip-locking key_buffer_size =3D 64M max_allowed_packet =3D 2M table_cache =3D 256 sort_buffer_size =3D 1M read_buffer_size =3D 1M read_rnd_buffer_size =3D 4M myisam_sort_buffer_size =3D 16M net_buffer_length =3D 2M thread_stack =3D 1M max_connections =3D 100 query_cache_type =3D 1 maximum-query_cache_size =3D 24M thread_cache =3D 8 thread_concurrency =3D 2 innodb_file_per_table innodb_data_home_dir =3D /ibdata/ innodb_data_file_path =3D ibdata1:500M:autoextend:max:2000M innodb_log_group_home_dir =3D /iblog/ innodb_log_arch_dir =3D /iblog/ innodb_buffer_pool_size =3D 64M innodb_additional_mem_pool_size =3D 8M innodb_log_file_size =3D 16M innodb_log_buffer_size =3D 4M
Tests:
First test was a sql dump file from the production server which was played t=
o
the test mysql server like this:
mysql -f -u root -ppass database < sqldump >> logfile
Myisam finished for about 10 hours and Innodb for 45 hours.
Second test was a script wich simulates multiple user load (20 users at a ti=
me ,
10000 all). It took Myisam half an hour to complete the test and more than 3=
0
hours to Innodb ( infact it is still running).
No errors in the mysql log.
Ofcource there was no optimization for Innodb but though It can't be so bad.
What's wrong?
-- Hristo Chernev
-----------------------------
=C2=F1=E8=F7=EA=EE =E5 =EF=EE-=E1=FA=F0=E7=EE =E8 =F1=E8=E3=F3=F0=ED=EE =F1
=C1=D2=CA ADSL! www.telecom.bg
--
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]
