Re: InnoDB Performance

[Daniel Kasak]

Marcin Lewandowski wrote:

> Hi,
>
> I've got webserver. There, I've got phpbb2 with circa 6000 users
> (average 70-100 users online). There was problems with locking or
> something else, when phpbb was using myisam tables. Yesterday, we have
> converted tables to innodb, because it should be more effective.

It depends on what you're doing. If you don't need transactions or
fine-grained locking, MyISAM may be a better option. In cases where you
have clients that keep locks open for an extended period of time ( MS
Access ), InnoDB may become the only option. With a webserver as the
only client, I don't think this will apply to you. If you don't need
transactions, consider moving back if you can't get the performance you
need.

> Since then we have high system load.
>
<snipped>

>
> MySQL is 4.0.22-log (gentoo linux)
>
> Here comes my.cnf:
>
>
> [client]
> port            = 4417
> socket          = /var/run/mysqld/mysqld.sock
>
> [safe_mysqld]
> err-log         = /var/log/mysql/mysql.err
>
> [mysqld]
> user            = mysql
> pid-file        = /var/run/mysqld/mysqld.pid
> socket          = /var/run/mysqld/mysqld.sock
> log-error       = /var/log/mysql/mysqld.err
>
>
> innodb_data_file_path = ibdata1:64M:autoextend
> innodb_buffer_pool_size=128M
> innodb_flush_log_at_trx_commit=1
>
I don't think you need this, and this will slow things down for you too.

>
>
> basedir         = /usr
> datadir         = /data/mysql
> tmpdir          = /tmp
> language        = /usr/share/mysql/polish
> log-slow-queries = /data/logs/mysql/slow.log
> log-update      = /data/logs/mysql/update.log
>
Try turning that log-update option off. If you want to use logging for
backup purposes, use the binary logging format.
Also, in this section [mysqld] add:

query_cache_type        = 1
query_cache_size        = 16M

and restart mysql. This should give you a nice performance hit - how
much will depend on how efficient phpbb2 is - with Access ( which issues
the same query over & over again ) I get a very big performance
increase. You may need to tune the query_cache_size bit.

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: ([phone] / F: ([phone]
email: dkasak@stripped
website: http://www.nusconsulting.com.au