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
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
SHOW PROCESSLIST
and
SHOW INNODB STATUS
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.
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…
//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?
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 )