David Schneider-Joseph schrieb:
On Jan 29, 2008, at 6:09 PM, Jan Kirchhoff wrote:
what hardware are you running on and you much memory do you have?
what version of mysql?| |
How did you set innodb_buffer_pool_size?
Hardware:
Dual AMD Opteron 246 2.0 GHz
4 GB DDR RAM (no swap being used)
Dual 146 GB SCSI drives with a RAID 1
Software:
RedHat Linux, kernel version 2.6.9-55.ELsmp
MySQL 5.0.45-community-log
[...]
As best I can tell, our server is tuned appropriately. We've
definitely spent effort on tuning it already.
This is on my Replication-Slave I use for backups:
mysql> create table test (id int NOT NULL auto_increment, x int NOT
NULL, primary key (id), key (x)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into test (x) select cast(rand()*10000000 as unsigned)
from verybigtable limit 1000000;
Query OK, 1000000 rows affected (20.20 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> insert into test (x) select cast(rand()*10000000 as unsigned)
from verybigtable limit 1000000;
Query OK, 1000000 rows affected (17.60 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> insert into test (x) select cast(rand()*10000000 as unsigned)
from verybigtable limit 1000000;
Query OK, 1000000 rows affected (15.67 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> insert into test (x) select cast(rand()*10000000 as unsigned)
from verybigtable limit 1000000;
Query OK, 1000000 rows affected (14.91 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> insert into test (x) select cast(rand()*10000000 as unsigned)
from verybigtable limit 1000000;
Query OK, 1000000 rows affected (17.89 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> insert into test (x) select cast(rand()*10000000 as unsigned)
from verybigtable limit 1000000;
Query OK, 1000000 rows affected (16.24 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
your innodb_log_buffer_size and innodb_log_file_size look tiny, this is
my config on the system:
(dual-socket dual-core opteron 2216 with Areca sata-raid10 (w/ bbu) and
16gb RAM)
+---------------------------------+---------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------+
| innodb_additional_mem_pool_size | 16777216 |
| innodb_autoextend_increment | 1000 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 10485760000 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | /var/lib/mysql/ibdata:100M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 0 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 120 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | /var/lib/mysql2/innodb/ |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 33554432 |
| innodb_log_file_size | 536870912 |
| innodb_log_files_in_group | 3 |
| innodb_log_group_home_dir | /var/lib/mysql2/innodb/ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | OFF |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 16 |
| innodb_thread_sleep_delay | 10000 |
+---------------------------------+---------------------------------------+
play around with innodb_log_buffer_size, innodb_log_file_size and try
to set innodb_flush_log_at_trx_commit=0.
Do you don't have a BBU on your raid-controller?
let me know if that changes anything.
Jan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
