Как получить максимальную отдачу от MySQL на машине QuadCore с 16 ГБ оперативной памяти?

Я использую сервер MySQL 5.5 на своей рабочей станции для анализа научных данных и задаюсь вопросом, как настроить MySQL, чтобы максимально использовать его по производительности. Типы запросов, которые я обычно запускаю, включают объединение 10-20 таблиц и могут работать довольно долго, от одной до нескольких минут вообще не являются исключением. Только очень немногие пользователи одновременно получают доступ к базе данных (максимум 5). Я переместил сервер с Lenovo Thinkpad T61 с двухъядерным процессором 2,2 ГГц и 4 ГБ оперативной памяти на следующую совершенно новую машину с компонентами, выбранными вручную:

  • Intel i7 3770, 4x 3,4 ГГц (работает @ 4x3,7 ГГц)
  • Чипсет Z77
  • 16 ГБ оперативной памяти DDR3 1600
  • Windows 7 Prof 64-bit
  • Сервер Windows и MySQL работает на твердотельном накопителе Intel 520 series.

Первые тесты (выполнение одного и того же запроса на обеих машинах) показали окончательное улучшение скорости для нового, но запросы все еще занимают много времени, и я ожидал большего повышения. Запросы, о которых идет речь, довольно хорошо оптимизированы, т. Е. Все таблицы имеют правильный ключ, который также используется как «объяснение расширенного».

Теперь к моим текущим настройкам MySQL: Сначала я должен упомянуть, что я давно перешел из MyISAM в Innodb.

Некоторые мои настройки my.ini (т. е. отклонения от настроек по умолчанию):

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
#tmp_table_size=35M
tmp_table_size=4000M
max_heap_table_size=4000M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
#innodb_buffer_pool_size=96M
innodb_buffer_pool_size=800M

general-log
expire_logs_days = 60
general_log_file = "F:/my_query_mysql.log"
log-output = TABLE
optimizer_search_depth = 0 #meant to cure the "statistics state" bug in some queries

Я хотел бы знать, будет ли кто-нибудь предлагать изменения вышеуказанных номеров или даже дополнительные настройки, о которых я не знаю.

Буду признателен за полезное замечание.

Стив

EDIT: у меня есть два запроса, включающие объединения по 10-20 таблицам и запускаемые на моем ноутбуке Lenovo и новом ПК. Запрос № 1 занял 3 м36 на новой машине против 9 м11 на ноутбуке; Запрос № 2 занял 22,5 с на рабочей станции против 48,5 с на ноутбуке. Таким образом, скорость выполнения была улучшена примерно на 2-2,5. На рабочей станции не использовалось даже 50% ОЗУ. Средняя загрузка процессора по четырем ядрам (как сообщалось диспетчером задач Windows) составляла лишь около 13%. Нагрузка на основе ядра (как сообщалось Core Temp) составляла около 25-40% для ОДНОГО ядра, а для остальных - <= 10%, что указывает на то, что MySQL не использует несколько ядер для одного запрос.

10 голосов | спросил Steve06 21 J000000Saturday12 2012, 14:27:29

4 ответа


5

Поскольку вы используете MySQL 5.5, вам может потребоваться настроить настройку InnoDB для доступа к нескольким ядрам

Вот настройки, которые вы должны использовать

innodb_thread_concurrency устанавливает верхнюю границу на количество параллельных потоков, которые InnoDB может держать открытым. Наилучший круглый номер для установки (2 X Количество процессоров) + Количество дисков. ОБНОВЛЕНИЕ . Поскольку я узнал из первых рук на конференции Percona NYC, вы должны установить это значение 0, чтобы предупредить InnoDB Storage Engine, чтобы найти наилучшее количество потоков для среды, в которой он работает.

innodb_concurrency_tickets устанавливает количество потоки, которые могут безнаказанно обойти проверку параллелизма. После достижения этого предела проверка параллельности потоков снова становится нормой.

innodb_commit_concurrency устанавливает количество которые могут быть совершены. Поскольку значение по умолчанию равно 0, это не позволяет делать любое количество транзакций одновременно.

innodb_thread_sleep_delay устанавливает количество миллисекунды поток InnoDB может быть бездействующим до повторного входа в очередь InnoDB. Значение по умолчанию - 10000 (10 секунд).

innodb_read_io_threads и innodb_write_io_threads (оба с MySQL 5.1.38) выделяют указанное количество потоков для чтения и записи. Значение по умолчанию - 4, а максимальное - 64.

innodb_replication_delay налагает задержку потока на ведомый имеет значение innodb_thread_concurrency.

Вот мои прошлые сообщения в MySQL 5.5 и активация нескольких ядер для InnoDB

ответил RolandoMySQLDBA 23 J000000Monday12 2012, 18:55:57
2

Percona- Ведущий консультант MySQL предлагает мастер настройки MySQL . Он позволяет вам настроить my.cnf/my.ini в зависимости от конфигурации вашей системы.

Также люди Percona выпустили книгу под названием « Высокопроизводительная MySQL ». Третье издание было недавно выпущено и подробно описывает настройку.

ответил Mahesh Patil 23 J000000Monday12 2012, 14:35:25
1

Использование памяти: см. http://mysql.rjweb.org/doc.php/memory (Большинство перегонов не будут иметь достаточной разницы в материи.)

max_heap_table_size = 4000M Опасно высоко! Если это необходимо 4 пользователям, вы не в оперативной памяти и поменяете местами. Перемещение ухудшает производительность намного больше, чем что угодно.

Запросы, занимающие более нескольких секунд: их следует изучать для улучшения; пожалуйста предоставьте SHOW CREATE TABLE; ПОКАЗАТЬ СТАТУС ТАБЛИЦЫ; EXPLAIN SELECT

ответил Rick James 24 J000000Tuesday12 2012, 03:21:40
0

Вы можете также рассмотреть варианты отхода. Такие, как PostgreSQL для FreeBSD. Но переключение с Windows на Linux приведет к повышению производительности.

ответил ilhan 11 MarpmFri, 11 Mar 2016 23:43:30 +03002016-03-11T23:43:30+03:0011 2016, 23:43:30

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132