FreeSource: MichaelShigorin/MySQL

MySQL for TYPO3 hosting

4 cores, 640+M RAM leased for this VPS

See also: mysql query cache performance optimization

http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html

http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/

mysqltuner

mytop

part of my.cnf follows:

# The MySQL server

[mysqld]

init-connect="set names koi8u"

chroot=/var/lib/mysql

datadir=/db

bdb-logdir=/log

#log=/log/queries

pid-file=/mysqld.pid

skip-locking

skip-networking

socket=/mysql.sock

tmpdir=/tmp

user=mysql

#port = 3306

skip-bdb

low-priority-updates

#wait_timeout = 30

thread_cache = 16

thread_concurrency = 6

#thread_stack = 512K

table_cache = 1024

#myisam_max_sort_file_size = 1G

#myisam_max_extra_sort_file_size = 1G

myisam_sort_buffer_size = 256M

sort_buffer_size = 64M

join_buffer_size = 64M

#key_buffer_size = 128M

read_buffer_size = 2M

#read_rnd_buffer_size = 64M

key_buffer = 512M

record_buffer = 32M

# http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html

#query_cache_type = 1

#query_cache_size = 32M

#query_cache_limit = 1M

#query_cache_min_res_unit=128

# http://typo3.org/development/articles/testing-and-tuning-typo3-performance/page/2/

query_cache_limit = 2M # default was 1M

query_cache_size = 64M # default was 0

query_cache_type = 1

key_buffer_size = 64M # default was 8M

max_allowed_packet = 1M

#max-connections = 50

#max_heap_table_size = 128M

#tmp_table_size = 128M

# http://web-scalability.solartxit.com/2008/05/30/mysql-%d1%82%d1%8e%d0%bd%d0%b8%d0%bd%d0%b3-%d0%bd%d0%b0%d1%81%d1%82%d1%80%d0%b0%d0%b8%d0%b2%d0%b0%d0%b5%d0%bc-%d0%bf%d0%be-%d0%b2%d0%b7%d1%80%d0%be%d1%81%d0%bb%d0%be%d0%bc%d1%83/

max_join_size=10000000

max_sort_length=20

wait_timeout=60

log-slow-queries=/log/slow-queries

long_query_time = 2

log_long_format

#log_warnings

ft_min_word_len = 4

#back_log = 50

#max_connections = 50

myisam_recover

# Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /var/lib/mysql/

#innodb_data_file_path = ibdata1:10M:autoextend

#innodb_log_group_home_dir = /var/lib/mysql/

#innodb_log_arch_dir = /var/lib/mysql/

# You can set .._buffer_pool_size up to 50 – 80 %

# of RAM but beware of setting memory usage too high

#innodb_buffer_pool_size = 256M

#innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size

#innodb_log_file_size = 64M

#innodb_log_buffer_size = 8M

#innodb_flush_log_at_trx_commit = 1

#innodb_lock_wait_timeout = 50

[mysqld_safe]

open-files-limit = 4096

[mysqldump]

quick

quote-names

max_allowed_packet = 16M

[mysql]

#no-auto-rehash # faster start of mysql but no tab completition

# Remove the next comment character if you are not familiar with SQL

#safe-updates

[isamchk]

key_buffer = 512M

key_buffer_size = 128M

sort_buffer_size = 512M

write_buffer = 4M

read_buffer = 4M

[myisamchk]

key_buffer = 512M

key_buffer_size = 128M

sort_buffer_size = 512M

read_buffer = 4M

write_buffer = 4M

[mysqlhotcopy]

interactive-timeout