очистка БД mysql

[BigBrother]

Есть два сервера бд mysql с настроенной репликацией master-master. У них есть бд в ~40GB, а в ней таблица innodb с ~100k записями. Встала потребность очистить таблицу от старых записей. После запуска запроса на очистку

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

DELETE st FROM tblname AS st
          INNER JOIN tbl_name1 AS nu
         ON nu.`unitid` = st.`unitId` AND
              (nu.`duration` != -1 OR st.`pDateTime` < NOW() - INTERVAL nu.`duration` DAY) ;
        ALTER TABLE tblname;

на одной бд, она запускает процесс для репликации на другую бд и этот процесс, типа зависает

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

mysql> show full processlist;
+----+-------------+------------+----------+-------------+------+-----------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User        | Host       | db       | Command     | Time | State                                                                 | Info                                                                                                                                                                                                          |
+----+-------------+------------+----------+-------------+------+-----------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | system user |            | NULL     | Connect     |  375 | Waiting for master to send event                                      | NULL                                                                                                                                                                                                          |
|  2 | system user |            | NULL     | Connect     |   73 | Has read all relay log; waiting for the slave I/O thread to update it | NULL                                                                                                                                                                                                          |
|  9 | replica     | srv:43997 | NULL     | Binlog Dump |  347 | Has sent all binlog to slave; waiting for binlog to be updated        | NULL                                                                                                                                                                                                          |
| 11 | root        | localhost  | db_name | Query       |  330 | Sending data                                |  запрос на удаление (см. ниже)  |
| 12 | root        | localhost  | NULL     | Query       |    0 | NULL                                                                  | show full processlist                                                                                                                                                                                         |
+----+-------------+------------+----------+-------------+------+-----------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

И значение time для процесса с id 11 постоянно растет. Доходило до 10 часов и ничего не менялось.
На второй сервер, данные через репликацию не поступают (позиция slave не меняется).
После этого запроса БД на которой он был запущен, перестает нормально останавливается и лог ошибок молчит.

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

[root@centosadmin data]# ../support-files/mysql.server stop
Shutting down MySQL...........................................................................
.....................................                         ................................................................................
...................................................                         ................................................................................
...................................................                         ................................................................................
...................................................                         ................................................................................
...................................................                         ...........................................................[ СБОЙ ]................................................................

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

mysql> SHOW ENGINE INNODB STATUS\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
111013 12:19:30 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 32 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2180, signal count 2178
Mutex spin waits 0, rounds 34450, OS waits 584
RW-shared spins 2811, OS waits 1206; RW-excl spins 485, OS waits 390
------------
TRANSACTIONS
------------
Trx id counter 0 119809
Purge done for trx's n:o < 0 119300 undo n:o < 0 0
History list length 8
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 13457, OS thread id 3030784912
MySQL thread id 21, query id 292 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 119808, ACTIVE 6909 sec, process no 13457, OS thread id 3031317392 updating or deleting, thread declared inside InnoDB 297
mysql tables in use 1, locked 1
63071 lock struct(s), heap size 4582720, 12127251 row lock(s), undo log entries 12064182
MySQL thread id 4, query id 137 localhost root Sending data
DELETE st FROM tblname AS st

          INNER JOIN tblname1 AS nu

         ON nu.`unitid` = st.`unitId` AND

              (nu.`duration` != -1 OR st.`pDateTime` < NOW() - INTERVAL nu.`duration` DAY)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
570032 OS file reads, 2184545 OS file writes, 209684 OS fsyncs
87.09 reads/s, 16472 avg bytes/read, 308.27 writes/s, 26.06 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 402, seg size 404,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, node heap has 16 buffer(s)
757.29 hash searches/s, 888.85 non-hash searches/s
---
LOG
---
Log sequence number 15 3327131048
Log flushed up to   15 3327005846
Last checkpoint at  15 3326987376
0 pending log writes, 0 pending chkp writes
75582 log i/o's done, 8.78 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 17576620; in additional pool allocated 901376
Dictionary memory allocated 28592
Buffer pool size   512
Free buffers       0
Database pages     217
Modified db pages  107
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 667865, created 54708, written 2360643
87.56 reads/s, 1.09 creates/s, 304.49 writes/s
Buffer pool hit rate 983 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 13457, id 2985933712, state: sleeping
Number of rows inserted 0, updated 0, deleted 12064181, read 12064182
0.00 inserts/s, 0.00 updates/s, 234.59 deletes/s, 234.59 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.13 sec)

После убиения процесса родителя (kill -9) сервера mysql, сервер бд запускается нормально, но htop показывает, что один из его потомков уже активно работает и использует CPU 20-40%, в то время как с бд никто не работает.

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

mysql> show full processlist;
+----+-------------+------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------+
| Id | User        | Host       | db   | Command     | Time | State                                                                 | Info                  |
+----+-------------+------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------+
|  1 | system user |            | NULL | Connect     |  245 | Waiting for master to send event                                      | NULL                  |
|  2 | system user |            | NULL | Connect     |   74 | Has read all relay log; waiting for the slave I/O thread to update it | NULL                  |
|  3 | replica     | srv:52590 | NULL | Binlog Dump |  193 | Has sent all binlog to slave; waiting for binlog to be updated        | NULL                  |
|  4 | root        | localhost  | NULL | Query       |    0 | NULL                                                                  | show full processlist |
+----+-------------+------------+------+-------------+------+-----------------------------------------------------------------------+---------------------

При этом в лог пишется

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

 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
InnoDB: Rolling back of trx id 0 119808 completed
111013 14:00:16  InnoDB: Rollback of non-prepared transactions completed

Если в этот момент запустить запрос очистки еще раз, то

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

mysql> \. del.sql
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Когда счетчик (см. выше) доходит до 100, сервер успокаивается. Этот процесс занимает около часа.

Как побороть эту проблему? Быть может улучшить (добавить логики или оптимизировать) запрос на очистку, например: удалять данные не за весь период, а за каждые пол года?
Нашел в инете http://blog.sjinks.pro/mysql/889-lock-wait...ng-transaction/ но это со стороны программирования, а можно ли со стороны сервера решить эту проблему?

[Ism]

Возможно сразу много записей mysql не может удалить. Может сделать скрипт удаляющий по одной. Лучше прогаммку. Тогда уж наверняка
Кроме того есть у mysql такая опция max_allowed_packet , но это для insert, возможно есть опция регулирующая удаление записей.
Также теоретически может влиять например малый кеш индексов , запросов или еще чего. Проверьте хватает ли серверу памяти

[KiWi]

И значение time для процесса с id 11 постоянно растет. Доходило до 10 часов и ничего не менялось.
На второй сервер, данные через репликацию не поступают (позиция slave не меняется).

"Не поступают" или "не выполняются"?
Если второе -- так и должно быть, ждите окончания запроса.

После убиения процесса родителя (kill -9) сервера mysql, сервер бд запускается нормально, но htop показывает, что один из его потомков уже активно работает и использует CPU 20-40%, в то время как с бд никто не работает.

Восстанавливаются InnoDB таблицы.

Как побороть эту проблему?

Не использовать kill -9 -- это НЕнормальная остановка, требующая восстановления.

Быть может улучшить (добавить логики или оптимизировать) запрос на очистку, например: удалять данные не за весь период, а за каждые пол года?
Нашел в инете http://blog.sjinks.pro/mysql/889-lock-wait...ng-transaction/ но это со стороны программирования, а можно ли со стороны сервера решить эту проблему?

Например, посмотреть EXPLAIN запроса? А ещё лучше -- не использовать INNER JOIN.

[BigBrother]

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

Подскажите пожалуйста, какими средствами языка sql, я могу указать, какую запись оставить в любом случае, даже если она подходит по условию на удаление? Например, вот запрос на очистку

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

delete tbl1.*
from tbl1
inner join tbl2 on tbl2.unitid = tbl1.unitid
where (tbl2.du != -1 and tbl1.ptime < NOW() - INTERVAL tbl2.du DAY and (tbl2.id=xx or tbl2.id=));

который работает корректно. Как в данном случае, я могу оставить 1-2-3 записи, которые соответствуют условию

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

tbl1.ptime < NOW() - INTERVAL tbl2.du DAY

которые должны удалится?? То есть, если по условию, должно удалится 5 записей, то как мне одну-две записи все равно оставить (самые последнии из этого списка). Так сказать, исключить их?

[sash-kan]

То есть, если по условию, должно удалится 5 записей, то как мне одну-две записи все равно оставить

http://dev.mysql.com/doc/refman/5.0/en/delete.html
delete … limit <count>

сначала вычисляете количество строк, подпадающих под критерий, затем передаёте команде delete нужное число·
в одну команду, насколько я понимаю, это засунуть нельзя (т.е., добавить sub-select в limit)·

[diesel]

в одну команду, насколько я понимаю, это засунуть нельзя (т.е., добавить sub-select в limit)·

можно, но не в mysql. как-то вот так, например:

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

delete from table where <something> and id not in (select id from table where <something> limit 3);

в mysql, разве что temporary table создать сначала для того что не нужно удалять.

[sash-kan]

в одну команду, насколько я понимаю, это засунуть нельзя (т.е., добавить sub-select в limit)·

можно, но не в mysql. как-то вот так, например:

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

delete from table where <something> and id not in (select id from table where <something> limit 3);

ну, если таким образом, то и в mysql должно прекрасно прокататить·

[diesel]

ну, если таким образом, то и в mysql должно прекрасно прокататить·

если мне не глючит память, то не прокатит, потому что в mysql в подзапросах для update/delete нельзя использовать ту же таблицу, из которой(в которой) удаляешь или апдейтишь.

[BigBrother]

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

И у кого есть опыт очистки бд (через стандартные запросы sql), когда два сервера mysql, работают в режиме репликации master-master, и с помощью веба, данных отдаются в режиме реал-тайм. Оба сервера должны быть включены, хоть база идентична, но на разные сервера приходят разные данные. Подскажите несколько идей, как в данном случае можно очистить бд.

ну, если таким образом, то и в mysql должно прекрасно прокататить·

если мне не глючит память, то не прокатит, потому что в mysql в подзапросах для update/delete нельзя использовать ту же таблицу, из которой(в которой) удаляешь или апдейтишь.

да, вы правы.
In MySQL, you can't modify the same table which you use in the SELECT part.
This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html

[KiWi]

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

http://blog.bsd-root.com/2011/05/mysql-row...ment-based.html