Я пытаюсь повысить производительность веб-сайта за счет оптимизации сервера Mysql. Помимо общей оптимизации, мне кажется, что некоторые операции записи занимают неприемлемо много времени.
Сервер - это Mac os x server 10.5.8 (я думаю, что это 32-битная ОС) с четырехъядерным процессором Intel xeon 2x2,8 ГГц и оперативной памятью 8 ГБ.
Есть 2 таблицы InnoDB, которые используются редко, а остальные 45 таблиц - MyISAM.
Вся база данных составляет 1,2 ГБ, включая таблицу журнала размером 400 МБ, которая только записывается и никогда не читается приложением.
Я бы предположил, что наличие всей базы данных в ОЗУ улучшит производительность. Также я бы предположил, что допуск некоторой потери данных в случае сбоя системы может улучшить производительность записи, но я не знаю, как настроить его, чтобы воспользоваться этим. Данные, которые могут быть потеряны, не критичны и могут быть восстановлены пользователем в случае потери.
Сайт посещают 1-2 человека одновременно.
Я запускал несколько итераций mysqltuner.pl. Вот текущие результаты:
>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.92-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0
-------- Security Recommendations -------------------------------------------
ERROR 1142 (42000) at line 1: SELECT command denied to user ''@'localhost' for table 'user'
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 16m 57s (4K q [4.126 qps], 122 conn, TX: 6M, RX: 1M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 922.0M global + 12.4M per thread (100 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.1G (26% of installed RAM)
[OK] Slow queries: 0% (0/4K)
[OK] Highest usage of available connections: 2% (2/100)
[!!] Cannot calculate MyISAM index size - re-run script as root user
[OK] Query cache efficiency: 42.5% (1K cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 36 sorts)
[OK] Temporary tables created on disk: 8% (6 on disk / 68 total)
[OK] Thread cache hit rate: 98% (2 created / 122 connections)
[OK] Table cache hit rate: 53% (58 open / 109 opened)
[OK] Open file limit used: 4% (103/2K)
[OK] Table locks acquired immediately: 100% (3K immediate / 3K locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
MySQL started within last 24 hours - recommendations may be inaccurate
а вот мой my.cnf
# The MySQL server
[mysqld]
port = 3306
socket = /var/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 512M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
skip-thread-priority
log_slow_queries = 1
long_query_time = 1
Перед перезапуском mysql у меня было 3 медленных запроса, возможно, по 100 тыс. Запросов, но где мне найти журнал с этими запросами?
//
Учитывая эти факторы, которые вы упомянули
Это быстро исключает использование InnoDB, поскольку MySQL 5.0 не имеет InnoDB, который может задействовать несколько ядер (самая старая версия MySQL, в которой есть InnoDB для задействования нескольких ядер, - это подключаемый модуль 5.1.38 InnoDB).
Вы не можете хранить всю базу данных в ОЗУ, потому что MyISAM кэширует только индексы. Однако вы можете загрузить все индексы MyISAM в ключевой буфер.
Первое, что вам нужно сделать, это вычислить правильный размер key_buffer_size. Вот запрос для вычисления этого для вас:
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,31),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;
Обратите внимание, что рекомендация ограничится 2 ГБ, если сумма всех индексов MyISAM превышает 2 ГБ. Также обратите внимание: в инструкции SQL есть предложение (SELECT 2 PowerOf1024) B. Это выведет рекомендацию в МБ. С помощью (SELECT 1 PowerOf1024) B выходы в КБ. С помощью (SELECT 3 PowerOf1024) B выходы в ГБ.
Хорошо, теперь вы знаете, какого размера сделать буфер ключей MyISAM. Как вы его загружаете?
Запустите это:
SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',
db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (SELECT engine,table_schema db,table_name tb,index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM (SELECT B.engine,A.table_schema,A.table_name,A.index_name,
A.column_name,A.seq_in_index
FROM information_schema.statistics A INNER JOIN
(SELECT engine,table_schema,table_name FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) B
USING (table_schema,table_name)
WHERE A.index_type <> 'FULLTEXT'
ORDER BY table_schema,table_name,index_name,seq_in_index) A
GROUP BY table_schema,table_name,index_name) AA
ORDER BY db,tb;
Этот запрос покажет вам каждый запрос, который вам нужно выполнить, чтобы принудительно поместить все страницы индекса MYI в ключевой буфер. Выведите этот запрос в сценарий и запустите этот вывод:
SQLSTMT="SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache FROM (SELECT engine,table_schema db,table_name tb,index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist FROM (SELECT B.engine,A.table_schema,A.table_name,A.index_name,A.column_name,A.seq_in_index FROM information_schema.statistics A INNER JOIN (SELECT engine,table_schema,table_name FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) B USING (table_schema,table_name) WHERE A.index_type <> 'FULLTEXT' ORDER BY table_schema,table_name,index_name,seq_in_index) A GROUP BY table_schema,table_name,index_name) AA ORDER BY db,tb;"
mysql -u... -p.... -AN -e"${SQLSTMT}" > MyISAMIndexPreload.sql
mysql -u... -p.... -A < MyISAMIndexPreload.sql
Попробуйте !!!
Вы сказали, что у вас «ощущение, что некоторые операции записи занимают неприемлемо много времени». Что дает вам это чувство? Вы пытались мера это вообще?
Если вы используете OS X, dtrace - прекрасный инструмент для измерения такого рода вещей. Некоторые люди сделали некоторые из то работать на тебя. По крайней мере, используйте vmstat, iostat или iotop чтобы определить, наблюдаете ли вы значительное замедление работы диска.
Ваш query_cache_size вероятно слишком велик. Записи должны сделать недействительными все записи для этой таблицы из кеша запросов. Чем больше кеш запросов, тем больше времени это занимает и это, вероятно, замедляет вашу запись. В мануале рекомендуются «десятки мегабайт». Вы должны постепенно снижать его и измерять производительность после каждого изменения, чтобы определить, насколько большим должен быть ваш.
В Table locks acquired immediately: 100% (3K immediate / 3K locks) значение предполагает, что блокировка таблицы MyISAM не является проблемой для вас. Это может стать серьезной проблемой для таблиц MyISAM, которые имеют более высокое соотношение операций записи и чтения.
Запросы, которые записываются на диск, включены в отслеживание медленных запросов, поэтому, если вы видите медленные запросы для INSERT, UPDATE, REPLACE, DELETE, TRUNCATE и т. Д., Это будет намеком на то, что у вас возникла проблема.
Журнал медленных запросов может быть файлом, таблицей или обоими сразу. Поскольку вы, похоже, не указали для него местоположение, он должен использовать значения по умолчанию. Из части мануала по журналу медленных запросов и часть на локации журнала:
Если вы не укажете имя для файла журнала медленных запросов, имя по умолчанию будет host_name-slow.log. Сервер создает файл в каталоге данных, если не указан абсолютный путь для указания другого каталога.
Если у вас 8 ГБ ОЗУ, это не значит, что вся ваша БД помещается в ОЗУ. ОС также использует некоторую память для внутренних операций.
Я бы предложил некоторые настройки в my.cnf, они могут сработать для вас
Increase your `key_buffer` from 384M to some high value may be to 512MB as you have sufficien RAM
Размер ключевого буфера, используемого для кэширования блоков индекса для таблиц MyISAM. Не устанавливайте его больше 30% доступной памяти, так как ОС также требует некоторой памяти для кэширования строк. Даже если вы не используете таблицы MyISAM, вы все равно должны установить его на 8-64M, так как он также будет использоваться для внутренних временных дисковых таблиц.
set table_open_cache to 2048
ОБНОВИТЬ
Для правильной настройки my.cnf посетите оптимизация my.cnf
Попробуйте, это может вам помочь ...