Форум пользователей MySQL

Aquariuscrimea · 15.03.2012 12:03:12

Переодически, раз в неделю в течении нескольких часов происходит что-то странное с Mysql.
Он начинает очень сильно кушать винчестер.
В приложении скрин iotop.
Нагрузка не постоянно 120 но около того. При нормальное работе Total disc read вообще по 0/0 обычно.
Лечиться перезагрузкой mysql. Через какое время (полчаса -час) это повторяется и потом неделю нет.
После перезагрузки все отлично сразу.

В логе ошибок mysql пусто.
Посещаемость никак не растет в эти моменты (смотрел access.log апача)
В slow-log Тоже ничего нет, кроме запросов которые начали тормозить в момент этой мега нагрузки. Без нагрузки они выполняются за 0.5 секунды.

Памяти на сервер 8 гигабайт. Винчестеры обычные в raid 1.

Вот основной кусок my.cnf.

net_buffer_length = 2K

table_cache = 1536

tmp_table_size=32M

ft_min_word_len = 3

thread_stack = 256K 

key_buffer_size = 256M 

max_allowed_packet = 10M 

read_buffer_size = 2M 

read_rnd_buffer_size = 8M 

query_cache_size = 32M 

join_buffer_size = 2M 

max_heap_table_size = 32M 

sort_buffer_size = 2M 

myisam_sort_buffer_size = 64M 

thread_cache_size=12 

max_connections = 170 

table_definition_cache = 1536

[mysqldump]

quick

quote-names

max_allowed_packet    = 16M

[mysql]

#no-auto-rehash    # faster start of mysql but no tab completition

[isamchk]

key_buffer        = 16M

Прикрепленные файлы:

Безымянный.png, Размер: 232,846 байт, Скачано: 553

rgbeast · 15.03.2012 14:50:02

Что в это время показывает sql-запрос SHOW FULL PROCESSLIST?
Также проверьте диск по информации smart.

Aquariuscrimea · 15.03.2012 16:18:28

спасибо, попробую.
Smart уже смотрел, все в норме кроме 2 у одного и 3 multi zone error rate у второго. Но за неделю не выросло.

Aquariuscrimea · 19.03.2012 11:58:53

вот опять сегодня такая беда
вот show proccesslist.
Влезло не все, в putty все не поместилось.

Я так понимаю что проблема в том что у нас myisam, несколько основных таблиц блокируется и происходит "Зависание"? Но запускал буквально пару недель назад tuning-primer.sh - он писал что Your table locking seems to be fine.

| 1012822 | site_name_base | localhost                        | site_name | Query   |  137 | Locked | UPDATE `firms` SET `informers_count_bonus` = '12212' WHERE `idcompany` = '6894'                      |

| 1012823 | site_name_base | localhost                        | site_name | Query   |  137 | Locked | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012824 | site_name_base | localhost                        | site_name | Query   |  135 | Locked | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012825 | site_name_base | localhost                        | site_name | Query   |  132 | Locked | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012826 | site_name_base | localhost                        | site_name | Query   |  132 | Locked | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

`google_ |

| 1012830 | site_name_base | localhost                        | site_name | Query   |  124 | Locked | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012831 | site_name_base | localhost                        | site_name | Query   |  124 | Locked | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012832 | site_name_base | localhost                        | site_name | Query   |  124 | Locked | SELECT `idcompany`, `favicon`

                                FROM `firms`

Отредактированно Aquariuscrimea (19.03.2012 12:03:28)

rgbeast · 19.03.2012 12:05:00

Все приведенные запросы в состоянии Locked. Это странно - точно нет больше исполняемых запросов?

Aquariuscrimea · 19.03.2012 12:22:09

есть, но они все в слипе. И tuning primer уже советует переходить на Innodb

вот более полный список

| 1012842 | site_name_base | localhost                        | site_name | Query   |  149 | Locked         | SELECT `idcompany`, `fullname`, `tariff`, `domain`, `domain_type`, `favicon`, `header_pic`, `google_ |

| 1012843 | site_name_base | localhost                        | site_name | Query   |  149 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012844 | site_name_base | localhost                        | site_name | Query   |  148 | Locked         | SELECT SQL_CALC_FOUND_ROWS stat.id , stat.is_pay,  DATE_FORMAT(stat.dateadd, '%d-%m-%Y') AS date_add |

| 1012845 | site_name_base | localhost                        | site_name | Sleep   |  142 |                | NULL                                                                                                 |

| 1012846 | site_name_base | localhost                        | site_name | Query   |  141 | Locked         | SELECT `firms_bonuses`.*

                                FROM `firms_bonuses`

                LEFT JOIN `firms` ON (`firms`.`i |

| 1012847 | site_name_base | localhost                        | site_name | Query   |  141 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012849 | site_name_base | localhost                        | site_name | Query   |  140 | Locked         | SELECT `areas`.`en`, `areas`.`id`, `domain`

        FROM `firms`

        LEFT JOIN `goods` USING ( |

| 1012851 | site_name_base | localhost                        | site_name | Sleep   |  138 |                | NULL                                                                                                 |

| 1012852 | site_name_base | localhost                        | site_name | Sleep   |  137 |                | NULL                                                                                                 |

| 1012853 | site_name_base | localhost                        | site_name | Sleep   |  137 |                | NULL                                                                                                 |

| 1012854 | site_name_base | localhost                        | site_name | Sleep   |  135 |                | NULL                                                                                                 |

| 1012855 | site_name_base | localhost                        | site_name | Sleep   |  132 |                | NULL                                                                                                 |

| 1012856 | site_name_base | localhost                        | site_name | Query   |  128 | Locked         | SELECT `areas`.`en`, `areas`.`id`, `domain`

        FROM `firms`

        LEFT JOIN `goods` USING ( |

| 1012857 | site_name_base | localhost                        | site_name | Sleep   |  128 |                | NULL                                                                                                 |

| 1012859 | site_name_base | localhost                        | site_name | Sleep   |  125 |                | NULL                                                                                                 |

| 1012860 | site_name_base | localhost                        | site_name | Sleep   |  125 |                | NULL                                                                                                 |

| 1012861 | site_name_base | localhost                        | site_name | Sleep   |  124 |                | NULL                                                                                                 |

| 1012862 | site_name_base | localhost                        | site_name | Sleep   |  124 |                | NULL                                                                                                 |

| 1012863 | site_name_base | localhost                        | site_name | Query   |  123 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012865 | site_name_base | localhost                        | site_name | Query   |  116 | Locked         | SELECT `idcompany`, `fullname`, `tariff`, `domain`, `domain_type`, `favicon`, `header_pic`, `google_ |

| 1012866 | site_name_base | localhost                        | site_name | Sleep   |  115 |                | NULL                                                                                                 |

| 1012867 | site_name_base | ip46-125.245.80.crimea.com:57517 | site_name | Query   |  108 | Locked         | SELECT *, `date_add`, DATE_FORMAT(`date_add`, '%d.%m.%Y') AS date_reg, `areas`.`phone_code` FROM `fi |

| 1012868 | site_name_base | localhost                        | site_name | Query   |  107 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012870 | site_name_base | localhost                        | site_name | Query   |  101 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012871 | site_name_base | localhost                        | site_name | Query   |  101 | Locked         | SELECT * FROM users  WHERE MD5(CONCAT(`login`, 'most secret word in the world', `pass`)) = '053e09e8 |

| 1012872 | site_name_base | localhost                        | site_name | Query   |   97 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012873 | site_name_base | localhost                        | site_name | Query   |   89 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012875 | site_name_base | localhost                        | site_name | Query   |   89 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012876 | site_name_base | localhost                        | site_name | Query   |   89 | Locked         | SELECT

                                        `goods`.`id`,

                                        `goods`.`cat`,

                                        `goods`.`pp`,

                                        `goods`.`pp_keywords`,

                 |

| 1012877 | site_name_base | localhost                        | site_name | Query   |   89 | Locked         | SELECT `areas`.`en`, `areas`.`id`, `domain`

        FROM `firms`

        LEFT JOIN `goods` USING ( |

| 1012878 | site_name_base | localhost                        | site_name | Query   |   89 | Locked         | SELECT SQL_CALC_FOUND_ROWS stat.id , stat.is_pay,  DATE_FORMAT(stat.dateadd, '%d-%m-%Y') AS date_add |

| 1012879 | site_name_base | localhost                        | site_name | Query   |   88 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012880 | site_name_base | localhost                        | site_name | Query   |   88 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012881 | site_name_base | localhost                        | site_name | Query   |   83 | Locked         | SELECT `informers_count`, `informers_count_bonus`

                                                FROM `firms`

                                                WHERE `idcompany` = '15 |

| 1012883 | site_name_base | localhost                        | site_name | Query   |   82 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012884 | site_name_base | localhost                        | site_name | Query   |   80 | Locked         | SELECT

                firms_blocks.*,

                `areas`.`area`,

                `firms`.`phon |

| 1012885 | site_name_base | localhost                        | site_name | Query   |   81 | Locked         | SELECT

                        firms.idcompany,

                        firms.sellerkod,

                        firms.fullname,

                        firms.brands,

                        firms.si |

| 1012886 | site_name_base | localhost                        | site_name | Query   |   79 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012887 | site_name_base | localhost                        | site_name | Query   |   75 | Locked         | SELECT `informers_count`, `informers_count_bonus`

                                                FROM `firms`

                                                WHERE `idcompany` = '65 |

| 1012888 | site_name_base | localhost                        | site_name | Query   |   75 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012890 | site_name_base | localhost                        | site_name | Sleep   |   72 |                | NULL                                                                                                 |

| 1012891 | site_name_base | localhost                        | site_name | Query   |   71 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012892 | site_name_base | localhost                        | site_name | Query   |   70 | Locked         | SELECT `areas`.`en`, `areas`.`id`, `domain`

        FROM `firms`

        LEFT JOIN `goods` USING ( |

| 1012893 | site_name_base | localhost                        | site_name | Sleep   |   68 |                | NULL                                                                                                 |

| 1012894 | site_name_base | localhost                        | site_name | Sleep   |   64 |                | NULL                                                                                                 |

| 1012895 | site_name_base | localhost                        | site_name | Query   |   63 | Locked         | SELECT *

                                        FROM `all_goods`

                                        WHERE `id` IN (90714,129852,88190,90185,79765,107846,94472,118 |

| 1012896 | site_name_base | localhost                        | site_name | Query   |   63 | Locked         | SELECT * FROM users  WHERE `login` = 'nsipakova@ukr.net' OR `email` = 'nsipakova@ukr.net'            |

| 1012897 | site_name_base | localhost                        | site_name | Query   |   63 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012898 | site_name_base | localhost                        | site_name | Sleep   |   62 |                | NULL                                                                                                 |

| 1012899 | site_name_base | localhost                        | site_name | Query   |   59 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012900 | site_name_base | localhost                        | site_name | Sleep   |   58 |                | NULL                                                                                                 |

| 1012901 | site_name_base | localhost                        | site_name | Sleep   |   57 |                | NULL                                                                                                 |

| 1012902 | site_name_base | localhost                        | site_name | Query   |   56 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012903 | site_name_base | localhost                        | site_name | Sleep   |   55 |                | NULL                                                                                                 |

| 1012904 | root         | localhost                        | NULL    | Query   |    0 | NULL           | SHOW  PROCESSLIST                                                                                    |

| 1012905 | site_name_base | localhost                        | site_name | Sleep   |   51 |                | NULL                                                                                                 |

| 1012906 | site_name_base | localhost                        | site_name | Query   |   46 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012910 | site_name_base | localhost                        | site_name | Query   |   47 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012911 | site_name_base | localhost                        | site_name | Sleep   |   46 |                | NULL                                                                                                 |

| 1012912 | site_name_base | localhost                        | site_name | Query   |   45 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012913 | site_name_base | localhost                        | site_name | Query   |   43 | Locked         | SELECT `areas`.`en`, `areas`.`id`, `domain`

        FROM `firms`

        LEFT JOIN `goods` USING ( |

| 1012914 | site_name_base | localhost                        | site_name | Query   |   41 | Locked         | SELECT `idcompany`, `fullname`, `tariff`, `domain`, `domain_type`, `favicon`, `header_pic`, `google_ |

| 1012915 | site_name_base | localhost                        | site_name | Query   |   41 | Locked         | SELECT `areas`.`en`, `areas`.`id`, `domain`

        FROM `firms`

        LEFT JOIN `goods` USING ( |

| 1012916 | site_name_base | localhost                        | site_name | Sleep   |   40 |                | NULL                                                                                                 |

| 1012917 | site_name_base | localhost                        | site_name | Query   |   38 | Locked         | SELECT `informers_count`, `informers_count_bonus`

                                                FROM `firms`

                                                WHERE `idcompany` = '17 |

| 1012918 | site_name_base | localhost                        | site_name | Query   |   38 | Locked         | SELECT `areas`.`en`, `areas`.`id`, `domain`

        FROM `firms`

        LEFT JOIN `goods` USING ( |

| 1012919 | site_name_base | localhost                        | site_name | Query   |   36 | Locked         | SELECT `areas`.`en`, `areas`.`id`, `domain`

        FROM `firms`

        LEFT JOIN `goods` USING ( |

| 1012921 | site_name_base | localhost                        | site_name | Sleep   |   32 |                | NULL                                                                                                 |

| 1012922 | site_name_base | localhost                        | site_name | Query   |   31 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012923 | site_name_base | localhost                        | site_name | Sleep   |   30 |                | NULL                                                                                                 |

| 1012924 | site_name_base | localhost                        | site_name | Query   |   28 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012925 | site_name_base | localhost                        | site_name | Query   |   29 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012926 | site_name_base | localhost                        | site_name | Query   |   28 | Locked         | SELECT SQL_CALC_FOUND_ROWS stat.id , stat.is_pay,  DATE_FORMAT(stat.dateadd, '%d-%m-%Y') AS date_add |

| 1012927 | site_name_base | localhost                        | site_name | Query   |   28 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012928 | site_name_base | localhost                        | site_name | Query   |   27 | Locked         | SELECT `areas`.`en`, `areas`.`id`, `domain`

        FROM `firms`

        LEFT JOIN `goods` USING ( |

| 1012929 | site_name_base | localhost                        | site_name | Query   |   26 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012931 | site_name_base | localhost                        | site_name | Query   |   25 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012933 | site_name_base | localhost                        | site_name | Query   |   25 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012934 | site_name_base | localhost                        | site_name | Sleep   |   24 |                | NULL                                                                                                 |

| 1012936 | site_name_base | localhost                        | site_name | Query   |   21 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012938 | site_name_base | localhost                        | site_name | Query   |   20 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012939 | site_name_base | localhost                        | site_name | Query   |   19 | Locked         | SELECT

                                DISTINCT(`firms`.`idcompany`), `firms`.`fullname`, `firms`.`city`, `firms`.`area_id`, ` |

| 1012940 | site_name_base | localhost                        | site_name | Query   |   19 | Locked         | SELECT *

                                        FROM `all_goods`

                                        WHERE `id` IN (48715,82447,126394,124762,96522,94766,58478,795 |

| 1012942 | site_name_base | localhost                        | site_name | Query   |   18 | Locked         | SELECT `idcompany`, `fullname`, `tariff`, `domain`, `domain_type`, `favicon`, `header_pic`, `google_ |

| 1012943 | site_name_base | localhost                        | site_name | Query   |   16 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012944 | site_name_base | localhost                        | site_name | Sleep   |   12 |                | NULL                                                                                                 |

| 1012945 | site_name_base | localhost                        | site_name | Sleep   |    7 |                | NULL                                                                                                 |

| 1012946 | site_name_base | localhost                        | site_name | Query   |    4 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012947 | site_name_base | localhost                        | site_name | Query   |    3 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012948 | site_name_base | localhost                        | site_name | Query   |    2 | Locked         | SELECT `idcompany`, `favicon`

                                FROM `firms`

                        |

| 1012950 | site_name_base | localhost                        | site_name | Sleep   |    1 |                | NULL                                                                                                 |

| 1012951 | site_name_base | localhost                        | site_name | Sleep   |    1 |                | NULL                                                                                                 |

+---------+--------------+----------------------------------+---------+---------+------+----------------+------------------------------------------------------------------------------------------------------+

132 rows in set (0.00 sec)

rgbeast · 19.03.2012 14:54:16

Ситуация не очень понятная. Какой-то запрос должен быть медленным, чтобы его все ждали. В списке не видно. С производительностью диска все нормально?

Aquariuscrimea · 19.03.2012 15:00:39

Вроде да.
А как проверить? параметры смарт в норме.

медленный запрос не попадает в slow-log потому что я раньше бутаю mysql чем он успевает закончить, возможно так?

а как-то можно вывести не на экран команду show full procceslist а в файл?

Aquariuscrimea · 19.03.2012 16:26:39

в общем сейчас такая же беда повторилась. Сайт тупил 15 мин, пока я до него добежал, он как раз перестал тупить. И в slow-query-log попала кверя которая делалась все 15 минут.
Кверя это выполняется кроном раз в 10 минут. Но если отдельное выполнять эту кверю она делается за полсекунды. И крон стоит каждые 10 минут и проблемы возникают только несколько раз в неделю.
Все таки дело в винте? Он хочет записать результаты запроса на винт и не может и из-за этого тупит?

Отредактированно Aquariuscrimea (19.03.2012 16:34:58)

Aquariuscrimea · 19.03.2012 16:36:57

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

Отредактированно Aquariuscrimea (19.03.2012 16:37:06)

rgbeast · 19.03.2012 18:53:45

Если это сложная выборка, ее можно попробовать разбить на несколько поэтапных выборок с фиксированным числом записей.

Aquariuscrimea · 19.03.2012 19:58:34

с выборкой разбираемся, но вообще по логике вопрос.
Почему нельзя как-то ограничить аппетиты mysql и конкретного запроса? Ведь могут быть действительно сложные и долгие запросы и плюс может разработчик забыл индекс поставить, почем должны страдать все сайта на сервере от этого? Как умереть его аппетиты? Пробовал при запуске крона использовать ionice но он скорее всего влияет только на php

rgbeast · 19.03.2012 20:06:58

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

Aquariuscrimea · 20.03.2012 10:51:50

но на shared Хостингах когда там 50-100 сайтов там как-то эту проблему решают, но там с помощью системы запускают Mysql в ограниченном "пространстве"? Но когда виртуальные машины то там понятно, а когда одна машина, Mysql Общий для всех то как они там каждого пользователя ограничивают?

vasya · 20.03.2012 11:21:40

Когда одна машина и Mysql общий для всех, то владельцу сайта приходит письмо следующего содержания.
"ваш сайт создает слишком большую нагрузку. Или вы это исправите, или мы отключим вас на фиг"

Aquariuscrimea · 20.03.2012 11:27:37

т.е. никаких вариантов ограничить аппетит одного из запроса mysql нет?

rgbeast · 20.03.2012 13:49:17

Aquariuscrimea написал:
т.е. никаких вариантов ограничить аппетит одного из запроса mysql нет?

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

Форум пользователей MySQL

#1 15.03.2012 12:03:12

Большая нагрузка на диск от mysql

#2 15.03.2012 14:50:02

Re: Большая нагрузка на диск от mysql

#3 15.03.2012 16:18:28

Re: Большая нагрузка на диск от mysql

#4 19.03.2012 11:58:53

Re: Большая нагрузка на диск от mysql

#5 19.03.2012 12:05:00

Re: Большая нагрузка на диск от mysql

#6 19.03.2012 12:22:09

Re: Большая нагрузка на диск от mysql

#7 19.03.2012 14:54:16

Re: Большая нагрузка на диск от mysql

#8 19.03.2012 15:00:39

Re: Большая нагрузка на диск от mysql

#9 19.03.2012 16:26:39

Re: Большая нагрузка на диск от mysql

#10 19.03.2012 16:36:57

Re: Большая нагрузка на диск от mysql

#11 19.03.2012 18:53:45

Re: Большая нагрузка на диск от mysql

#12 19.03.2012 19:58:34

Re: Большая нагрузка на диск от mysql

#13 19.03.2012 20:06:58

Re: Большая нагрузка на диск от mysql

#14 20.03.2012 10:51:50

Re: Большая нагрузка на диск от mysql

#15 20.03.2012 11:21:40

Re: Большая нагрузка на диск от mysql

#16 20.03.2012 11:27:37

Re: Большая нагрузка на диск от mysql

#17 20.03.2012 13:49:17

Re: Большая нагрузка на диск от mysql

Aquariuscrimea написал:

Board footer