MySQL Performance: MySQL => MyISAM to INNODB..

MyISAM to INNODB.. [message #1266]

Wed, 16 May 2007 05:10

Henkz
Messages: 6
Registered: May 2007

Junior Member

Our community starts to gets slowly in the evening and i tried to change from myisam to innodb. For some hours its works fine. But in the evening my site gets very slow and a error message starts to show sometimes.

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[MySQL][ODBC 3.51 Driver][mysqld-4.1.11-nt]Lock wait timeout exceeded; try restarting transaction

Is it my querys or can i do some performance to speed up my site and not gets any erromsg.

Do i need to change all myisam to innodb? is it ok only convert the heavy tables?

Need more info to help?

//Henrik

Server specifications:
2x Intel Xeon CPU 3.2 ghz
4 GB RAM
Windows 2003
IIS 6.0
MySQL 4.1.11
ASP 3.0

Datafolder: 200mb

my.ini:
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 4.1/"
datadir="C:/Program Files/MySQL/MySQL Server 4.1/Data/"
default-character-set=latin1
default-storage-engine=INNODB
max_connections=800
query_cache_size=200M
table_cache=1520
tmp_table_size=30M
thread_cache_size=38

#*** MyISAM Specific options
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100M
myisam_sort_buffer_size=30M
key_buffer_size=1024M
read_buffer_size=64M
read_rnd_buffer_size=256M
sort_buffer_size=128M

#*** INNODB Specific options ***
innodb_additional_mem_pool_size=6M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=3M
innodb_buffer_pool_size=1000M
innodb_log_file_size=50M
innodb_thread_concurrency=18

Report message to a moderator