Показать страницуСсылки сюдаНаверх Эта страница только для чтения. Вы можете посмотреть её исходный текст, но не можете его изменить. Сообщите администратору, если считаете, что это неправильно. Собственно описание настроек и рекомендации, т.к. нет толкового описания по оптимизации mysql и если есть какие-то предложения и замечания, можно обсудить здесь[link-forum], в wiki будут потом сделаны корректировки. <code> [client] port = 3306 socket=/var/run/mysqld/mysqld.sock </code> Разрешить выполнять запросы из файлов: LOAD DATA INFILE "/tmp/test.sql" INTO TABLE test; <code> [mysql] loose-local-infile=1 </code> Порт, сокет, рабочая папка... Если не пишет в лог проверьте путь, mysql не умеет создавать папки. <code> [mysqld_safe] err-log = /var/log/mysql/mysql.err [mysqld] #start options user = mysql port = 3306 socket = /var/run/mysqld/mysqld.sock pid-file = /var/run/mysqld/mysqld.pid log-error = /var/log/mysql/mysql.err basedir = /usr datadir = /var/lib/mysql language = /usr/share/mysql/russian skip-external-locking symbolic-links=0 </code> Если медленно отрабатывает DNS имеет смысл указать <code> skip-name-resolve </code> Имеет ли смысл понижать приоритет на обновление в преимущество к выборкам? У меня статистика допустим такая, аптайм правда сутки select 8,206 k 265.423 k 80.88% update 1,514 k 48.955 k 14.92% <code> low-priority-updates </code> **query_cache** <code> query_cache_size = 256M query_cache_limit = 256M </code> убеждаемся query_cache_type=ON Кэширование запросов, т.е. хэш-массив, это что-то типа двумерного массива где ключ это запрос, а значение - данные. Как работает: при старте mysql выделяет блок памяти равный **query_cache_size**, затем поступает запрос и если он без дерективы SQL_NO_CACHE ну и понятно если таблица не изменялась, то при поступлении первых строк начинает выделятся память размером **query_cache_min_res_unit**(По умолчанию 4КБ) и так далее до тех пор, пока размер выборки не привышает **query_cache_limit**. Параметр **query_cache_min_res_unit** может забить память, т.е. я так понял фрагментирует её этими кусочками по 4Кб, **Qcache_free_blocks** будет сильно увелививаться, **query_cache_min_res_unit** нужно увеличивать. Либо посчитать по формуле: <code> query_cache_min_res_unit = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache. </code> У меня получилось 2Кб, можно значит его уменьшать. Можно также уменьшить **query_cache_size**, думаю 256Мб всё таки многовато будет, т.к. больше незадействованного кэша, плюс фрагментация, выборка из кэша прозводится сложнее и приводит к доп. нагрузке. Вообщем **Qcache_free_blocks** должен стремится к единице. Также можно высчитать эффективность работы кэша по формуле: <code> qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached) </code> У меня получилось 0,4 - т.е. 40% попадает в кэш. Если срабатывает счетчик **Qcache_lowmem_prunes** то **query_cache_size** нужно тоже увеличивать. Вообще, говорят в 6 или 7 версии кэширование хотят отменить. Переменные можно просматривать или через phpMyAdmin во вкладке "Состояние" или через консоль: <code> SHOW GLOBAL STATUS LIKE 'Qcache%' SHOW VARIABLES LIKE 'query_cache_%'; </code> Буферы для объединённых выборок(SELECT JOIN....), вообще каждый буфер цепляется к созданному потоку, так сказать резервируется для него память, это тоже нужно учитывать. <code> join_buffer_size = 256M max_join_size=256M </code> блокировать хост при 1000 неудачных попыток работы-соединения <code> max_connect_errors = 1K </code> Увеличивать если будет ошибка **«Packet too large»**, т.е. при передаче данных на сервер, допустим заливаете дапм. Помоему это актуально при приёме-передаче данных типа BLOB, т.к. если допустим видео перевести в двоичный формат размер пакета потребуется увеличить. <code> max_allowed_packet=16M </code> Держать открытыми таблицы для потоков, чтобы не тратить ресурс каждый раз на открытие. Если растёт значение **Open_tables**, то можно увеличивать, параметр равен кол-ву таблиц. В нашем случае с запасом на 500 таблиц. По сути это два одинаковых параметра - **table_open_cache** это после версии 5.1 <code> table_open_cache = 1k table_cache = 1k </code> Максимальный размер памяти для временных таблиц Mysql, у нас в конфиге 1Гб. Но всёравно **Created_tmp_disk_tables** немного растёт, чем меньше это значение тем лучше. Но может это вызвано тем, что согласно документации временные таблицы не могут быть созданы в памяти когда в запросе есть TEXT или BLOB <code> max_heap_table_size = 1024M tmp_table_size = 1024M </code> **thread_cache_size** cледует увеличивать если растет **thread_created** и он значительно больше чем **thread_cache**, т.е. клиент отключился, но кеш остался, затем клиент подключился, поток заново не создаётся а берётся из кэша, ресурс на подключение не тратится, значения 32 для нашего случая вполне хватит, если ставить 64 или предельное 100, то это нужно для нагруженных вэб-серверов. <code> thread_cache_size = 32 </code> Увеличивать если будет ошибка **«Too many connections»**, думаю это только для веб-серверов актуально <code> max_connections = 64 </code> **Myisam** За ненадобностью не нужен, но если всё таки осталось часть таблиц, дайте ему немного В нашем случае много давать не нужно, ведь каждый трейд зарезервирует для них место. <code> key_buffer_size = 8M myisam_sort_buffer_size = 2M sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M </code> Ниже включается бинари лог, разкоментируйте следующие 2 строки чтобы включить Нам не нужно, т.к. мы делаем резервную копию. #server-id=1 #log-bin=mysqld-bin Здесь можно перенести размещение временных таблиц в tmpfs, но мы используем InnoDB, нам не нужно. tmpdir = /tmp/ **InnoDB** Каждую таблицу хранить в отдельном файле <code> innodb_file_per_table </code> Не использовать системный буфер, только свой. <code> innodb_flush_method=O_DIRECT </code> В идеале рекомендуют каждую инстанцию ставить по 1Гб, т.е. в данном примере буфер состоит из 3 инстанций, т.к. buffer_pool_size = 3G Размер памяти выделенный под данные и индексы, рекомендуется выставлять до 70-80% доступной ОЗУ. <code> innodb_buffer_pool_size = 3G </code> Если у InnoDB закончится память, то она запросит 4М у операционной системе, о чём предварительно запишет в лог. <code> innodb_additional_mem_pool_size = 4M </code> Путь к базе <code> innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ </code> Параметры файла данных, авто-расширение с шагом 10Мб. Но мы храним каждую таблицу в отдельном файле. Хотя есть некоторое мнение, что хранить можно в одном файле, особенно мелкие таблицы, не тратятся ресурсы на открытие таблиц. <code> innodb_data_file_path = ibdata1:10M:autoextend </code> Размер одного лог файла, при превышении размера будет создан ещё один файл, максимальное значение рекомендуют до 512МБ. Чем больше лог, тем дольше восстановление. Если лог будет маленький часто будет переполнятся и обновляться, т.е. по его окончании будет создаваться контрольная метка. Много тоже нельзя, долго будет стартовать после аварийного завершения. <code> innodb_log_file_size = 256M </code> Кол-во файлов журналов <code> innodb_log_files_in_group = 2 </code> Размер буфера лога, рекомендуют выставить максимум 8М, если имеется большое колво транзакций и **Innodb_log_waits** увеличивается, тогда ещё увеличить. <code> innodb_log_buffer_size = 8M </code> Сбрасывать лог транзакции раз в секунду при занчении - 0. Считается самый оптимальный вариант между производительностью и надёжностью. Самый медленный 1 - записывать лог после каждой транзакции. 2 - самый быстрый вариант, лог сбрасывается только в кеш ОС, а она потом фиксирует в лог, но так как у нас есть и финансовые данные ставим раз в секунду сбрасывать лог транзакций. Хотя можно будет попробывать поставить и "2", будет создавать контрольные точки раз в 2-5 секунд. "1" - это актуально для финансовых операций в базе, фиксируется каждая транзакция, нам не нужно. <code> innodb_flush_log_at_trx_commit = 0 </code> Тайминг ожидания блокировки записи какой нибудь транзакции, я так понял что если транзакция сама себя заблокировала, ну т.е. что-то типа бесконечного цикла, до для этого есть этот тайминг, если запись не разблокировалась через 70 мс, то произойдёт отмена блокировки и откат изменений. <code> innodb_lock_wait_timeout = 70 </code> <code> [mysqldump] quick Тут думаю можно и 64 не пожалеть, хотя больших строк у нас нет, но для дапма не жалко)) max_allowed_packet = 64M [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysqld_safe] log-error=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid </code> billing/mysql_tunning.txt Последнее изменение: 8 лет назад — ld