Как оптимизировать настройку MySQL?

MySQL/PostgreSQL/SQLite/Oracle/M$SQL/....

Модератор: terminus

Правила форума
Убедительная просьба юзать теги [code] при оформлении листингов.
Сообщения не оформленные должным образом имеют все шансы быть незамеченными.
gogi
рядовой
Сообщения: 16
Зарегистрирован: 2008-01-04 22:41:31

Как оптимизировать настройку MySQL?

Непрочитанное сообщение gogi » 2008-02-21 22:17:09

Народ, кто посоветует как и что поменять в настройках Мускла для максимальной производительности? Мускл 5-й, живет под FreeBSD 6.3, машинка выделенная под базу. Машинка двухпроцессорная, мускл скомпилен в thread-варианте. Innodb баз нет и их поддержка не компилилась. Все таблицы в MyISAM. Мозгов на сервере 4 гига.

Буду зело признателен за совет по параметрам для этого случая! Заранее спасибо!

Хостинговая компания Host-Food.ru
Хостинг HostFood.ru
 

Услуги хостинговой компании Host-Food.ru

Хостинг HostFood.ru

Тарифы на хостинг в России, от 12 рублей: https://www.host-food.ru/tariffs/hosting/
Тарифы на виртуальные сервера (VPS/VDS/KVM) в РФ, от 189 руб.: https://www.host-food.ru/tariffs/virtualny-server-vps/
Выделенные сервера, Россия, Москва, от 2000 рублей (HP Proliant G5, Intel Xeon E5430 (2.66GHz, Quad-Core, 12Mb), 8Gb RAM, 2x300Gb SAS HDD, P400i, 512Mb, BBU):
https://www.host-food.ru/tariffs/vydelennyi-server-ds/
Недорогие домены в популярных зонах: https://www.host-food.ru/domains/

Аватара пользователя
Alex Keda
стреляли...
Сообщения: 35462
Зарегистрирован: 2004-10-18 14:25:19
Откуда: Made in USSR
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение Alex Keda » 2008-02-22 0:16:52

а чё - тупит?
если нет - не трожь =)
=======
ты скажи что сделал - что не устраивает.
а так - на гуще кофейной гадать будем.
в общем случае - отай ему почти всю раму.
Убей их всех! Бог потом рассортирует...

Аватара пользователя
serge
майор
Сообщения: 2133
Зарегистрирован: 2006-07-30 15:34:14
Откуда: Саратов
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение serge » 2008-03-09 10:25:07

А вот пример когда тупит и прилично:

Код: Выделить всё

PID     USERNAME     THR PRI   NICE   SIZE     RES    STATE  TIME      WCPU   COMMAND
51250 mysql             14    20    0        331M   117M  kserel   239:42   26.03%   mysqld
И так висит все время, часто загрузка проца поднимаеться выше.
Кол-во запросов в секунду ~10
В phpmyadmin ругань на

Код: Выделить всё

Slow_queries	              3,348
Handler_read_rnd            33 M
Handler_read_rnd_next	  262 M
Qcache_lowmem_prunes	  4,802
Created_tmp_disk_tables 113 k
Select_full_join	              1,527
Sort_merge_passes	  4,438
Opened_tables	              810
Table_locks_waited	  115
Баз немного, около 20 штук. Самая большая это dspam. Около 5 млн записей. Запросы к ней иногда выполняються дольше чем положено (Slow_queries).
Есть еще база, запросы к которой используют много раз join. Практически всегда при этом создаеться временная таблица на hdd.
Пытался заставить создавать таблицы в памяти. Ставил

Код: Выделить всё

tmp_table_size = 512M
загрузка проца стала 50-60% а в phpmyadmin все таже ругань.
поставил

Код: Выделить всё

tmp_table_size = 1M
загрузка проца как показано выше при практически такой же ругани в phpmyadmin
весь конфиг сервера

Код: Выделить всё

skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 10M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
tmp_table_size = 1M
# Try number of CPU's*2 for thread_concurrency
#thread_concurrency = 2
#log-slow-queries

Код: Выделить всё

pkg_info | grep mysql 
mysql-client-5.0.51a Multithreaded SQL database (client)
mysql-server-5.0.51a Multithreaded SQL database (server)

Код: Выделить всё

vi /etc/make.conf
# MySQL
.if ${.CURDIR} == ${PORTSDIR}/databases/mysql50-server
BUILD_OPTIMIZED=yes
BUILD_STATIC=yes
WITH_CHARSET=cp1251
WITH_COLLATION=cp1251_bin
.endif

.if ${.CURDIR} == ${PORTSDIR}/databases/mysql50-client
BUILD_OPTIMIZED=yes
BUILD_STATIC=yes
WITH_CHARSET=cp1251
WITH_COLLATION=cp1251_bin
.endif
Тип таблиц - MyISAM.
В конфиге пробовал играть значениями практически всех параметров, но либо принципиальных изменений небыло, либо я их не заметил.
Я понимаю, что скорее всего проблема в том, что запросы к MySQL неоптимальны. Но запросы изменить я не могу. Что еще можно попробовать чтобы увеличить производительность или снизить использование ресурсов процессора?

Аватара пользователя
LMik
капитан
Сообщения: 1852
Зарегистрирован: 2007-07-17 9:14:39
Откуда: МО
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение LMik » 2008-03-09 10:48:54

Хы, так загрузка проца - показатель что все работы в памяти ведутся и мускулу это нравится (с винта гораздо медленнее читать/писать), и он может развернуться нормально и быстро отдавать ответы. Временную таблицу верни в память.
BSD... Join the dark side.
Виpус детям не игpушка, не товаpищ и не дpуг!

Аватара пользователя
LMik
капитан
Сообщения: 1852
Зарегистрирован: 2007-07-17 9:14:39
Откуда: МО
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение LMik » 2008-03-09 10:51:16

в thread-варианте
это что значит?

Код: Выделить всё

PTHREAD_LIBS=-lthr >> /etc/make.conf
Последний раз редактировалось LMik 2008-03-09 10:54:15, всего редактировалось 1 раз.
BSD... Join the dark side.
Виpус детям не игpушка, не товаpищ и не дpуг!

Аватара пользователя
LMik
капитан
Сообщения: 1852
Зарегистрирован: 2007-07-17 9:14:39
Откуда: МО
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение LMik » 2008-03-09 10:52:37

Код: Выделить всё

cat /boot/loader.conf
autoboot_delay="1"
kern.dfldsiz="2764M"            # Set the initial data size limit
kern.maxdsiz="2764M"            # Set the max data size
kern.maxssiz="128M"     # Set the max stack size
Это чтобы мускул смог скушать хотябы 2.5 гига рамы.
BSD... Join the dark side.
Виpус детям не игpушка, не товаpищ и не дpуг!

Аватара пользователя
LMik
капитан
Сообщения: 1852
Зарегистрирован: 2007-07-17 9:14:39
Откуда: МО
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение LMik » 2008-03-09 10:53:48

Код: Выделить всё

skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 10M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
tmp_table_size = 1M
# Try number of CPU's*2 for thread_concurrency
#thread_concurrency = 2
#log-slow-queries
Давай ему больше буферов, что за мизер такой с таким количеством памяти?

При запросах смотри

Код: Выделить всё

systat -v 1 
, на сколько там винт загружен?
BSD... Join the dark side.
Виpус детям не игpушка, не товаpищ и не дpуг!

Аватара пользователя
serge
майор
Сообщения: 2133
Зарегистрирован: 2006-07-30 15:34:14
Откуда: Саратов
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение serge » 2008-03-09 14:17:40

LMik писал(а):

Код: Выделить всё

cat /boot/loader.conf
autoboot_delay="1"
kern.dfldsiz="2764M"            # Set the initial data size limit
kern.maxdsiz="2764M"            # Set the max data size
kern.maxssiz="128M"     # Set the max stack size
Это чтобы мускул смог скушать хотябы 2.5 гига рамы.
Памяти всего 1 гиг :oops:

Аватара пользователя
serge
майор
Сообщения: 2133
Зарегистрирован: 2006-07-30 15:34:14
Откуда: Саратов
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение serge » 2008-03-09 14:20:48

LMik писал(а):Хы, так загрузка проца - показатель что все работы в памяти ведутся и мускулу это нравится (с винта гораздо медленнее читать/писать), и он может развернуться нормально и быстро отдавать ответы. Временную таблицу верни в память.
Я не против, но нагрузка на БД мизерная (~10 запросов в сек). Наблюдал как работает mysql на нагрузке в несколько раз выше и проц при этом загружал на 2-5%. Вот это и вызывает недоумение.
Тут дело в неоптимальных запросах, имхо. Бороться с источником проблемы возможности нет, вот и пытаюсь как то это сгладить на стороне сервера.

Аватара пользователя
serge
майор
Сообщения: 2133
Зарегистрирован: 2006-07-30 15:34:14
Откуда: Саратов
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение serge » 2008-03-09 14:23:37

LMik писал(а):
в thread-варианте
это что значит?

Код: Выделить всё

PTHREAD_LIBS=-lthr >> /etc/make.conf
Уже думал в эту строну. Теперь точно попробую.
З.Ы. Как это отразиться на производительности с учетом 1 ядра в процессоре? Почему-то мне показалось что это все хорошо для SMP серверов.

Аватара пользователя
LMik
капитан
Сообщения: 1852
Зарегистрирован: 2007-07-17 9:14:39
Откуда: МО
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение LMik » 2008-03-09 14:43:51

serge писал(а):
LMik писал(а):
в thread-варианте
это что значит?

Код: Выделить всё

PTHREAD_LIBS=-lthr >> /etc/make.conf
Уже думал в эту строну. Теперь точно попробую.
З.Ы. Как это отразиться на производительности с учетом 1 ядра в процессоре? Почему-то мне показалось что это все хорошо для SMP серверов.
Вобще поставь 7 релиз уже. Там все шустрее работает. Про буферы уже сказал - больше делай, он тормозит именно поэтому.
libthr pthr и прочие либы занимаются распределением нитей процессов по 1+ ядрам.
BSD... Join the dark side.
Виpус детям не игpушка, не товаpищ и не дpуг!

Аватара пользователя
serge
майор
Сообщения: 2133
Зарегистрирован: 2006-07-30 15:34:14
Откуда: Саратов
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение serge » 2008-03-09 18:32:15

LMik писал(а):При запросах смотри Код: Выделить всёsystat -v 1 , на сколько там винт загружен?
От 0 до 5%. Т.е. практически незагружен. Там еще к тому же gmirror стоит.

ProFTP
проходил мимо

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение ProFTP » 2008-03-10 13:55:08

а что использует мусю сайт или форум или какая-то программа??
если боты будут что-то вносить то она и будет грузиться или если будет очень много выносить данных...

буфер я так понимаю 64 метра... :roll:

Аватара пользователя
serge
майор
Сообщения: 2133
Зарегистрирован: 2006-07-30 15:34:14
Откуда: Саратов
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение serge » 2008-03-10 15:30:01

ProFTP писал(а):а что использует мусю сайт или форум или какая-то программа??
если боты будут что-то вносить то она и будет грузиться или если будет очень много выносить данных...
Сайт. Точнее даже mysql обслуживает маленький хостинг.
З.Ы. Чуть позже отпишусь о результатах прошедшей ночи оптимизации. Продвижки есть :)

Аватара пользователя
LMik
капитан
Сообщения: 1852
Зарегистрирован: 2007-07-17 9:14:39
Откуда: МО
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение LMik » 2008-03-10 16:39:09

serge писал(а):
ProFTP писал(а):а что использует мусю сайт или форум или какая-то программа??
если боты будут что-то вносить то она и будет грузиться или если будет очень много выносить данных...
Сайт. Точнее даже mysql обслуживает маленький хостинг.
З.Ы. Чуть позже отпишусь о результатах прошедшей ночи оптимизации. Продвижки есть :)
:) Буферы буферы, и ещё раз буферы :)
BSD... Join the dark side.
Виpус детям не игpушка, не товаpищ и не дpуг!

Аватара пользователя
voider
лейтенант
Сообщения: 830
Зарегистрирован: 2008-02-21 20:35:03
Откуда: msk

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение voider » 2008-03-12 2:07:08

Код: Выделить всё

[mysqld]
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=64M
log-bin
server-id=1

[safe_mysqld]
err-log=/var/log/mysqld.log
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

это пойдет для 2 - и больше памяти

Аватара пользователя
voider
лейтенант
Сообщения: 830
Зарегистрирован: 2008-02-21 20:35:03
Откуда: msk

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение voider » 2008-03-12 2:11:39

Код: Выделить всё

# Try number of CPU's*2 for thread_concurrency =2*2
thread_concurrency = 4
так как утебя машина двухпроцесорная ставь 4 цифру

Gloft
лейтенант
Сообщения: 645
Зарегистрирован: 2008-03-09 11:32:12
Откуда: Москва

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение Gloft » 2008-03-16 12:02:32

вот пару статеек
по оптимизации
http://sqlinfo.ru/articles/optimisation/

Аватара пользователя
serge
майор
Сообщения: 2133
Зарегистрирован: 2006-07-30 15:34:14
Откуда: Саратов
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение serge » 2008-03-16 20:25:48

Ага, читал. Много инета потратил на оптимизацию :) Но, имхо, четкой инструкции нету получаеться, или не выкладывает ее никто. Пришлось самому анализировать собранный материал и методом проб и ошибок менять параметры сервера.
З.Ы. Чето никак сам не отпишусь о проделанной работе :)

Аватара пользователя
Morty
ст. лейтенант
Сообщения: 1370
Зарегистрирован: 2007-07-17 23:25:12

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение Morty » 2008-03-17 14:37:24

а в чём разница

Код: Выделить всё

PTHREAD_LIBS=-lthr >> /etc/make.conf
и так

Код: Выделить всё

WITH_LINUXTHREADS=yes >> /etc/make.conf
я по последней собирал

Аватара пользователя
LMik
капитан
Сообщения: 1852
Зарегистрирован: 2007-07-17 9:14:39
Откуда: МО
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение LMik » 2008-03-17 19:14:35

Morty писал(а):а в чём разница

Код: Выделить всё

PTHREAD_LIBS=-lthr >> /etc/make.conf
и так

Код: Выделить всё

WITH_LINUXTHREADS=yes >> /etc/make.conf
я по последней собирал
Это разные либы поточности.
BSD... Join the dark side.
Виpус детям не игpушка, не товаpищ и не дpуг!

Аватара пользователя
dikens3
подполковник
Сообщения: 4856
Зарегистрирован: 2006-09-06 16:24:08
Откуда: Нижний Новгород
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение dikens3 » 2008-03-17 22:29:05

LMik писал(а):Это разные либы поточности.
И какая из них точнее?
Лучше установить FreeBSD, чем потратить 30 лет на Linux'ы и выяснить какой из них хуже.

Аватара пользователя
LMik
капитан
Сообщения: 1852
Зарегистрирован: 2007-07-17 9:14:39
Откуда: МО
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение LMik » 2008-03-17 22:35:20

dikens3 писал(а):
LMik писал(а):Это разные либы поточности.
И какая из них точнее?
от слова поток.
BSD... Join the dark side.
Виpус детям не игpушка, не товаpищ и не дpуг!

Аватара пользователя
Alex Keda
стреляли...
Сообщения: 35462
Зарегистрирован: 2004-10-18 14:25:19
Откуда: Made in USSR
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение Alex Keda » 2008-03-18 0:46:01

dikens3 писал(а):
LMik писал(а):Это разные либы поточности.
И какая из них точнее?
:lol: :lol: :lol: :lol: :lol: :lol: :lol: :lol: :lol: :lol: :lol: :lol: :lol: :lol: :lol: :lol: :lol: :lol:
Убей их всех! Бог потом рассортирует...

Аватара пользователя
Alex Keda
стреляли...
Сообщения: 35462
Зарегистрирован: 2004-10-18 14:25:19
Откуда: Made in USSR
Контактная информация:

Re: Как оптимизировать настройку MySQL?

Непрочитанное сообщение Alex Keda » 2008-03-18 0:46:56

LMik писал(а):
dikens3 писал(а):
LMik писал(а):Это разные либы поточности.
И какая из них точнее?
от слова поток.
AC/DC?
Вольтаж? =))
Убей их всех! Бог потом рассортирует...