OPTIMIZE и пр.

Тема в разделе "PHP & Mysql & MariaDB & Percona", создана пользователем Sult, 22 ноя 2006.

  1. Sult

    Sult Новичок

    Сообщения:
    7
    OPTIMIZE и пр.

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

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

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

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

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

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

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

    Спасибо.
  2. Апельсин

    Апельсин Оранжевое создание

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

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

    Sult Новичок

    Сообщения:
    7
    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
  4. Wicked

    Wicked Новичок

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

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

    -~{}~ 23.11.06 18:00:

    И учти, что в mysql < 5.1 есть такая штука как Silent Column Specification Changes:
  5. Апельсин

    Апельсин Оранжевое создание

    Сообщения:
    1.819
    Адрес:
    Москва
    у тебя myisam_sort_buffer_size маленький. Всего 8M.
  6. Sult

    Sult Новичок

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

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

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

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

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

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

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

    alpine Новичок

    Сообщения:
    1.037
    Адрес:
    Kharkiv, Ukraine
    Sult
    У тебя все упирается в конечном счете в производительность дисковой системы.
  8. Sult

    Sult Новичок

    Сообщения:
    7
    Еще изменил параметры в сторону увеличения выделенной памяти, чуть помогло- стало работать на 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
  9. Krishna

    Krishna Продался Java

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

    Sult Новичок

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

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

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

    -~{}~ 02.12.06 16:03:

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

    Sad Spirit мизантроп (Старожил PHPClub) Команда форума

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

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

    Krishna Продался Java

    Сообщения:
    1.372
    Адрес:
    Default City
    Вот это и есть корень проблемы :)
  13. Sult

    Sult Новичок

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

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

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

    -~{}~ 03.12.06 00:19:

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

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

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

    Sad Spirit мизантроп (Старожил PHPClub) Команда форума

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

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

    Krishna Продался Java

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

    Sult Новичок

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

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

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

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

    добрый эх Новичок

    Сообщения:
    22
    Re: OPTIMIZE и пр.

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