Мало памяти для MySQL

37

dmakcent

3 декабря 2014, 04:05

2041

nginx + php-fpm + mysql на vds с 2Гб памяти. Базы на MyISAM, около 800 таблиц. Мускулу постоянно нехватает памяти для кеша, первоначально выделял 128Мб под query_cache, но через определенное время весь кеш заполняется и сыпятся lowmem_prunes. Выделял больше памяти - только дольше заполняется кеш, и опять сыпятся lowmem_prunes. Сейчас query_cache=512Мб, но ситуацию это не изменило.

Посоветуйте конфиг, в т.ч. размер query_cache&

my.cfg

skip-host-cache

skip-name-resolve

skip-networking

low-priority-updates

skip-external-locking



key_buffer_size         = 48M

sort_buffer_size        = 128K

thread_cache_size       = 25

max_connections         = 100

table_cache             = 2048

tmp_table_size          = 24M

max_heap_table_size     = 24M

query_cache_size        = 512M

myisam-recover-options  = BACKUP

innodb_buffer_pool_size = 6M

mysqltuner.pl

[--] Up for: 6d 8h 31m 14s (29M q [54.321 qps], 855K conn, TX: 91B, RX: 6B)

[--] Reads / Writes: 65% / 35%

[--] Total buffers: 606.0M global + 832.0K per thread (100 max threads)

[OK] Maximum possible memory usage: 687.2M (33% of installed RAM)

[OK] Slow queries: 0% (9/29M)

[OK] Highest usage of available connections: 17% (17/100)

[OK] Key buffer size / total MyISAM indexes: 48.0M/32.5M

[OK] Key buffer hit rate: 100.0% (1B cached / 17K reads)

[OK] Query cache efficiency: 77.7% (18M cached / 23M selects)

[!!] Query cache prunes per day: 4584

[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 434K sorts)

[!!] Joins performed without indexes: 14411

[!!] Temporary tables created on disk: 43% (379K on disk / 867K total)

[OK] Thread cache hit rate: 99% (17 created / 855K connections)

[OK] Table cache hit rate: 41% (1K open / 3K opened)

[OK] Open file limit used: 48% (2K/4K)

[OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)

[OK] InnoDB buffer pool / data size: 6.0M/416.0K

[OK] InnoDB log waits: 0

-------- Recommendations -----------------------------------------------------

General recommendations:

    Run OPTIMIZE TABLE to defragment tables for better performance

    Increasing the query_cache size over 128M may reduce performance

    Adjust your join queries to always utilize indexes

    When making adjustments, make tmp_table_size/max_heap_table_size equal

    Reduce your SELECT DISTINCT queries without LIMIT clauses

Variables to adjust:

    query_cache_size (> 512M) [see warning above]

    join_buffer_size (> 128.0K, or always use indexes with joins)

    tmp_table_size (> 24M)

    max_heap_table_size (> 24M)

status mysql

Variable_name   Value

Qcache_free_blocks      4346

Qcache_free_memory      62649240

Qcache_hits     18279496

Qcache_inserts  4989119

Qcache_lowmem_prunes    29133

Qcache_not_cached       249932

Qcache_queries_in_cache 187621

Qcache_total_blocks     380291

munin

165

Vin_cent

3 декабря 2014, 06:43

#1

0. Какая версия MySQL?

1. Оставь в покое (сделай 32Mb) query_cache_size, он тебе не поможет.

2. tmp_table_size и max_heap_table_size можешь синхронно увеличить, например до 64Mb

3. Не вижу путь к tmp. Надеюсь не на диске, а в памяти?

4. Есть запросы с JOIN без индексов. Нужно проставить. И сделай join_buffer_size=2Mb

1723

LEOnidUKG

3 декабря 2014, 06:44

#2

Вы считаете, что всегда должно хватать?

У вас сайты тормозят или в чём причина настройки?

✅ Мой Телеграм канал по SEO, оптимизации сайтов и серверов: https://t.me/leonidukgLIVE ✅ Качественное и рабочее размещение SEO статей СНГ и Бурж: https://getmanylinks.ru/

D

37

dmakcent

3 декабря 2014, 07:33

#3

Vin_cent:
0. Какая версия MySQL?

1. Оставь в покое (сделай 32Mb) query_cache_size, он тебе не поможет.

2. tmp_table_size и max_heap_table_size можешь синхронно увеличить, например до 64Mb

3. Не вижу путь к tmp. Надеюсь не на диске, а в памяти?

4. Есть запросы с JOIN без индексов. Нужно проставить. И сделай join_buffer_size=2Mb

1. 5.5.40

2. Увеличивал до 192Мб, не сказалось на tmp_table_size, поэтому уменьшил до 24Мб.

3. tmpdir=/tmp. Как прописать в памяти?

4. join_buffer_size тоже увеличивал до 2Мб, никак не сказалось.

LEOnidUKG:
Вы считаете, что всегда должно хватать?
У вас сайты тормозят или в чём причина настройки?

Считал, что должно. У многих lowmem_prunes проскакивают в малых количествах.

Сайты не тормозят, но хочу оптимально настроить мускул.

K5

209

kgtu5

3 декабря 2014, 07:40

#4

max_connections = 50

query_cache_size = 64M - больше не надо

query_cache_limit = 1M

join_buffer_size = 2M

tmp_table_size = 128M

max_heap_table_size = 128M

аська 45два48499два записки на работе (http://memoryhigh.ru) помогу с сайтом, удалю вирусы, настрою впс -> отзывы ТУТ (/ru/forum/836248) и ТАМ (http://www.maultalk.com/topic140187.html) !!!всегда проверяйте данные людей, которые сами пишут вам в аську или скайп!!!

1723

LEOnidUKG

3 декабря 2014, 07:42

#5

dmakcent:

Считал, что должно. У многих lowmem_prunes проскакивают в малых количествах.
Сайты не тормозят, но хочу оптимально настроить мускул.

Если ничего не тормозит, то вы играетесь с виртуальными циферками. Лучше займитесь продвижением сайтов и т.п.

165

Vin_cent

3 декабря 2014, 07:50

#6

dmakcent:
1. 5.5.40
2. Увеличивал до 192Мб, не сказалось на tmp_table_size, поэтому уменьшил до 24Мб.
3. tmpdir=/tmp. Как прописать в памяти?
4. join_buffer_size тоже увеличивал до 2Мб, никак не сказалось.

Считал, что должно. У многих lowmem_prunes проскакивают в малых количествах.
Сайты не тормозят, но хочу оптимально настроить мускул.

Что значит увеличивал - никак не сказалось? Надо всё в комплексе увеличить/уменьшить, и уж потом смотреть, сказалось или нет. Делай, как я говорю.

>> 3. tmpdir=/tmp. Как прописать в памяти?

Инструкция: http://www.fromdual.com/mysql-tmpdir-on-ram-disk

5. MySQL 5.5 MyISAM медленно работает. У меня, после перехода на 5.6, mysql (myisam) стал заметно быстрее работать. Почитайте: http://habrahabr.ru/post/242337/

1723

LEOnidUKG

3 декабря 2014, 08:09

#7

Ну как ещё вариант это поставить 5.6 мускуль, и врубить там memcached plugin

Главное чтобы таблицы были в innoDB

---------- Добавлено 03.12.2014 в 14:14 ----------

Но опять это всё нужно, если что-то тормозит и нагрузка жёсткие большая.

Геморрой размером с датское Фильтры в интернет магазине Падает memcached

N

419

netwind

3 декабря 2014, 08:23

#8

Прежде всего надо понять что это кеш результатов запросов. Он не такой как специально созданные кеши в cms и тд - очищается при любом пуке в базу. И кешировать пытается вообще все запросы, в том числе, которые наверняка не будут нужны в будущем.

Поэтому, например, 26 числа вы увеличивали размер и делали перезапуск, но на третьем графике число попаданий (синенькое) заметно не выросло. И хотя число запросов в кеше росло исправно, толку от них нет, только память занимают.

Можно считать, что и раньше все было нормально.

dmakcent:
Increasing the query_cache size over 128M may reduce performance

И эта пометка не просто так. При бОльших размерах кеша вероятны "странные зависания" связанные с очисткой кеша.

LEOnidUKG:
Ну как ещё вариант это поставить 5.6 мускуль, и врубить там memcached plugin
Главное чтобы таблицы были в innoDB

Так в этом случае memcached plugin собственно кешированием в памяти не занимается. Вообще непонятно зачем такой совет.

Кнопка вызова админа ()

Упрощение навигации для сайта CMS для потрала с Google советует с осторожностью

1723

LEOnidUKG

3 декабря 2014, 08:34

#9

netwind:

Так в этом случае memcached plugin собственно кешированием в памяти не занимается. Вообще непонятно зачем такой совет.

От делать нечего т.к. именно делать нечего ТС-у

D

37

dmakcent

3 декабря 2014, 12:44

#10

LEOnidUKG:
От делать нечего т.к. именно делать нечего ТС-у

Мускул работал при высоком значении кеша. И хоть он работал без сбоя, это не значит что эти настройки оптимальны для мускула. Я обратился за советом, что именно тут не так. Вы разбираетесь в этом деле, я тоже хочу разобраться.

Vin_cent:
>> 3. tmpdir=/tmp. Как прописать в памяти?
Инструкция: http://www.fromdual.com/mysql-tmpdir-on-ram-disk

Сделал как вы посоветовали.

Vin_cent:

5. MySQL 5.5 MyISAM медленно работает. У меня, после перехода на 5.6, mysql (myisam) стал заметно быстрее работать. Почитайте: http://habrahabr.ru/post/242337/

Хочу попробовать обновить.

key_buffer_size         = 48M
join_buffer_size        = 2M
sort_buffer_size        = 128K
thread_cache_size       = 25
max_connections         = 50
table_cache             = 2048
tmp_table_size          = 64M
max_heap_table_size     = 64M
query_cache_limit       = 1M
query_cache_size        = 64M
myisam-recover-options  = BACKUP

Google Updates - апдейты Продвижение нескольких брендов/регионов Борьба с ботами

Яндекс Вебмастер вынес товарные фиды в отдельный раздел

Зачем быть уникальным в мире, где все можно скопировать