Ошибка при конвертировании MyISAM в InnoDB.

[Buldozer]

Ошибка при конвертировании MyISAM в InnoDB.

Пытаюсь конвертировать таблицы из MyISAM в InnoDB, получаю ошибку: - "ERROR 1030 (HY000) at line 24: Got error -1 from storage engine".
Подскажите в чем может быть причина?

 

[Krishna]

Надо смотреть логи субд - конкретно сообщения механизма innodb

 

[Buldozer]

mysql> alter table accepted_pages engine=InnoDB;
ERROR 1025 (HY000): Error on rename of './test_bd/#sql-26d3_a0' to './test_bd/accepted_pages' (errno: -1)

в логе следующее:

InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.
070530 18:22:22 InnoDB: Error: table `test_bd/accepted_pages` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html

доку на эту тему читал - не фига не понятно
в my.cnf на тему innodb вот это:

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_file_io_threads = 4
innodb_force_recovery=1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

объясните пожалуйста, что не так?

-~{}~ 30.05.07 19:09:

версия mysql 5.0 в доке вроде как указано, что для mysql 4.0 и выше вообще что-то для innodb конфигурировать не обязательно - должно так работать.

 

[Апельсин]

дык, вам же в логах все ясно говорят:

InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user.

 

[Buldozer]

сразу столько много непонятных слов с этим innodb, что глаза разбежались.
закоментировал innodb_force_recovery=1 - пошло дело.

Подскажите еще в сторону каких параметров конфигурации смотреть, что бы увеличить скорость работы базы в обмен на "понижение надежности"?

-~{}~ 31.05.07 01:31:

а то вот перешел на инндодб в надежде избавиться тормозов от блокировок таблиц... а как тормозило в этом плате так и тормозит

 

[Апельсин]

никаких волшебных опций типа increase_innodb_speed=1 нет.
Единственое что можно поставить innodb_locks_unsafe_for_binlog=1 и то только если не используется репликация, чтобы отключить next-key locking в InnoDB.

Почему вы считаете что проблема именно в блокировках? Оптимизированы ли у вас запросы? Используется ли везде индексы?

Посмотрите вывод SHOW INNODB STATUS. Какие у вас активные транзакции, какие заблокированы, каких локов на какие записи они ждут и т.д.

 

[AnToXa]

innodb_flush_log_at_trx_commit = 1 поменять на 0? вроде как раз то что нужно, скорость в обмен на надежность. это одна из возможностей конечно.

 

[Buldozer]

>innodb_flush_log_at_trx_commit
>nnodb_locks_unsafe_for_binlog

буду пробовать

>Почему вы считаете что проблема именно в блокировках?
есть все необходимые индексы и прочее... база очень долго и успешно работало, но как только большинство таблиц перевалило за 10-20 млн записей, show processlist начал показывать сплошные locked

Сейчас перевел таблицы-монстры на innodb, locked`ов нет, но работает как-то странно... запросы которые раньше выполнялись по 2 сек, сейчас висят по минуте. Более того, такое впечатление, что периодически часть накопленных данных сбрасывается из некоторых таблиц. И работает странно, то летает, то тормозит.

>SHOW INNODB STATUS

=====================================
070601 10:20:03 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 22 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 28233110, signal count 24500681
--Thread 1142466912 has waited at ./../include/btr0btr.ic line 28 for 1.00 seconds the semaphore:
S-lock on RW-latch at 0x2aabaf587510 created in file buf0buf.c line 497
a writer (thread id 1142466912) has reserved it in mode  exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr0cur.c line 424
Last time write locked in file buf0buf.c line 1735
--Thread 1150392672 has waited at btr0cur.c line 424 for 0.00 seconds the semaphore:
S-lock on RW-latch at 0x2aabac2dc158 created in file buf0buf.c line 497
a writer (thread id 1150392672) has reserved it in mode  exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr0pcur.c line 247
Last time write locked in file buf0buf.c line 1735
Mutex spin waits 0, rounds 668867755, OS waits 14513310
RW-shared spins 23150885, OS waits 11258471; RW-excl spins 1749979, OS waits 657035
------------
TRANSACTIONS
------------
Trx id counter 0 4727177
Purge done for trx's n:o < 0 4721406 undo n:o < 0 0
History list length 931
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 4727151, not started, process no 8873, OS thread id 1149335904
MySQL thread id 19360, query id 24687203 localhost admin
---TRANSACTION 0 0, not started, process no 8873, OS thread id 1148279136
MySQL thread id 19299, query id 24687389 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 4727155, not started, process no 8873, OS thread id 1146693984
MySQL thread id 19031, query id 24687388 127.0.0.1 admin_xap2
---TRANSACTION 0 4699961, not started, process no 8873, OS thread id 1149864288
MySQL thread id 4643, query id 24505755 89.208.40.152 admin_xap2
---TRANSACTION 0 4727175, COMMITTED IN MEMORY, process no 8873, OS thread id 1147222368 committing, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
, undo log entries 1
MySQL thread id 19363, query id 24687386 localhost admin end
UPDATE `users` SET ksap = ksap  -600 WHERE `id` = 347 LIMIT 1
---TRANSACTION 0 4727173, COMMITTED IN MEMORY, process no 8873, OS thread id 1141938528 committing, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
, undo log entries 1
MySQL thread id 19364, query id 24687385 localhost admin end
UPDATE `pages_updated` SET `page_update_date` = 1180678803, occupied = occupied-1 WHERE page_id = 189075788
---TRANSACTION 0 4727171, COMMITTED IN MEMORY, process no 8873, OS thread id 1147750752 committing, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
, undo log entries 1
MySQL thread id 19204, query id 24687346 127.0.0.1 admin_xap2 end
update pages_updated set page_update_date = 1180678802 where page_id = 180362384
---TRANSACTION 0 4727166, COMMITTED IN MEMORY, process no 8873, OS thread id 1148807520 committing, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
, undo log entries 1
MySQL thread id 17983, query id 24687316 127.0.0.1 admin_xap2 end
update pages_pr set status = 0, pr_updated_date = 1180678802 where page_id = 188142050
---TRANSACTION 0 4727044, ACTIVE 1 sec, process no 8873, OS thread id 1142466912 fetching rows, thread declared inside InnoDB 30
mysql tables in use 1, locked 0
MySQL thread id 19362, query id 24687093 localhost admin Sending data
select count(*) from complete_links_alt
Trx read view will not see trx with id >= 0 4727045, sees < 0 4721404
---TRANSACTION 0 4726864, ACTIVE 2 sec, process no 8873, OS thread id 1143523680 starting index read, thread declared inside InnoDB 157
mysql tables in use 3, locked 0
, holds adaptive hash latch
MySQL thread id 19357, query id 24686231 localhost admin Copying to tmp table
SELECT complete_links_alt.link_id, spider_list.status
                FROM accepted_pages, complete_links_alt, spider_list
                WHERE complete_links_alt.page_id = accepted_pages.page_id
                AND spider_list.link_id = complete_links_alt.link_id
                AND accepted_pages.site_id  = '2749'
                ORDER BY complete_links_alt.date DESC
Trx read view will not see trx with id >= 0 4726865, sees < 0 4721404
---TRANSACTION 0 4725633, ACTIVE 37 sec, process no 8873, OS thread id 1151449440 fetching rows, thread declared inside InnoDB 480
mysql tables in use 2, locked 0
MySQL thread id 4709, query id 24672012 81.176.68.154 admin_xap2 Sending data
select accepted_pages.page_url from accepted_pages, pages_updated where accepted_pages.page_id = pages_updated.page_id and pages_updated.occupied = 0 and pages_updated.page_update_date >= 1171989194 and accepted_pages.site_id = 1891
Trx read view will not see trx with id >= 0 4725634, sees < 0 4721404
---TRANSACTION 0 4724863, ACTIVE 57 sec, process no 8873, OS thread id 1155148128 starting index read, thread declared inside InnoDB 353
mysql tables in use 2, locked 0
MySQL thread id 19297, query id 24667639 localhost admin Sending data
SELECT count(complete_links_alt.link_id) FROM `complete_links_alt`, `accepted_pages` WHERE complete_links_alt.page_id = accepted_pages.page_id AND accepted_pages.site_id = '3418'
Trx read view will not see trx with id >= 0 4724864, sees < 0 4721404
---TRANSACTION 0 4721404, ACTIVE 201 sec, process no 8873, OS thread id 1150392672 fetching rows, thread declared inside InnoDB 470
mysql tables in use 1, locked 0
MySQL thread id 19223, query id 24640282 localhost admin Sending data
SELECT * FROM `xaps_history` WHERE `reklam` = 3756 AND data = 1180673859
Trx read view will not see trx with id >= 0 4721405, sees < 0 4708741
--------
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: doing file i/o (read thread) ev set
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 55, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 1; buffer pool: 0
34400782 OS file reads, 2938496 OS file writes, 799225 OS fsyncs
4 pending preads, 0 pending pwrites
226.58 reads/s, 48478 avg bytes/read, 8.64 writes/s, 7.82 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 95, free list len 15187, seg size 15283,
610187 inserts, 592195 merged recs, 116431 merges
Hash table size 4425293, used cells 3656368, node heap has 11429 buffer(s)
14930.37 hash searches/s, 7256.17 non-hash searches/s
---
LOG
---
Log sequence number 9 4252083386
Log flushed up to   9 4252082430
Last checkpoint at  9 4251754083
1 pending log writes, 0 pending chkp writes
734511 log i/o's done, 7.64 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2428597088; in additional pool allocated 12117504
Buffer pool size   131072
Free buffers       0
Database pages     119643
Modified db pages  884
Pending reads 102
Pending writes: LRU 0, flush list 0, single page 0
Pages read 77462820, created 19040, written 2799975
670.65 reads/s, 0.00 creates/s, 0.91 writes/s
Buffer pool hit rate 997 / 1000
--------------
ROW OPERATIONS
--------------
9 queries inside InnoDB, 0 queries in queue
6 read views open inside InnoDB
Main thread process no. 8873, id 1140881760, state: sleeping
Number of rows inserted 298808, updated 451274, deleted 2468367, read 7843250309
0.68 inserts/s, 6.82 updates/s, 2.41 deletes/s, 936042.82 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

уф... как бы разобраться =-0

-~{}~ 01.06.07 10:59:

поставил
innodb_locks_unsafe_for_binlog = 1

а innodb_flush_log_at_trx_commit = 0
у меня уже был там... - как я понял из документации, риск потери данных в результате этой манипуляции может быть только в тех случах если обваливается мускуль или сама железяка?
Т.е. вот у меня есть подозрения что часть данных потерялась три часа назад. При этом аптайм мускла уже сутки... следовательно в этом параметре проблемы нет?

-~{}~ 01.06.07 11:02:

mysql> show processlist;
+-----+------------+-----------------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------------+-----------------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 111 | admin | localhost | master_db | Query | 83 | Sending data | select count(*) from accepted_pages |
+-----+------------+-----------------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

на MyISAM этот запрос выполнялся 1 секунду

 

[AnToXa]

на MyISAM этот запрос выполнялся 1 секунду

логично, т.к. в myisam этот запрос вообще модет не сканировать таблицу, а innodb приходится, бо транзакции есть.
как вариант, если часто нужно получать количество записей - можно попробовать денормализовать его куда-нибудь и обновлять ручками, а в 5.1 кажется есть триггеры для такого.

 

[Апельсин]

Buldozer,так это ожидаемо что выборки работают медленее из-за версионности. Ну а SELECT COUNT(*) вообще по разному работает для MyISAM и для InnoDB т.к. для MyISAM это значение хранится,а для InnoDB вычисляется каждый раз. Если точно значение не принципиально то можно брать кол-во строк из SHOW TABLE STATUS. Там приблизительно кол-во строк показывается.

Поставьте достаточно большие логи транзакций, по дефолту они 5Мб идут, если мне не изменяет память.

 

[Buldozer]

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

>выборки работают медленее из-за версионности
мда... причем работает на несколько порядков медленнее, сплошные засады. Походу придется к MyISAM возвращаться... вешаться вешлось но в совокупности работало быстрее

>Поставьте достаточно большие логи транзакций
сейчас у меня вот так:
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M

размер базы в сыром виде 32 гб, имеет смысл увеличить эти параметры?

 

[Апельсин]

а сколько у вас innodb_buffer_pool_size ?

кстати вы транзакции используете или в autocommit mode?

 

[Buldozer]

вообщем в my.cnf для innodb у меня сейчас следующее
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:4096M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 0
innodb_locks_unsafe_for_binlog = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

>кстати вы транзакции используете или в autocommit mode?
откровенно говоря даже не понимаю о чем речь?

 

[Апельсин]

Кстати проверьте все-таки индексы, вот например ме странно что такой запрос выполняется больше 200 секунд:

---TRANSACTION 0 4721404, ACTIVE 201 sec, process no 8873, OS thread id 1150392672 fetching rows, thread declared inside InnoDB 470
mysql tables in use 1, locked 0
MySQL thread id 19223, query id 24640282 localhost admin Sending data
SELECT * FROM `xaps_history` WHERE `reklam` = 3756 AND data = 1180673859
Trx read view will not see trx with id >= 0 4721405, sees < 0 4708741

 

[Buldozer]

как я понимаю autocommit mode - это для MyISAM, а для InnoDB - это всегда транзакции?

 

[Апельсин]

не совсем ..
в autocommit mode каждый отдельный запрос будет транзакцией.

Я имела ввиду используете ли вы:

begin;
...
...
commit;

 

[Buldozer]

нет,такого не использую

-~{}~ 01.06.07 15:17:

а для выборки с like '%...%' на innodb, тоже какакие-то особенности есть?

 

[Апельсин]

да нет, так же как и для MyISAM будет сканироваться вся таблица.

 

[Buldozer]

а у MyISAM перед InnoDB в подсчете count() преимущество, только если идет подсчет всех записей в таблице?
Т.е. select count(*) where... - "равноценно"? я верно понимаю?

 

[Апельсин]

да, верно