FreeBSD: Оптимизация MySQL

Имеем VPS
CPU 4×1000
Memory 2G
База форум phpBB3 1.2 Gb + 0.18 Gb нагурзка 30-150 пользователей на форуме по данным за последние 5 минут

Замечено: При изменении настроек my.cnf и перезагрузки MySQL база убивается, видимо это связано с нагрузкой форума. После убиения можно восстановить работу базы если отключить форум через ACP phpBB и прогнать в PhpMyadmin оптимизацию(10 минут) и восстановление(15-20 минут) базы, не включая форума перезагрузить MySQL и после этого включить форум!

Чтобы хоть как-то понять что происходит с MySQL сколько она памяти использует надо прогнать утилитку mysql tuner

fetch http://mysqltuner.com/mysqltuner.pl
perl mysqltuner.pl
далее во FreeBSD надо ввести пароль администратора MySQL

>> MySQLTuner 1.0.1 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 797M (Tables: 134)
[!!] Total fragmented tables: 18

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12h 42m 34s (1M q [25.057 qps], 114K conn, TX: 4B, RX: 140M)
[--] Reads / Writes: 85% / 15%
[--] Total buffers: 1.4G global + 3.6M per thread (110 max threads)
[!!] Maximum possible memory usage: 1.8G (88% of installed RAM)
[OK] Slow queries: 0% (25/1M)
[OK] Highest usage of available connections: 45% (50/110)
[OK] Key buffer size / total MyISAM indexes: 1.2G/1.0G
[OK] Key buffer hit rate: 99.3% (39M cached / 291K reads)
[OK] Query cache efficiency: 36.5% (262K cached / 718K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (3 temp sorts / 39K sorts)
[!!] Joins performed without indexes: 270
[OK] Temporary tables created on disk: 6% (2K on disk / 44K total)
[OK] Thread cache hit rate: 95% (5K created / 114K connections)
[OK] Table cache hit rate: 94% (512 open / 540 opened)
[OK] Open file limit used: 56% (650/1K)
[OK] Table locks acquired immediately: 99% (629K immediate / 632K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 2.0M, or always use indexes with joins)

Обычно утилита дает рекомендации по увеличению чего-то

На что следуют обратить внимание

[--] Total buffers: 1.4G global + 3.6M per thread (110 max threads)
[!!] Maximum possible memory usage: 1.8G (88% of installed RAM)

Показан максимально возможный уровень использования памяти. Вторая часть первой строки вычисляется по формуле (read_buffer + sort_buffer + join_buffer_size) * max_connections

Строка

[OK] Highest usage of available connections: 45% (50/110)

Показывает максимальное количество подключений к базе - зная всё это можно предположить справится ли сервер с пиковой нагрузкой (если будет 110 подключений) - в моём случае не справится потому что в теории будет использует 1.8 G - при этом мало останется для Apache и прочего.

Строка

[OK] Key buffer size / total MyISAM indexes: 1.2G/1.0G

Утилита просит чтобы Key buffer size был выше MyISAM indexes - жаль не понятно как он используется потому что вроде бы никогда до конца не наполняется но при этом память сразу резервируется вся

Строка

[!!] Joins performed without indexes: 270

Вот это совершенно непонятная строка потому что увеличение join_buffer_size не помогает но сильно сжирает память

Конфигурация my.cnf при которой MySQL пока справляется с нагрузкой

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

key_buffer=1274M
table_cache=512
read_buffer=128K
sort_buffer=1M
join_buffer_size=2M
max_connections=110
wait_timeout=10
max_connect_errors=10
query_cache_limit=1M
query_cache_size=128M
query_cache_type=1
tmp_table_size=256M
thread_cache_size=4
connect_timeout=10
interactive_timeout=120
skip-innodb
skip-bdb
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid