Richard,
I calculate 430 s / 116 s = 3.7, not 7 :).
Last time you had extremely slow execution probably
because of fsync. Obviously the doublewrite method
has fixed it, since the performance is not that bad any
more. Linux kernel 2.4 might be better.
Remember that MyISAM uses the file cache of the
operating system like it own key cache. Thus the insert
will probably run totally in main memory for MyISAM.
InnoDB has crash recovery and must flush the data
physically to disk when it does not fit in the buffer
pool.
On a dedicated database server you may set the buffer
pool to up to 80 % of physical memory.
An interesting question is the CPU usage: what does
'top' say about it for both types of inserts?
What if you increase the key cache and the
buffer pool?
Regards,
Heikki
Copied message:
.................
Though i would give Innodb another go with the latest mysql-max RPM's
(3.23.41)
for linux running on a Redhat machine. This is on a Compaq ML-370, 667Mhz
with 1gig
mem on kernel 2.2.19. Disks are Raid-5. Huge performance difference between
Innodb
and MyISAM for a simple table create (7 times slower!). Total size of
original maillog.email
table is around 700meg (varchar, thus packed), so we ar maybe looking at
around
100-150meg for the new table:
mysql> select count(*) from maillog.email;
+----------+
| count(*) |
+----------+
| 6744395 |
+----------+
1 row in set (0.00 sec)
mysql> create table mailtest type=innodb select * from maillog.email limit
1000000;
Query OK, 1000000 rows affected (7 min 9.92 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> create table mailtest2 select * from maillog.email limit 1000000;
Query OK, 1000000 rows affected (1 min 56.13 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
my.cnf:
[mysqld]
#log-long-format
log = mysqld.log
#log-slow-queries = mysqld-slow.log
set-variable = key buffer=64M
#set-variable = max allowed packet=1M
set-variable = table cache=128
set-variable = sort buffer=4M
set-variable = record buffer=1M
#set-variable = thread cache=8
#set-variable = thread concurrency=8 # Try number of CPU's*2
#set-variable = myisam sort buffer size=64M
innodb data file path = ibdata1:512M
innodb data home dir = /data/innodb/ibdata
set-variable = innodb mirrored log groups=1
innodb log group home dir = /data/innodb/iblogs
set-variable = innodb log files in group=3
set-variable = innodb log file size=30M
set-variable = innodb log buffer size=16M
innodb flush log at trx commit=1
innodb log arch dir = /data/innodb/iblogs
innodb log archive=0
set-variable = innodb buffer pool size=80M
set-variable = innodb additional mem pool size=10M
set-variable = innodb file io threads=4
set-variable = innodb lock wait timeout=50
[myisamchk]
set-variable = key buffer=256M
set-variable = sort buffer=256M
set-variable = read buffer=2M
set-variable = write buffer=2M
--
Richard Ellerbrock
[EMAIL PROTECTED]
----------------------------------------------------------------------------
----
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
