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

[gogi]

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

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

[Хостинг 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]

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

[serge]

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

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

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]

Хы, так загрузка проца - показатель что все работы в памяти ведутся и мускулу это нравится (с винта гораздо медленнее читать/писать), и он может развернуться нормально и быстро отдавать ответы. Временную таблицу верни в память.

[LMik]

в thread-варианте

это что значит?

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

PTHREAD_LIBS=-lthr >> /etc/make.conf

[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 гига рамы.

[LMik]

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

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 

, на сколько там винт загружен?

[serge]

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

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 гиг

[serge]

Хы, так загрузка проца - показатель что все работы в памяти ведутся и мускулу это нравится (с винта гораздо медленнее читать/писать), и он может развернуться нормально и быстро отдавать ответы. Временную таблицу верни в память.

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

[serge]

в thread-варианте

это что значит?

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

PTHREAD_LIBS=-lthr >> /etc/make.conf

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

[LMik]

в thread-варианте

это что значит?

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

PTHREAD_LIBS=-lthr >> /etc/make.conf

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

Вобще поставь 7 релиз уже. Там все шустрее работает. Про буферы уже сказал - больше делай, он тормозит именно поэтому.
libthr pthr и прочие либы занимаются распределением нитей процессов по 1+ ядрам.

[serge]

При запросах смотри Код: Выделить всёsystat -v 1 , на сколько там винт загружен?

От 0 до 5%. Т.е. практически незагружен. Там еще к тому же gmirror стоит.

[ProFTP]

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

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

[serge]

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

Сайт. Точнее даже mysql обслуживает маленький хостинг.
З.Ы. Чуть позже отпишусь о результатах прошедшей ночи оптимизации. Продвижки есть

[LMik]

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

Сайт. Точнее даже mysql обслуживает маленький хостинг.
З.Ы. Чуть позже отпишусь о результатах прошедшей ночи оптимизации. Продвижки есть

Буферы буферы, и ещё раз буферы

[voider]

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

[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]

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

# Try number of CPU's*2 for thread_concurrency =2*2
thread_concurrency = 4

так как утебя машина двухпроцесорная ставь 4 цифру

[Gloft]

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

[serge]

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

[Morty]

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

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

PTHREAD_LIBS=-lthr >> /etc/make.conf

и так

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

WITH_LINUXTHREADS=yes >> /etc/make.conf

я по последней собирал

[LMik]

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

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

PTHREAD_LIBS=-lthr >> /etc/make.conf

и так

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

WITH_LINUXTHREADS=yes >> /etc/make.conf

я по последней собирал

Это разные либы поточности.

[dikens3]

Это разные либы поточности.

И какая из них точнее?

[LMik]

Это разные либы поточности.

И какая из них точнее?

от слова поток.

[Alex Keda]

Это разные либы поточности.

И какая из них точнее?

[Alex Keda]

Это разные либы поточности.

И какая из них точнее?

от слова поток.

AC/DC?
Вольтаж? )