mysql InnoDB i ibdata1

[Volodymyrko]

є в мене mysql сервер... крім myisam звичного.. використовується iinodb ....
одного разу обєм таблиць ІнноДБ дося більзько 8Г... подумали шо то задагато (гальма почалися)... витерли головну "винуватиця" (таблицю на 6,5 Г) ...
тепер загальний обєм даних ІнноДБ -  ~1,3Г...
а файл ibdata1 так і залишився ~8Г... чого розмір файлу не змінився я знаю (прочитав немало на рахунок цього)

 а питання маю таке... чи може розмір  файлу ibdata1 (всі таблиці в одному файлі, тобто innodb_file_per_table = OFF)  впливати негативно на продуктивність серверу... навіть якщо обєм даних значно менший (1,3 Г,..  innodb_buffer_pool_size = 2Г )...    
в принципі поки не можу явно сказати шо маю проблеми з mysql... просто цифра в 8Г  лякає .. може хто з досвіду знаю чи є  великий  і "пустий" ibdata1  "поганим"

[anatolijd]

тут не так важливий розмір файлу, як ступінь його фрагментованості.
який там autoextent size ?  таблиць багато ? update/delete часті ?
innodb приблизно раз в секунду скидає змінені блоки на диск, і при надто фрагментованому ibdata  це очевидно сповільнює запис.
А як тільки розмір бази вийде за розмір  innodb_buffer_pool_size - кількість звертань до диску виросте в рази, і iowait різко зросте.

Якщо ж там read/write ratio > 99% , то великий й пустий ibdata не відчувається.
Але знову ж, як тільки дані перестануть поміщатись в памяті і mysql доведеться регулярно підчитувати їх з диску - продуктивність впаде.

[Volodymyrko]

який там autoextent size

innodb_data_file_path                   | ibdata1:10M:autoextend

таблиць багато ?

319

update/delete часті ?

# mysqladmin extended-status |grep  Innodb_rows
| Innodb_rows_deleted               | 882626        |
| Innodb_rows_inserted              | 1410916       |
| Innodb_rows_read                  | 2596521081    |
| Innodb_rows_updated               | 1283622       |

2596519306/(882615+1410876+1283552)
725

А як тільки розмір бази вийде за розмір  innodb_buffer_pool_size - кількість звертань до диску виросте в рази, і iowait різко зросте.

про це я знаю... зараз в мене
innodb_buffer_pool_size                 | 2147483648   (з запасом, щоб раптово не закінчився)
а розмір даних 1,3Г..

виходить, тоді поки що воно мені не страшно.. час від часу  то що треба проводити optimize для запобігання великої фрагментації ..
правильно ?

[anatolijd]

загалом правильно.

стосовно OPTIMIZE - це треба пробувати щоб знайти отой "час-від-часу" паріод коли оптимізація приносить користь, і не виконувуати OPTIMIZE частіше ніж це дійсно необхідно.
Якщо по таблиці є кілька індексів (крім primary) - при великій твблиці цей процес може розтягрутись на години(!). В такому випадку в рази швидше буде якщо  дропати індекси, виконувати optimize і тоді перестворювати індекси.
А це не завжди можливо, оскільки якщо в вас production база під навантаженням, і ви дропнете тимчасово якийсь індекс - є великий шанс подкласти сервер взагалі (тому що всі select'и будуть виконувати full table scan замість key lookup ).