Hi all,
Because I want to use transactions in the future I have converted all
tables of a copy of our production database server (1800+, 512 MB RAM,
Linux) to InnoDB format. No problem until now. First, let me show you
settings in my.cnf:
key_buffer = 16M
table_cache = 128
sort_buffer_size = 1M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
thread_concurrency = 8
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
Question: Is sort_buffer_size and read_buffer_size relevant to InnoDB?
All these settings seem to be fine for me. With MyISAM I have used a
key_buffer of 256M and sort_buffer_size of 4M which procuded very fast
database accesses. mytop's output:
MySQL on localhost (4.0.15-standard-log)
up 0+23:14:39 [04:23:24]
Queries: 5.7M qps: 72 Slow: 34.0 Se/In/Up/De(%): 63/10/15/05
Cache Hits: 1005.2k Hits/s: 12.3 Ratio: 27.3%
Key Efficiency: 100.0% Bps in/out: 8.0k/33.8k
But now everything is slow, I don't know why. Even without load each
query takes a bit longer. Shouldn't it be vice versa? Then I did some
load testing: CPU usage and system load raised by 100 percent. That's
not normal for me, does InnoDB need more power, more momory? While
testing MySQL was able to handle all the queries but, well, not as
fast as I would like to have it in productive environment.
I have also noticed that some more complex queries (select with 4
joins and 2 orders) last much too long. With MyISAM everything was <
0.5s but now I sometimes have a strange one that is listed for several
houndred seconds (?!) in the process list. That's not normal, isn't
it? Something strange is going on here and I do not have a clue what I
could be. Playing around with the settings and raising InnoDB's pool
size to 80% of memory didn't change anything.
So, I'm not familiar with this great InnoDB thing, maybe you have some
ideas. :)
Thanks in advance!
Matthias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
