|
Задавайте вопросы, мы ответим
Вы не зашли.
Здравствуйте.
Уже несколько раз замечал такую ситуацию, когда mysqladmin proc показыает около 50 процессов (а может и больше подсчетом не занимался) и все они в состоянии Locked. Сегодня повторилась такая же ситуация. Вот что мы имеем
| 12216268 | ih | xxx.xxx.xx.xx:34846 | o_tab | Query | 893 | Locked | select ID, NAME, WEIGHT, b.QUANTITY, i_price as finish_i_price, CAT_ID, virtu |
| 12325333 | ih | xxx.xxx.xx.xx:3860 | o_tab | Query | 2299 | Copying to tmp table | select i_quantity_price.*, items.name from i_quantity_price left join items on |
| 12362735 | client | хх.хх.хх.хх:63876 | o_tab | Query | 958 | Locked | update items set weight = "0.005", war="0", cat_id = "1148" where id = "6954" |
...................
................
.............
представлены они тут в такой же последовательности как и mysqladmin proc
хочу понять принцип блокировки.
Почему заблокированы все селекты? Тоесть если стоит Read LOCK то чтение то разрещено.
Есть предположение что выполнение запросов идет сверху вниз тогда выполняется
Copying to tmp table | select i_quantity_price.*, items.name from
Locked | update items set weight = "0.005", заблокирован потом что стоит Read LOCK
ну а все остальные запросы что идут после update items заблокированы потому что апдейт еще не выполнился. Но тогда почему самый первый селект заблокированый?
Неактивен
Вы идеально выбрали строчки, даже не прийдется говорить «скорее всего» 
Как Вы правильно сказали, табличка MyISAM оказалась заблокированной на чтение
в результате работы второго запроса (Copying to tmp table | select i_quant...).
Пока к этой табличке приходят другие запросы на чтение, все будет работать замечательно.
Но у Вас приходят не только запросы на чтение, но и запрос на изменение данных.
Запрос на изменение данных, разумеется, не может ничего сделать, пока табличка
заблокирована на чтение (т.к. ему надо заблокировать ее на запись), и поэтому он
начинает ждать завершения длинного запроса на чтение.
Далее приходит следующий запрос на чтение, который обнаруживает, что есть запрос
на запись (а в MyISAM запросы на запись по умолчанию более приоритетные, чем запросы
на чтение). В результате он начинает ждать, когда выполнится запрос на запись. Т.е.
они оба Locked, но ждут завершения разных запросов: третий — второго, а первый —
третьего.
Помочь ситуации можно разными способами:
1. Делать низкоприоритетные изменения (UPDATE LOW PRIORITY).
2. Делать высокоприоритетные выборки (SELECT HIGH_PRIORITY).
3. Преобразовать табличку в InnoDB (тогда вообще не будет блокировок на чтение).
4. Делать длинные SELECT-запросы на реплике, куда не приходят другие запросы.
Если Вы будете смотреть в сторону пункта 1, то можно включить глобально низкоприоритетные
обновления параметром low-priority-updates. Но осторожно с ним, при большом количестве
запросов на чтение, табличка может вообще никогда не отпустить блокировку, и, соответственно,
изменения никогда до нее не доберутся.
Неактивен
paulus написал:
Вы идеально выбрали строчки, даже не прийдется говорить «скорее всего»
ну дык! старался.
paulus написал:
Как Вы правильно сказали, табличка MyISAM оказалась заблокированной на чтение
в результате работы второго запроса (Copying to tmp table | select i_quant...).
Пока к этой табличке приходят другие запросы на чтение, все будет работать замечательно.
Но у Вас приходят не только запросы на чтение, но и запрос на изменение данных.
Запрос на изменение данных, разумеется, не может ничего сделать, пока табличка
заблокирована на чтение (т.к. ему надо заблокировать ее на запись), и поэтому он
начинает ждать завершения длинного запроса на чтение.
Далее приходит следующий запрос на чтение, который обнаруживает, что есть запрос
на запись (а в MyISAM запросы на запись по умолчанию более приоритетные, чем запросы
на чтение). В результате он начинает ждать, когда выполнится запрос на запись. Т.е.
они оба Locked, но ждут завершения разных запросов: третий — второго, а первый —
третьего.
Помочь ситуации можно разными способами:
1. Делать низкоприоритетные изменения (UPDATE LOW PRIORITY).
2. Делать высокоприоритетные выборки (SELECT HIGH_PRIORITY).
3. Преобразовать табличку в InnoDB (тогда вообще не будет блокировок на чтение).
4. Делать длинные SELECT-запросы на реплике, куда не приходят другие запросы.
Если Вы будете смотреть в сторону пункта 1, то можно включить глобально низкоприоритетные
обновления параметром low-priority-updates. Но осторожно с ним, при большом количестве
запросов на чтение, табличка может вообще никогда не отпустить блокировку, и, соответственно,
изменения никогда до нее не доберутся.
тоесть блокировки становятся в очередь и запросы после апдейта уже знают что следующая блокировка будет wite block?
а есть какая-то команда что бы посмотреть очередь блокировок?
Неактивен
Ну, на самом деле никаких очередей нету, есть блокировки и приоритеты.
Поэтому и посмотреть их нельзя. Ну, кроме как в show processlist.
Неактивен
У меня такая-же ситуация.
Причем на одном сервере висит 12 сайтов на платформе Битрикс с средней посещаемостью 2 000 хитов в сутки.
А на 2-м сервере висит то-же Битрикс но с посещаемостью в 20 000 хитов в сутки.
Оперативки на первом 2х512, а на 2-м 2х1024
Вот какая конфига MySQL на обоих серверах сейчас:
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 64M
key_buffer_size = 64M
max_allowed_packet = 15M
max_connections = 20
max_heap_table_size = 32M
myisam_sort_buffer_size = 64M
read_buffer_size = 64M
read_rnd_buffer_size = 64M
record_buffer = 4M
sort_buffer_size = 4M
table_cache = 256
thread_cache_size = 16K
thread_concurrency = 16
thread_stack = 64K
tmp_table_size = 256M
query_cache_limit = 16M
query_cache_size = 256M
query_cache_type = 1
optimizer_search_depth=0
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:50M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
innodb_buffer_pool_size = 128M
innodb_additional_mem_pool_size = 70M
innodb_log_buffer_size = 14M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 40
skip-federated
log-bin=mysql-bin
server-id = 1
[safe_mysqld]
log-error=/var/lib/mysql/mysqld.log
[mysqldump]
quick
quote-names
max_allowed_packet = 8M
[mysql]
#no-auto-rehash
[isamchk]
key_buffer = 16M
sort_buffer_size = 8M
read_buffer = 10M
write_buffer = 10M
[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
Что можете посоветовать чтобы база не подвисала?
Неактивен
Еще сейчас на одном из форумов вычитал:
Посмотрите статистику кэша: show status like 'qc%';
если значение Qcache_lowmem_prunes больше 0, значит каким-то запросам не хватает памяти в кэше.
Внес изменения сделав
query_cache_limit = 512M
query_cache_size = 512M
Выполнил
SHOW STATUS LIKE 'qc%';
Получил
Qcache_free_blocks = 4
Qcache_free_memory = 536544672
Qcache_hits = 147
Qcache_inserts = 206
Qcache_lowmem_prunes = 0
Qcache_not_cached = 508
Qcache_queries_in_cache = 150
Qcache_total_blocks = 363
У меня Qcache_lowmem_prunes так и остался 0... Почему?
Неактивен
По-моему при 20к запросах в сутки никаких проблем с нагрузкой быть не может
в принципе Ну только если битрикс занимается параллельно еще чем-то
--
По поводу вопросов: если таблички MyISAM, то надо увеличить key_buffer (например,
до 512 мегабайт); если InnoDB — innodb_buffer_pool_size (аналогично).
Ну и подумайте о innodb_flush_log_at_trx_commit = 0
--
Qcache_lowmem_prunes равный нулю — это, по Вашим же словам, хорошо — что Вас
смущает тогда?
P.S. Но 512 мегов из гигабайта на сервере отдавать под query cache я бы не стал... ну,
50 поставьте, ну, 100...
Неактивен
Я тут бороздя просторы интернета на своем космическом корабле набрел на интересную статью:
Анализ производительности базы данных MySQL
Так вот в статье говориться о весьма полезном скрипте: Скрипт "tuning primer" (Matt Mongomery).
Суть работы скрипта такова:
Скрипт надо запустить в shell'e из под root, он цепляется к базе, читает ее конфигурацию... читает логи и выдает свои рекомендации по оптимизации тех или иных параметров.
НО есть один маленький нюанс, автор заявляет, что более точные рекомендации скрипт может выдать если база работала без рестарта более 48 часов.
В принципе я использовал скрипт и при меньшем времени (просто у меня база больше 20 минут без зависания не работала), провозился 3 дня и теперь при все тех-же 20к. хитов и параллельном считывании и записи у меня база больше не подвисает.
Вот пример одной и рекомендации данного скрипта:
Shell написал:
----------------вырезано----------------
QUERY CACHE
Query cache is enabled
Current query_cache_size = 6 M
Current query_cache_used = 64 K
Current query_cache_limit = 6 M
Current Query cache Memory fill ratio = 1.04 %
Current query_cache_min_res_unit = 4 M
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
----------------вырезано----------------
TEMP TABLES
Current max_heap_table_size = 4.00 G
Current tmp_table_size = 4.00 G
Of 10885 temp tables, 37% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
----------------вырезано----------------
Неактивен
Угу, таких скриптов очень много; также это все можно смотреть лапками, очень
удобно
Неактивен
если бы знал сам что и где смотреть то давно бы уже решил вопрос, а тут хороший человек написал хороший скрипт.
Автору 5+
Неактивен
подскажите, а low priority - это влияет только на одну таблицу? т.е. если у меня вся работа идет с таблицей firms (и select и Update) то если я поставлю low priority для таблицы логов то это никак не повлияет на блокировку таблицы firms, верно?
Неактивен
А что значит "поставлю low priority для таблицы логов"?
Неактивен
т.е. у меня большая конкуренция на одной таблице, а я сделаю low priority для insert и Update у другой таблице. Это же никак не скажеться на загруженной?
Неактивен
Что-то от меня ускользает смысл написанного вами, что значит "сделаю low priority для insert и Update у таблицы"?
Я всегда считал, что low_priority write это вид явной блокировки, устанавливаемый с помощью команды lock table. А вы о чем?
Неактивен
Ну, можно же сделать INSERT LOW_PRIORITY — тогда SELECTы будут приоритетнее
вставки, а вставка будет ждать. Но на другие таблицы оно не будет влиять, естественно.
Неактивен
