Оптимизация MySQL

Есть табличка с данными. строк в ней очень много, порядка миллиона. сразу скажу, что в этом плане ничего не поделать, нужны все данные сразу, уменьшить размер нельзя. есть скрипт, результатом работы которого является наполнение другой таблицы. в определенный момент содержимое второй таблицы сливается в первую с помощью запроса типа INSERT .. SELECT. этот запрос отрабатывает относительно долго. ну оно и понятно, данных много. но самое обидное что в этот момент загрузка проца поднимается до 100%. блокировка сервера на 15-30 секунд мне не нравится. нельзя ли как нибудь сказать мускулю чтоб например отрабатывал это объединение в фоновом режиме?

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

Ну пали конфиг сервака хотябы для начала.........
И настройки мускуля

Перемещай данные кусками через интервалы времени.

Без конфигов и таблицы, твой вопрос вообще не имеет смысла.

webboxxx писал:

в этот момент загрузка проца поднимается до 100%. блокировка сервера на 15-30 секунд мне не нравится

Что-то я не пойму, ну загрузка процессора у тебя 100% временами (причем наверняка не процессора даже а одного из ядер), при чем тут блокировка сервера-то? Или ты блокировку базы имел ввиду? Так есть же штатные функции мускула типа INSERT DELAYED, как раз для таких случаев.

на серьёзных запросах на нескольких ядрах можно в top'е увидеть и 100, и 200, и даже 300% cpu usage, и это не есть проблема для сервера.

если виснет сервер на 10-30 сек, то проблема не в cpu usage а в iowait.

покажи конфиг мускула
При инсертах большого количества строк надо серьезно увеличивать выделение памяти под индексы. Помоему key_buffer_size.
Я не знаю, поможет ли это при INSERT .... SELECT ....
Но когда надо было из дампа залить 4Гб базу - то очень сильно помогало. Если поставить не 8мб по умолчанию, а хотя бы 128

webboxxx: ты что ты сейчас спросил, это аналогично как в автофоруме написать "завожу машину, она гремит. Что с ней ?"

P.S. 90% что проблема в селекте

понял, сейчас расскажу все детально, покажу конфиг.

Soft-Com: сервер сам по себе не виснет, я немного не правильно выразился. если быть более точным - то 1) idle в top показывает 0.0%, 2) в этот момент зависают все остальные запросы к базе, например из админки скрипта, таким образом админка не открывается так как ждет ответа от мускуля. а так как админка не открывается, то у пользователей создается впечатление что сервер висит.
это так и должно быть? мне просто казалось что 0.0% idle есть совсем не гуд.

shahfil: DELAYED не катит, в мане написано: DELAYED is ignored with INSERT ... SELECT

вот. и еще напомните плз, как под ФриБСД глянуть конфиг железа на серваке? тут еще дело в том, что сервак уже достаточно старенький, там помоему даже проц еще одноядерный. может быть вообще хотеть от этого железа то, чего я от него хочу совершенно напрасно.

вот конфиг мускуля. конфиг сервера нужен? что имеется ввиду - конфигурация апача или что?

Код:

back log     50 basedir    /usr/local/ bdb cache size    8,388,600 bdb home    /var/db/mysql/ bdb log buffer size    131,072 bdb logdir     bdb max lock    10,000 bdb shared data    OFF bdb tmpdir    /var/tmp/ binlog cache size    32,768 bulk insert buffer size    8,388,608 character set client    utf8 (Глобальное значение)    latin1 character set connection    utf8 (Глобальное значение)    latin1 character set database    latin1 character set results    utf8 (Глобальное значение)    latin1 character set server    latin1 character set system    utf8 character sets dir    /usr/local/share/mysql/charsets/ collation connection    utf8_unicode_ci (Глобальное значение)    latin1_swedish_ci collation database    latin1_swedish_ci collation server    latin1_swedish_ci concurrent insert    ON connect timeout    5 datadir    /var/db/mysql/ date format    %Y-%m-%d datetime format    %Y-%m-%d %H:%i:%s default week format    0 delay key write    ON delayed insert limit    100 delayed insert timeout    300 delayed queue size    1,000 expire logs days    0 flush    OFF flush time    86,400 ft boolean syntax    + -><()~*:""&| ft max word len    84 ft min word len    4 ft query expansion limit    20 ft stopword file    (built-in) group concat max len    1,024 have archive    NO have bdb    YES have blackhole engine    NO have compress    YES have crypt    YES have csv    NO have example engine    NO have geometry    YES have innodb    YES have isam    NO have merge engine    YES have ndbcluster    NO have openssl    NO have query cache    YES have raid    NO have rtree keys    YES have symlink    YES init connect     init file     init slave     innodb additional mem pool size    1,048,576 innodb autoextend increment    8 innodb buffer pool awe mem mb    0 innodb buffer pool size    8,388,608 innodb data file path    ibdata1:10M:autoextend innodb data home dir     innodb fast shutdown    ON innodb file io threads    4 innodb file per table    OFF innodb flush log at trx commit    1 innodb flush method     innodb force recovery    0 innodb lock wait timeout    50 innodb locks unsafe for binlog    OFF innodb log arch dir     innodb log archive    OFF innodb log buffer size    1,048,576 innodb log file size    5,242,880 innodb log files in group    2 innodb log group home dir    ./ innodb max dirty pages pct    90 innodb max purge lag    0 innodb mirrored log groups    1 innodb open files    300 innodb table locks    ON innodb thread concurrency    8 interactive timeout    600 join buffer size    131,072 key buffer size    134,217,728 key cache age threshold    300 key cache block size    1,024 key cache division limit    100 language    /usr/local/share/mysql/english/ large files support    ON lc time names    en_US license    GPL local infile    ON locked in memory    OFF log    OFF log bin    OFF log error     log slave updates    OFF log slow queries    OFF log update    OFF log warnings    1 long query time    10 low priority updates    OFF lower case file system    OFF lower case table names    0 max allowed packet    1,047,552 max binlog cache size    4,294,967,295 max binlog size    1,073,741,824 max connect errors    1,000 max connections    1,024 max delayed threads    20 max error count    64 max heap table size    16,777,216 max insert delayed threads    20 max join size    16,777,216 max length for sort data    1,024 max prepared stmt count    16,382 max relay log size    0 max seeks for key    4,294,967,295 max sort length    1,024 max tmp tables    32 max user connections    1,000 max write lock count    4,294,967,295 myisam data pointer size    4 myisam max extra sort file size    2,147,483,648 myisam max sort file size    2,147,483,647 myisam recover options    DEFAULT myisam repair threads    1 myisam sort buffer size    16,777,216 myisam stats method    nulls_unequal net buffer length    16,384 net read timeout    30 net retry count    1,000,000 net write timeout    60 new    OFF old passwords    OFF open files limit    11,095 pid file    /var/db/mysql/MC101196.pid port    3,306 preload buffer size    32,768 prepared stmt count    0 protocol version    10 query alloc block size    8,192 query cache limit    1,048,576 query cache min res unit    4,096 query cache size    67,108,864 query cache type    ON query cache wlock invalidate    OFF query prealloc size    8,192 range alloc block size    2,048 read buffer size    1,044,480 read only    OFF read rnd buffer size    262,144 relay log purge    ON relay log space limit    0 rpl recovery rank    0 secure auth    OFF server id    0 skip external locking    ON skip networking    OFF skip show database    OFF slave net timeout    3,600 slave transaction retries    0 slow launch time    2 socket    /tmp/mysql.sock sort buffer size    1,048,568 sql mode     sql notes    ON sql warnings    ON storage engine    MyISAM sync binlog    0 sync frm    ON sync replication    0 sync replication slave id    0 sync replication timeout    0 system time zone    MSK table cache    256 table type    MyISAM thread cache size    8 thread stack    196,608 time format    %H:%i:%s time zone    SYSTEM tmp table size    33,554,432 tmpdir     transaction alloc block size    8,192 transaction prealloc size    4,096 tx isolation    REPEATABLE-READ version    4.1.22 version bdb    Sleepycat Software: Berkeley DB 4.1.24: (November 3, 2006) version comment    FreeBSD port: mysql-server-4.1.22 version compile machine    i386 version compile os    portbld-freebsd5.2.1 wait timeout    300

Конфиг в топку, запрос лучше полный покажи

да чо запрос, запрос обычный, INSERT table1 (f1,f2) SELECT f1,f2 FROM table2, куда уж проще
я сейчас попробовал решить проблему более очевидными способами - в нескольких местах скрипта даю мускулю "отдыхать" небольшой паузой. засчет этого скрипт стал сливать данные более мелкими порциями, ну и загрузка уменьшилась. так как скрипты работают в фоне - этого думаю будет достаточно.

вообще хуевая тема с такими таблицами работать. особенно в режиме тяжелого r/w.

у тебя идет большая перестройка индексов наверно. плюс перемещение больших объемов с одного места на другое.

я бы оптимайзил хранилище. разнес бы таблицы эти по разным винтам. плюс индекс куда нить еще поместил бы.

а объем таблицы 1 и 2 скажи ? И сколько памяти на серваке? Может просто имеет смысл добавить памяти? тогда система затолкает обе таблицы в RAM и все будет в шеколаде.

Как вариант "INSERT DELAYED INTO table1 (f1,f2) SELECT f1,f2 FROM table2"

запрос

Код:

INSERT table1 (f1,f2) SELECT f1,f2 FROM table2

выполняется как одна транзакция, а данных много - вроде так.
если попробовать скриптом копировать построчно (n транзакций) - должно помочь

Pentarh: памяти гиг. объем таблицы 200мб, чуть больше миллиона записей. вторая таблица 100-200к обычно в момент объединения. да, еще очень важный момент забыл, в первой таблице есть уникальный индекс, ради которого соб-сно и нужно хранить весь объем. фильтрация данных занимает видимо бОльшую часть времени ну да ладно, я придумал сейчас еще один вариант (хотя думал что перепробовал уже все), попробую - может больше и не надо ничего мудрить.

Stek: говорю ж, DELAYED is ignored with INSERT ... SELECT (с) Manual

Dim82: то есть ты предлагаешь вместо одного запроса подряд выполнить 100,000 ? что-то мне подсказывает, что так лучше не будет

ПОстрочно можно с обменными операциями улезть в дикую засаду. По теме:
1) индексы, правильно пентарх грил - скорее всего с оптимизацией у тебя засада.
3) попробуй ИноДБ таблицы. Там транзакционная модель и построчная блокировка. На деле это означает что попасть на блок очень сложно не вынимая базы целиком каждым запросом.
2) логическая оптимизация: не знаю что именно у тебя там. Если в аське объяснишь подробнее - дам готовый рецепт. Сходу - нафиг из одной таблицы в другую тащить все данные, если можно допустим создать третью таблицу и туда сваливать лишь нужные id? Если каждый селект требует всех данных... а не кешировать - ли такие результаты? ну и ещё много чего.

вместо 1 - 100000 это бред. Это 100% убьёт сервер.

кстати, подумываю, уж не сменить ли сервак этот.. старый он уже, знаю что переплачиваю, потому как устарело железо уже. Intel(R) Pentium(R) 4 CPU 2.80GHz, памяти 1гиг, трафа терабайт в месяц, фул-менеджед, 24/7 саппорт. $235. что сейчас за эти деньги я могу арендовать?

А если LOW_PRIORITY вместо DELAYED ?

По сути тебе надо просто ручками взять и запустить часть запроса с выборкой, посмотреть сколько она выполняется. Если быстро, то значит проблемма с инсертом.

Т.е. для начала все таки определить, что тормозит.

У гиби можешь квадру взять за 250. Гиби, он конечно распиздяй ещё тот (и суппорт у него такой - же), но серваки, единожды полученые и настроенные работают хорошо и недорого

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

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

Цитата:

то есть ты предлагаешь вместо одного запроса подряд выполнить 100,000 ? что-то мне подсказывает, что так лучше не будет

да, наверное сервер устанет

Подсчитать к-во строк >> разбить на 10 (к примеру) >> выполнить за 10 раз.

как вариант написал... может и третью таблицу добавить как выше Sirgey писал

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

Sirgey писал:

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

ты может быть сам хорошо разбираешься в администрировании. а для меня все что выходит за рамки php и mysql - темный лес. а к платному админу по пустякам обращаться жаба душит.