| Another InnoDB performance question :) [message #1740] |
Wed, 05 September 2007 01:04  |
|
Ok here goes...
I have an innodb with multiple database (MyIASM & InnoDB) but one InnoDB one is causing me major issues.
I have 71 tables using about 4.9G in data.
The machine is an Intel P4 single processor with 2G ram (this use to run fine before I converted to InnoDB...)
I have a few tables that tend to be my problems, one of which has 19 million records, the others 11 & 6.
I believe I've tuned the system as best I can but still see select(*) from history (19MM table) take 15+ minutes! Now if you consider that there are going to be lots of selects / updates on that table every minute then you can see part of my problem 
Any and all suggestions welcome!
Also, on some of the other tables with MM records select count runs in < 1 minute. What should I expect for read/update/inserts in databases / tables of this size? Why would this be good before i switched to innodb?
David
[mysqld]
old_passwords = false # inserted by debconf
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /data/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
#skip-external-locking
innodb_buffer_pool_size=1GB
innodb_log_file_size=256M
innodb_flush_log_at_trx_commit=2
max_connections = 200
skip-locking
key_buffer = 384M
max_allowed_packet = 16M
table_cache = 2000
sort_buffer_size = 10M
read_buffer_size = 10M
myisam_sort_buffer_size = 128M
thread_stack = 1024K
thread_cache_size = 80
thread_cache = 900
query_cache_limit= 1M
query_cache_size= 32M
query_cache_type = 1
# Try number of CPU's*2 for thread_concurrency
#thread_concurrency = 4
log-slow-queries = /var/log/mysql/mysql-slow.log
skip-bdb
|
|
|
Members
Search
Help
Register
Login
Home
