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
