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?


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

Datafolder: 200mb

basedir=“C:/Program Files/MySQL/MySQL Server 4.1/”
datadir=“C:/Program Files/MySQL/MySQL Server 4.1/Data/”

#*** MyISAM Specific options

#*** INNODB Specific options ***

you need to determine qhich queries are creating the locks, and which are being blocked by them

when the site starts to run slow, you can try


to get more feedback on which queries are performing badly.

Then run an EXPLAIN on each one to see if they are running optimally.

You can mix and match MyIsam with innodb, but personally i prefer to just convert everything to innodb and elimiate the resources needed to feed myisam

Your innodb config may need tweaking, but Ill leave that to the other guys to look at

Thanks for you tip carpii!
I will try this then i get help for my .ini file.
I have changed all back and wait for more feedback before i change back to innodb.

Can anyone help my innodb config?

Please, i realy need some help.
Can someone help me to set good startvalues in my my.ini file?
Then i get 1000 login users, sites get very slow…

Hello Henrik

My advice is that you switch completely to InnoDB. Joins between MyISAM and InnoDB tables may hurt performance badly.
Keep MyISAM for logging or archiving (if you do any).

Your default values are OK, but I suggest that (after converting to InnoDB) you keep MyISAM key_buffer_size to a minimum (say 128MB)

innodb_buffer_pool_size is typically 60-80% of available memory. I’m not a Windows guy so I don’t know how good the values may be but under Linux, with a server dedicated to MySQL, I’ll put the values to something like 2048M minimum. Your mileage may vary if you have other apps like IIS or Apache on the server.

The log_file_size should be something like 25% of buffer pool size, so for your config it should be much larger (256M with the current values)

Also I don’t know the impact of InnoDB thread concurrency on windows but I would set it to 2 x number of CPU by default.

I agree about moving entirely to innodb. You can virtually eliminate your myIsam resources and there are significant gains in concurrency to be made with innodb, at the expense of larger data files.

Remember innodb doesnt support full text indices though, which might be a consideration. You could always use Sphinx though.

The other thing you really need to do is study your process list as I mentioned earlier. If you have suboptimal queries then it might be that no amount of config tweaking will address the problem that your queries are just badly written.

Find the slow queries, run EXPLAIN on them and then see if any indices can be added to help, or if the query can be rewritten.

Thank you both for your help! nice!
Now i think i can fill in some value correct. )
Is it ok to keep myisam config values?

I dont realy understund that you meen about this:
" Remember innodb doesnt support full text indices though, which might be a consideration. You could always use Sphinx though. "
That can happen?

Yes, you can keep MyISAM config values. Know that it will allocate unnecessary memory if you don’t use MyISAM then.

About Full text indexes : They are not supported in InnoDB tables

But if you don’t use this function there’s no problem to convert your tables. You could use MyISAM consolidated search tables and do a join, though. It will still work )