Enabling Logs - Stephen Marquard
You can switch on mysql logging easily enough with /etc/my.cnf settings like:
[mysqld]
log=/var/log/mysql/mysqld.log
The mysql connector also has a number of logging options that can be enabled.
MySQL Configurations
Aaron Zeckoski
\[mysqld\]
#log=/usr/local/mysql/mysqld.log
default-storage-engine = innodb
innodb_file_per_table
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
log-slow-queries = /usr/local/mysql/mysql-slow.log
long_query_time = 1
character-set-server = utf8
query_cache_size = 64M
skip-external-locking
max_connections=200
read_buffer_size=2M
sort_buffer_size=2M
Stephen Marquard
On our test/build server (4G RAM, also runs maven/tomcat, etc.), we have:
[mysqld] #log=/var/log/mysql/mysqld.log log-slow-queries = /var/log/mysql/mysql-slow.log long_query_time = 1 default-table-type=innodb character-set-server = utf8 query_cache_size = 64M innodb_buffer_pool_size = 384M
It's useful to uncomment the full query logging for debugging or performance issues.
To use the query cache, you need a connection string like:
url@javax.sql.BaseDataSource=jdbc:mysql://localhost:3306/sakai?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=false&cachePrepStmts=true&prepStmtCacheSize=4096&prepStmtCacheSqlLimit=4096
To get logs of slow queries with better precision in your tomcat log, you can add:
&slowQueryThresholdMillis=500&logSlowQueries=true&maxQuerySizeToLog=128000
On a production server, the main settings that you'd increase for mysql would be
innodb_buffer_pool_size (bigger is better) and query_cache_size (not likely to
exceed 256M in practice).