OPTIMIZE и пр.

[Sult]

OPTIMIZE и пр.

PHP + Mysql 4.0.20
железо нормальное, сервер особо не нагружен

в базе есть таблицы около 4 млн. записей (1 Гб) - ключевые слова для полнотекстового поиска,
скрипты работают быстро (LIKE естественно не используется, это я чтобы снять ряд встречных вопросов

таблицы обновляются каждый день и большими порциями
(при этом тормозится сильно сервер, но это уже другой вопрос)

естественно таблицы становятся "рыхлыми" на 1Гб примерно 150 Мб накладных расходов
пробовал запускать OPTIMIZE - работал час, конца процесса я так и не дождался
при этом таблица естественно лочится, да и сервер тормозит
так что такой вариант не приемлем

какие будут мысли по этому поводу?

насколько я знаю в фоновом режиме у MySQL подобных фич нет, может у 5 версии есть что либо?

1. таблицы из полей varchar. Насколько сильно поможет (и поможет ли) замена их на тип char ?
2. есть ли смысл переходить на другие базы (версии MySQL) данных? Если да, то какие?
3. м.быть какие нить другие предложения?

Спасибо.

 

[Апельсин]

В MySQL хоть вы используете MyISAM, хоть InnoDB если у вас удаляются данные то переодически необходимо запускать OPTIMIZE.

OPTIMIZE не самая быстрая операция, но у вас возможно небольшие размеры буфера(ов). Какой тип таблиц вы используете?

 

[Sult]

MyISAM
Есть смысл перейти на InnoDB ?
просто не было опыта работы с ними, вроде пишут, что хороши для больших объемов данных...

Вот параметры сервера базы (оперативная память сервера - 1Гб):
| back_log | 50
| bdb_cache_size | 20480
| bdb_log_buffer_size | 0
| bdb_home |
| bdb_max_lock | 10000
| bdb_logdir |
| bdb_shared_data | OFF
| bdb_tmpdir |
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (May 14, 2004)
| binlog_cache_size | 32768
| bulk_insert_buffer_size | 524288
| concurrent_insert | ON
| connect_timeout | 5
| delay_key_write | ON
| delayed_insert_limit | 1
| delayed_insert_timeout | 1
| delayed_queue_size | 1
| flush | OFF
| flush_time | 0
| ft_boolean_syntax | + -><()~*:""&|
| ft_min_word_len | 4
| ft_max_word_len | 254
| ft_max_word_len_for_sort | 20
| ft_stopword_file | (built-in)
| have_bdb | DISABLED
| have_crypt | YES
| have_innodb | DISABLED
| have_isam | YES
| have_raid | NO
| have_symlink | NO
| have_openssl | YES
| have_query_cache | YES
| init_file |
| innodb_additional_mem_pool_size | 1048576
| innodb_buffer_pool_size | 8388608
| innodb_data_file_path |
| innodb_data_home_dir |
| innodb_file_io_threads | 4
| innodb_force_recovery | 0
| innodb_thread_concurrency | 8
| innodb_flush_log_at_trx_commit | 1
| innodb_fast_shutdown | ON
| innodb_flush_method |
| innodb_lock_wait_timeout | 50
| innodb_log_arch_dir |
| innodb_log_archive | OFF
| innodb_log_buffer_size | 1048576
| innodb_log_file_size | 5242880
| innodb_log_files_in_group | 2
| innodb_log_group_home_dir |
| innodb_mirrored_log_groups | 1
| innodb_max_dirty_pages_pct | 90
| interactive_timeout | 120
| join_buffer_size | 131072
| key_buffer_size | 25165824
| large_files_support | ON
| license | GPL
| local_infile | ON
| log | OFF
| log_update | OFF
| log_bin | OFF
| log_slave_updates | OFF
| log_slow_queries | OFF
| log_warnings | ON
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_file_system | OFF
| lower_case_table_names | 0
| max_allowed_packet | 25164800
| max_binlog_cache_size | 4294967295
| max_binlog_size | 1073741824
| max_connections | 200
| max_connect_errors | 10
| max_delayed_threads | 0
| max_insert_delayed_threads | 0
| max_heap_table_size | 16777216
| max_join_size | 4294967295
| max_relay_log_size | 0
| max_seeks_for_key | 4294967295
| max_sort_length | 1024
| max_user_connections | 0
| max_tmp_tables | 32
| max_write_lock_count | 4294967295
| myisam_max_extra_sort_file_size | 268435456
| myisam_max_sort_file_size | 2147483647
| myisam_repair_threads | 1
| myisam_recover_options | OFF
| myisam_sort_buffer_size | 8388608
| net_buffer_length | 8192
| net_read_timeout | 30
| net_retry_count | 1000000
| net_write_timeout | 60
| new | OFF
| open_files_limit | 29491
| log_error |
| port | 3306
| protocol_version | 10
| query_alloc_block_size | 8192
| query_cache_limit | 1048576
| query_cache_size | 524288
| query_cache_type | ON
| query_prealloc_size | 8192
| range_alloc_block_size | 2048
| read_buffer_size | 131072
| read_only | OFF
| read_rnd_buffer_size | 262144
| rpl_recovery_rank | 0
| server_id | 0
| slave_net_timeout | 3600
| skip_external_locking | ON
| skip_networking | OFF
| skip_show_database | OFF
| slow_launch_time | 2
| sort_buffer_size | 524280
| sql_mode | 64
| table_cache | 64
| table_type | MYISAM
| thread_cache_size | 0
| thread_stack | 196608
| tx_isolation | REPEATABLE-READ
| timezone | MSK
| tmp_table_size | 33554432
| transaction_alloc_block_size | 8192
| transaction_prealloc_size | 4096
| version | 4.0.20
| version_comment | FreeBSD port: mysql-server-4.0.20
| version_compile_os | portbld-freebsd4.8
| wait_timeout | 120

 

[Wicked]

А что, собственно, страшного, что таблица будет немного "рыхлой"? Тем более, что данные именно обновляются, а не стираются.

1. таблицы из полей varchar. Насколько сильно поможет (и поможет ли) замена их на тип char ?

Ну должно в некоторой степени помочь против кластеризации, т.к. блоки будут одинакового размера. Но готовься, что у тебя будет куча реально незанятого места.

-~{}~ 23.11.06 18:00:

И учти, что в mysql < 5.1 есть такая штука как Silent Column Specification Changes:

... all CHAR columns longer than three characters are changed to VARCHAR columns. &copy; 5.0 manual

In some cases, older versions of MySQL silently changed column specifications from those given in a CREATE TABLE or ALTER TABLE statement. No such changes are made by MySQL 5.1, and an error occurs if a column cannot be created using the specified data type. &copy; 5.1 manual

 

[Апельсин]

у тебя myisam_sort_buffer_size маленький. Всего 8M.

 

[Sult]

Автор оригинала: Wicked
А что, собственно, страшного, что таблица будет немного "рыхлой"? Тем более, что данные именно обновляются, а не стираются.

Я может неправильно выразился. Данные стираются (частями) а потом записываются.

Ну должно в некоторой степени помочь против кластеризации, т.к. блоки будут одинакового размера. Но готовься, что у тебя будет куча реально незанятого места.
И учти, что в mysql < 5.1 есть такая штука как Silent Column Specification Changes:

Да, заметил такую штуку еще ранее. Так что пока это отпадает (у меня 4.02)

у тебя myisam_sort_buffer_size маленький. Всего 8M.

Увеличил до 128 М
Результаты следующие:
Таблица 1.8 млн записей 200 Мб время оптимизации (OPTIMIZE TABLE) - 13 мин
конечно же, это много, если учесть, что таблица при этом лочится
но хуже всего, что при этом тормозит весь сервер, даже когда идет обращение к другим таблицам.

А есть еще таблицы под 4 млн.

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

Кстати, собирюсь доставлять на днях 1Гб памяти (станет 2). Положительный эффект от этого в общих чертах понятен.

Хотелось бы узнать последствия для MySQL если доставить 2-й проц (Xeon 3ГГц)

 

[alpine]

Sult

Кстати, собирюсь доставлять на днях 1Гб памяти (станет 2). Положительный эффект от этого в общих чертах понятен.

Хотелось бы узнать последствия для MySQL если доставить 2-й проц (Xeon 3ГГц)

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

 

[Sult]

Еще изменил параметры в сторону увеличения выделенной памяти, чуть помогло- стало работать на 30-40% быстрее.

Поставил след.парметры:

set-variable=myisam_sort_buffer_size=256M

[myisamchk]
set-variable=key_buffer=256M
set-variable=sort_buffer_size=256M
set-variable=read_buffer=4M
set-variable=write_buffer=4M

 

[Krishna]

MyISAM
Есть смысл перейти на InnoDB ?
просто не было опыта работы с ними, вроде пишут, что хороши для больших объемов данных...

InnoDB не поддерживает FULLTEXT, к сожалению, и в обозримом будущем поддерживать не собирается. У тебя везде хранятся актуальные данные? Просто если реально обновляются только последние данные, то может стоит сменить MyISAM на MERGED например..

 

[Sult]

Автор оригинала: Krishna
InnoDB не поддерживает FULLTEXT, к сожалению, и в обозримом будущем поддерживать не собирается. У тебя везде хранятся актуальные данные? Просто если реально обновляются только последние данные, то может стоит сменить MyISAM на MERGED например..

FULLTEXT не пользуюсь. Скрипт иморта данных заполняет/обновляет отдельную таблицу индексных слов, которая используется при полнотекстовом поиске. Вот эти таблицы и являются обычно самыми большими (сейчас до 4 млн. строк). Замечу, что проблем при самом поиске не возникает- скрипт работает быстро (только вот поледнее время стало чуть дольше, поэтому я и занялся проблемой оптимизации (OPTIMIZE) таблиц, они реально очень "рыхлые".) Проблемы в основном возникают при самом процессе изменения данных (очень длительный и грузит сервер). И со временем становится хуже, из за чего и был сделан вывод о необходимости оптимизации проблем.

По поводу обновления данных. Обновляются не последние, а все данные. Обновление проходит частями раз в сутки и за неделю таблицы (таблица данных + таблица индексных слов) обновляются полностью. Т.е. проходит что то-типа ротации. Обновление какой либо части таблицы проходит путем удаления этой части, а потом INSERT для этой части. UPDATE не подходит, потому что число новых записей всегда разное, большое усложнение кода при этом и не факт, что лучше по скорости (скорее всего гораздо хуже).

С MERGE - буду изучать, но на первый взгляд вроде не подходит.

-~{}~ 02.12.06 16:03:

Пока склоняюсь к следующему решению:
1. использовать myisamchk , а не OPTIMIZE TABLE (грузит всю базу при работе)
2. попробовать реализовать следующее- копировать таблицы в отдельную директорию, там применять к ним myisamchk, а потом обратно в рабочую

 

[Sad Spirit]

Автор оригинала: Sult
естественно таблицы становятся "рыхлыми" на 1Гб примерно 150 Мб накладных расходов

"Рыхлость" сама по себе не проблема, если
а) доступ к данным осуществляется в основном по индексам;
б) MySQL умеет использовать освободившееся место (судя по мануалу, умеет, хотя упоминания полей с переменной длиной наводят на мысли).

2. есть ли смысл переходить на другие базы (версии MySQL) данных? Если да, то какие?

Ну перейдёшь ты, допустим, на PostgreSQL и будут у тебя команды VACUUM и VACUUM FULL. Первая будет помечать место, занятое удалёнными записями, как пригодное для дальнейшего использования (случай "рыхлой" таблицы), но не будет блокировать работу с таблицей. Вторая будет блокировать таблицу на время своей работы, но уменьшать физический размер файла.

 

[Krishna]

FULLTEXT не пользуюсь.

Вот это и есть корень проблемы

 

[Sult]

Автор оригинала: Krishna
Вот это и есть корень проблемы

Утверждается, что НЕ использование FULLTEXT - корень роблемы.

Абсолютно, коллега, НЕ согласен. Я ж не пользуюсь оператором LIKE. Поиск производится по специально созданным "индексным" файлам. Еще раз пишу, что поиск по 4 млн. записей таблице осуществляется меньше 1 сек, что для меня вполне достаточно. Кроме того, мне кажется, что без FULLTEXT можно сделать гораздо более тонкие вещи (все таки FULLTEXT в какой то степени - черный ящик)

Посмотрите развитие серьезных поисковых движков типа Mnogosearch. В разное время они пользовались различными структурами, но FULLTEXT никогда. Я не говорю, что FULLTEXT плохо, но если делать с умом, то можно обойтись и без него.

-~{}~ 03.12.06 00:19:

Автор оригинала: Sad Spirit
"Рыхлость" сама по себе не проблема, если
а) доступ к данным осуществляется в основном по индексам;
б) MySQL умеет использовать освободившееся место (судя по мануалу, умеет, хотя упоминания полей с переменной длиной наводят на мысли).

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

Кстати, я писал ранее, что на 1Гб таблицы - накладных расходов 150 Мб. Термин "накладные расходы" и его значение, я подсмотрел в phpMyAdmin. После прогона этой 1 Гб таблицы через myisamchk получил размер 460 Mb. Так что вот так.

Автор оригинала: Sad Spirit
а) доступ к данным осуществляется в основном по индексам;

Естественно, все по индексам, никаких LIKE. Долгоиграющих запросов нет и близко.

 

[Sad Spirit]

Автор оригинала: Sult
Я тоже так думал раньше. Но на стабильно работающей системе стали все больше проявлятся проблемы. Нагрузка, объемы данных и пр. существенно не изменились. Изменилась со временем только "рыхлость" таблиц. Учитывая большой объем данных я сделал вывод, что проблема в этом.

Кстати, я писал ранее, что на 1Гб таблицы - накладных расходов 150 Мб. Термин "накладные расходы" и его значение, я подсмотрел в phpMyAdmin. После прогона этой 1 Гб таблицы через myisamchk получил размер 460 Mb. Так что вот так.

Прогон таблицы, в которой периодически заменяются все записи, через OPTMIZE / myisamchk --- занятие бессмысленное, с потом и кровью почищенное место всё равно придётся заполнять при следующей замене части записей.

С другой стороны, уменьшение таблицы в 2 раза после OPTIMIZE, если данные в ней не обновлялись сразу все, говорит нам о том, что мыскль таки не умеет полноценно использовать освободившееся место. Где-то что-то там пухнет, возможно индексы, из-за чего и проблемы лезут. В том же PostgreSQL старых версий, пока аналогичную проблему не исправили, рекомендовали периодическую перестройку индексов.

 

[Krishna]

Sult
Вы бы лучше изучили подробнее возможности ФТ. И что Вы так зациклились на LIKE? LIKE 'abc%' вполне себе использует индексы, например.
Это LIKE '%xxx' не использует.
Mnogosearch не использует ФТ по той причине, что последний появился совсем недавно.
Я считаю, что нет смысла изобретать велосипед, когда у системы есть заведомо более производительное и скорее всего более мощное средство, которое заодно позаботится о поддержании формы таблицы должным образом.
Консерватизм хорош в меру.

 

[Sult]

Автор оригинала: Krishna
Sult
Mnogosearch не использует ФТ по той причине, что последний появился совсем недавно.

Mnogosearch несколько раз кардинально менял структуру хранения данных и было это когда ФТ использовался вовсю.

Автор оригинала: Krishna
Вы бы лучше изучили подробнее возможности ФТ. И что Вы так зациклились на LIKE? LIKE 'abc%' вполне себе использует индексы, например.
Это LIKE '%xxx' не использует.

Изучил уже давно и проводил эксперименты по производительности. Никаких чудес не заметил (по сравнению с уже работающей классической схемой и c использванием myisamchk с оптимизационными ключами).
На счет LIKE-
>> Это LIKE '%xxx' не использует
ну я не думал, что здесь мы НЕ будем обсуждать такие прописные истины.
LIKE я упомянул, чтобы снять последующие тривиальные советы.
Я тоже использую ФТ- очень удобная штука, но и есть негативные моменты, которые актуальны в моем случае, например
"..Загрузка больших массивов данных в таблицу будет происходить намного быстрее, если таблица НЕ содержит индекс FULLTEXT.."
В принципе процесс индексации я беру на себя, и при этом не грузится сам сервер базы. Но опять же, в данном случае речь шла совсем не об этом (использовать или нет ФТ). Я не исключаю, что в данном проекте я возможно перейду на использование ФТ (особенно в свете предстоящих доработок по полнотекстовому поиску с использованием ФТ), но это никак не связано с текущей проблемой.

Если Вы имели ввиду, что "... OPTIMIZE TABLE с индексами FULLTEXT теперь работает в 100 раз быстрее. ...", так это понятно, но остаются сами данные, они же ведь остаются прежними. Если другое, так напишите об этом сразу.

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

И еще - давайте поменьше снобизма и менторского тона (ртфм, изучите получше и пр.) ..
Не надо щас меряться у кого круче яйца. Мы ж тут общаемся, наверное, чтобы истину установить..

 

[добрый эх]

Re: OPTIMIZE и пр.

Автор оригинала: Sult
таблицы обновляются каждый день и большими порциями

Может структуру/дизайн БД как-нить изменить
Например разделить таблицу на несколько таблиц один к одному.