mysql: find InnoDB table size

How to find Innodb tables size?

"show table status" command doesn't show separated Innodb tables size, it showes total InnoDB data size. So we can use INFORMATION_SCHEMA for finding size of each InnoDB table.

for example, try  find 10 biggest InnoDB tables:

#mysql -A


mysql> use INFORMATION_SCHEMA
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, CREATE_TIME, UPDATE_TIME FROM TABLES WHERE ENGINE='InnoDB' ORDER BY DATA_LENGTH DESC limit 10;



using  INFORMATION_SCHEMA you can find data_length, index_length and other useful information

mysql: How find fragmented MySQL tables

#mysql

> use information_schema

> select ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables where DATA_FREE > 0;




The "You must to know" answer

first at all you must to understand that Mysql tables get fragmented when update a row, so it's a normal situation. When a table is created, lets say imported using a dump with data, all rows are stored with no fragmentation in many fixed size pages. When you update a variable length row, the page containing this row is divided in two or more pages to store the changes, and these new two(or more) pages contains blank spaces filling the unused space.

This does not impact in your performance, unless of course the fragmentation growth too much. What is too much fragmentation, well let's see the query you're looking for:

select ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables where DATA_FREE > 0;

The DATA_LENGTH and INDEX_LENGTH are the space your data and indexes are using, and DATA_FREE is the total amount of bytes unused in all the table pages (fragmentation).

Here's an example of real production table

| ENGINE | TABLE_NAME | data_length | index_length | data_free |
| InnoDB | comments | 896 | 316 | 5 |

In this case we have a Table using (896 + 316) = 1212 MB, and have data a free space of 5 MB. This means a "ratio of fragmentation" of:

5/1212 = 0.0041

...Which is a really low "fragmentation ratio".

I've been working with tables with a ratio near 0.2 (meaning 20% of blank spaces) and never notice a slow down on queries, even if I optimize the table, the performance is the same. But apply a optimize table on a 800MB table takes a lot of time and blocks the table for several minutes, impracticable on production.

So, if you consider what you win in performance and the time wasted in optimize a table, I prefer NOT OPTIMIZE.

If you think it's better for storage, see your ratio and see how much space can you save when optimize. It's usually not too much, so I prefer NOT OPTIMIZE.

And if you optimize, the next update will create blank spaces by splitting a page in two or more. But it's faster to update a fragmented table than a not fragmented one, because if the table is fragmented an update on a row not necessarily will split a page.

I hope this help you.



http://serverfault.com/questions/202000/how-find-and-fix-fragmented-mysql-tables
http://dba.stackexchange.com/questions/16341/how-do-you-remove-fragmentation-from-innodb-tables

optimize table in mysql

The SQL query that help  to find tables which are non-optimal :

SHOW TABLE STATUS WHERE Data_free > [integer value]

substituting [integer value] for an integer value, which is the free data space in bytes. This could be e.g. 102400 for tables with 100k of free space. This will then only return the tables which have more than 100k of free space.

An alternative way of searching would be to look for tables that have e.g. 10% of overhead free space by doing this:

SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1

The downside with this is that it would include small tables with very small amounts of free space so it could be combined with the first SQL query to only get tables with more than 10% overhead and more than 100k of free space:

SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1 AND Data_free > 102400

for d in `mysql -e "show databases"|g -v Database|g -v information_schema` ; do echo " -------- $d ----------" && for t in `mysql -D  $d  -e "SHOW TABLE STATUS WHERE Data_free > 0 " | awk '{ if ($2 != "MyISAM") printf $1 "\n"}'`; do echo $t && mysql -D  $d  -e "optimize table $t"  ; done   ; done

You can optimize all table in all mysql databases with shell command  :

# for d in `mysql -e "show databases"|grep  -v Database|g -v information_schema` ; do echo " ----- Database:  $d  ----------"  ;  for t in `mysql -D  $d  -e "SHOW TABLE STATUS WHERE Data_free  > 0 "  | grep -v Name  |  awk '{print $1 }' `; do echo "optimize table $t"  ; mysql -D  $d  -e "optimize table $t"  ; done   ; done

or you can optimize only some one database

# for t in `mysql -D    -e "SHOW TABLE STATUS WHERE Data_free  > 0 "  | grep -v Name  |  awk '{print $1 }' `; do echo "optimize table $t"  ; mysql -D    -e "optimize table $t"  ; done   

Remember:  optimization of big mysql tables takes a long time

find all InnoDB (MyISAM) tables in mysql

All InnoDB table:

1. with  shell command line:
   # mysql -B -D INFORMATION_SCHEMA -e "SELECT table_schema, table_name FROM TABLES WHERE engine = 'innodb'; "
   

2. or with  mysql command line:

   mysql> SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';

if you want find all MyISAM tables simply change @innodb@ to "MyISAM" in command

Оптимізація mysql

Для оптимізація mysql бази даних є кілька важливих моментів

1.  потрібно визначити загальний обєм всіх індексів у всіх базах даних, це можна зробити за допомогою команди


s=0; i=0; for d in `mysql -e "show databases" |grep -v Database ` ; do i=`mysql -D $d -e "show table status" | awk '{sum= sum+$9;} END {print sum}'` ; echo $i |grep . > /dev/null || i=0; echo "$d ---> $i" ; s=`echo $s+$i|bc` ; done ; echo "index size: $s bytes" 

виводить список баз даних і відповідно розмір індексів  в ній, а в кінці загалний розмір індексу
(правда тут тип (myisam, innodb)  не розрізняється.. виправити не проблема..  )



2.  Для швидкої оптимізації myisam mysql таблиць можна  скоритатися такою командою:
   
   for d in `mysql -e "show databases"|g -v Database|g -v information_schema` ; do echo " -------- $d ----------" && for t in `mysql -D $d -e "SHOW TABLE STATUS WHERE Data_free > 0 " | awk '{ if ($2 == "MyISAM") printf $1 "\n"}'`; do echo $t && mysql -D $d -e "optimize table $t" ; done ; done
   
   Для optimize конкретної бази: 
   for t in `mysql -D dbname -e "show tables" |grep -v Tables` ; do echo $t ; mysql -D dbname -e " optimize table $t" ; done

  Для innodb таблиць: 

for d in `mysql -e "show databases"|g -v Database|g -v information_schema` ; do echo " -------- $d ----------" && for t in `mysql -D $d -e "SHOW TABLE STATUS WHERE Data_free > 0 " | awk '{ if ($2 == "InnoDB") printf $1 "\n"}'`; do echo $t && mysql -D $d -e "optimize table $t" ; done ; done








 Замітка по оптимізації за допомогою optimize: Якщо по таблиці є кілька індексів (крім primary) - при великій твблиці цей процес може розтягрутись на години(!). В такому випадку в рази швидше буде якщо  дропати індекси, виконувати optimize і тоді перестворювати індекси.

помилки в mysql реплікації

SLAVE STOP; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;


my.cnf:
 slave-skip-errors = 1356

php скрипт для бекапу бази даних mysql

Одного разу при перенесенні кількох сайтів в мене виникли проблеми з переносом бази даних mysql: при імпорті бази було неправильне кодування, одним словом буква "ш" не відображалася.. ні пхпмайадімін ні іншу способи не допомагали..
Допомвг мені php-шний скрипт, який сам бекап всю базу




backup_tables('localhost','username','password','db_name');

/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{

$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);

//get all of the tables
if($tables == '*')
{
$tables = array();
$result = mysql_query('SHOW TABLES');
while($row = mysql_fetch_row($result))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}

//cycle through
foreach($tables as $table)
{
$result = mysql_query('SELECT * FROM '.$table);
$num_fields = mysql_num_fields($result);

$return.= 'DROP TABLE '.$table.';';
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
$return.= "\n\n".$row2[1].";\n\n";

for ($i = 0; $i < $num_fields; $i++)
{
   while($row = mysql_fetch_row($result))
   {
      $return.= 'INSERT INTO '.$table.' VALUES(';
      for($j=0; $j<$num_fields; $j++)
      {
          $row[$j] = addslashes($row[$j]);
          $row[$j] = ereg_replace("\n","\\n",$row[$j]);
          if (isset($row[$j])) {  $return.= '"'.$row[$j].'"' ;        }
          else {  $return.= '""';    )
        if ($j<($num_fields-1)) { $return.= ','; }
      }
      $return.= ");\n";
   }
}
$return.="\n\n\n";
}

//save file
$handle = fopen($_SERVER['DOCUMENT_ROOT'].'/fffff.txt','w+');
fwrite($handle,$return);
fclose($handle);
}





якщо цей скрипт використовувати на шаред хостингу, то потрібно спочатку створити пустий файл dump_db.sql і змінити йому права на 666, в інашкому випадку буде помилку, про "permission denied ...."

стовривши дапм, скачавши його, я зміг імпортувати на новий сервер базу..

mysql --default-character-set=cp1251 -D db_name < dump_db.sql

(для мене було потріно саме cp1251 ..)

непогана статя по налагтуванням mysql

непогана статя по налагтуванням mysql

http://habrahabr.ru/blogs/mysql/108418/

Відновити пароль до mysql

Відновлення пароля для користувача root MySQL

Ви можете відновити MySQL сервера баз даних з паролем наступні п'ять простих кроків.

Крок № 1: Зупинка процесу MySQL сервером.

Крок № 2: Запуск MySQL (mysqld) сервера/демона з опцією --skip-grant-tables , з якою mysql не запитуватиме пароль.

Крок № 3: Підключення до MySQL сервера в якості користувача root.

Крок № 4: Встановлення нового MySQL-паролю для облікового запису root, тобто скинути пароль MySQL.

Крок № 5: Вихід і перезавантаження сервер MySQL.

Ось команди, які ви повинні ввести для кожного кроку (залогуйтесь як  користувач root):
Крок № 1: Зупинка служби MySQL

# /etc/init.d/mysql stop

Крок № 2: Запуск mysql сервер з опцією --skip-grant-tables :

# mysqld_safe --skip-grant-tables &

Крок № 3: Підключення до mysql-серверу за допомогою mysql-клієнта:

# mysql -u root

Крок № 4: Встановлення нового mysql-паролю для користувача root

mysql> use mysql;
mysql>update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> exit

Крок № 5: Зупинити MySQL сервер:

#mysqladmin shutdown

Крок № 6: Запуск сервера MySQL і перевірити його

#/etc/init.d/mysql start
#mysql -u root -p 

Як підрахувати обєм індексів всіх таблиць в БД mysql

Підрахувати обєм індексів всіх таблиць  в певній базі даним  mysql  можна так:

mysql -D db_name -e "show table status;" | awk '{sum= sum+$9;} END {print sum/1024/1024}'

якщо треба то потрібно додати імя користувача для доступу до БД і його пароль

mysql  -u -p ........


можна також порахувати загальний обєм бази даних:

mysql -D db_name -e "show table status;" | awk '{sum= sum+$7;} END {print sum/1024/1024}'

зміна типу даних в mysql

изменение структуры таблицы MySQL

Иногда структуру созданную с помощью CREATE TABLE нужно изменить. Проще всего это сделать на пустой таблице, иначе нужно смотреть чтобы в итоге преобразования не потерялись какие-то нужные данные. В любом случае, если вы делаете это первый раз, создайте заранее резервную копию базы. Изменение структуры:

Переименовать таблицу:

ALTER TABLE myfirsttable RENAME mysecondtable;

Переименовать столбец:

ALTER TABLE mytable CHANGE a b INTEGER;

Добавить новый столбец TIMESTAMP с именем mytimestamp:

ALTER TABLE mytable ADD mytimestamp TIMESTAMP;

Удалить столбец:

ALTER TABLE mytable DROP COLUMN notneeded;

Изменить тип столбца a INTEGER на TINYINT NOT NULL (оставляя имя прежним) и изменить тип столбца b с CHAR(10) на CHAR(20) с переименованием его с b на c:

ALTER TABLE mytable MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

mysql tunning script

Matt Mongomery at MySQL has developed a great MySQL "tuning primer" that will suggest basic performance tuning settings. It is available at: http://www.day32.com/MySQL/tuning-primer.sh

MySQL should be allowed at least 48 hours of normal operation before running, to allow for the best suggestions.

Что нужно настроить в mySQL сразу после установки?

Что нужно настроить в mySQL сразу после установки?

Вольный перевод довольно старой статьи с MySQL Performance Blog о том, что лучше сразу же настроить после установки базовой версии mySQL.

Удивительно, сколько народу устанавливает mySQL на свои сервера и оставляют его с настройками по умолчанию.

Несмотря на то, что в mySQL существует довольно много настроек, которые Вы можете изменить, есть набор действительно очень важных характеристик, которые обязательно нужно оптимизировать под собственный сервер. Обычно после такой небольшой настройки производительность сервера заметно увеличивается.

  * key_buffer_size — крайне важная настройка при использовании MyISAM-таблиц. Установите её равной около 30-40% от доступной оперативной памяти, если используете только MyISAM. Правильный размер зависит от размеров индексов, данных и нагрузки на сервер — помните, что MyISAM использует кэш операционной системы (ОС), чтобы хранить данные, поэтому нужно оставить достаточно места в ОЗУ под данные, и данные могут занимать значительно больше места, чем индексы. Однако обязательно проверьте, чтобы всё место, отводимое директивой key_buffer_size под кэш, постоянно использовалось — нередко можно видеть ситуации, когда под кэш индексов отведено 4 ГБ, хотя общий размер всех .MYI-файлов не превышает 1 ГБ. Делать так совершенно бесполезно, Вы только потратите ресурсы. Если у Вас практически нет MyISAM-таблиц, то key_buffer_size следует выставить около 16-32 МБ — они будут использоваться для хранения в памяти индексов временных таблиц, создаваемых на диске.
  * innodb_buffer_pool_size — не менее важная настройка, но уже для InnoDB, обязательно обратите на неё внимание, если собираетесь использовать в основном InnoDB-таблицы, т.к. они значительно более чувствительны к размеру буфера, чем MyISAM-таблицы. MyISAM-таблицы в принципе могут неплохо работать даже с большим количеством данных и при стандартном значении key_buffer_size, однако mySQL может сильно «тормозить» при неверном значении innodb_buffer_pool_size. InnoDB использует свой буфер для хранения и индексов, и данных, поэтому нет необходимости оставлять память под кэш ОС — устанавливайте innodb_buffer_pool_size в 70-80% доступной оперативной памяти (если, конечно, используются только InnoDB-таблицы). Относительно максимального размера данной опции — аналогично key_buffer_size — не стоит увлекаться, нужно найти оптимальный размер, найдите лучшее применение доступной памяти.
  * innodb_additional_mem_pool_size — данная опция практически никак не влияет на производительность mySQL, однако рекомендую оставлять для InnoDB около 20 МБ (или чуть больше) под различные внутренние нужды.
  * innodb_log_file_size — крайне важная настройка в условиях баз данных с частыми операциями записи в таблицы, в особенности при больших объёмах. Большие размеры увеличивают быстродействие, однако будьте осторожны — увеличится и время восстановления данных. Я обычно выставляю значение около 64-512 МБ в зависимости от размера сервера.
  * innodb_log_buffer_size — стандартное значение данной опции вполне подойдёт для большинства систем со средним количеством операций записи и небольшими транзакциями. Если же в Вашей системе бывают всплески активности, или Вы активно работаете с BLOB-данными, то рекомендую немного увеличить значение innodb_log_buffer_size. Однако не переусердствуйте — слишком большое значение будет пустой тратой памяти: буфер сбрасывается каждую секунду, поэтому Вам не понадобится больше места, чем требуется в течение этой секунды. Рекомендуемое значение — около 8-16 МБ, а для небольших баз — и того меньше.
  * innodb_flush_log_at_trx_commit — жалуетесь, что InnoDB работает в 100 раз медленнее MyISAM? Вероятно, Вы забыли про настройку innodb_flush_log_at_trx_commit. Значение по умолчанию «1» означает, что каждая UPDATE-транзакция (или аналогичная команда вне транзакции) должна сбрасывать буфер на диск, что достаточно ресурсоёмко. Большинство приложений, в особенности ранее использовавшие таблицы MyISAM, будут хорошо работать со значением «2» (т.е. «не сбрасывать буфер на диск, только в кэш ОС»). Лог, однако, всё равно будет сбрасываться на диск каждые 1-2 секунды, поэтому в случае аварии Вы потеряете максимум 1-2 секунды обновлений. Значение «0» повысит производительность, но Вы рискуете потерять данные даже при аварийной остановке mySQL-сервера, в то время как при установке значение innodb_flush_log_at_trx_commit в «2» Вы потеряете данные только при аварии всей операционной системы.
  * table_cache — открытие таблиц может быть весьма ресурсоёмко. К примеру, MyISAM-таблицы помечают заголовки .MYI файлов как «используемые в текущий момент». Обычно не рекомендуется открывать таблицы слишком часто, поэтому лучше, чтобы кэш был достаточных размеров, чтобы держать все Ваши таблицы открытыми. Для этого используется некоторое количество ресурсов ОС и оперативной памяти, однако это обычно не является существенной проблемой для современных серверов. Если у Вас несколько сотен таблиц, то стартовым значением для опции table_cache может быть«1024» (помните, что каждое соединение требует свой собственный дескриптор). Если у Вас ещё больше таблиц или очень много соединений — увеличьте значение параметра. Я видел mySQL сервера со значением table_cache равной 100 000.
  * thread_cache — создание/уничтожение потоков также является ресурсоёмкой операцией, которая происходит при каждой установке соединения и каждом разрыве соединения. Я обычно выставляю эту опцию равную 16. Если у Вашего приложения могут быть скачки количество конкурентных соединений и по переменной Threads_Created виден быстрый рост количества потоков, то стоит увеличить значение thread_cache. Цель — не допускать создания новых потоков в условиях нормального функционирования сервера.
  * query_cache_size — если Ваше приложение много и часто читает данные, и при этом у Вас нет кэша на уровне приложения, эта опция может очень помочь. Не ставьте здесь слишком большое значение, так как обслуживание большого кэша запросов будет само по себе затратным. Рекомендуемое значение — от 32 до 512 МБ. Не забудьте проверить, насколько хорошо используется кэш запросов — в некоторых условиях (при небольшом количестве хитов в кэше, т.е. когда практически не выбираются одинаковые данные) использование большого кэша может ухудшить производительность.


Как Вы можете видеть, это — глобальные настройки. Эти переменные зависят от «железа» сервера и используемых движков mySQL, в то время как сессионные переменные обычно настраиваются специально под конкретные задачи. Если Вы в основном используете простые запросы, то нет никакой необходимости увеличивать значение sort_buffer_size, даже если у Вас есть лишние 64 ГБ оперативной памяти. Более того, большие значения кэшей могут только ухудшить производительность сервера. Сессионные переменные лучше оставить на потом, для тонкой настройки сервера.

PS: инсталляция mySQL идёт с несколькими предустановленными файлами my.cnf, рассчитанными под разную нагрузку. Если Вам некогда настраивать сервер вручную, то обычно лучше использовать их, чем стандартный конфигурационный файл, выбрав тот, что больше подойдёт под нагрузку Вашего сервера. 

Что нужно настроить в mySQL сразу после установки?

load data

select * from table  [where id=8917597 ] into outfile "file" ;

scp 

load data infile "/home/vova/" into table table ;

myisamchk

myisamchk -r -f -O key_buffer=2048M -O sort_buffer=256M -O read_buffer=16M -O write_buffer=16M /ssd/mysql/*/*MYI

-r или --recover При указании этой опции можно исправить практически все, кроме уникальных ключей, в которых есть повторения (ошибка, вероятность которой мизерна для таблиц ISAM/MyISAM). Если необходимо восстановить таблицу, то начинать надо с этой опции. Только если myisamchk сообщит, что таблица не может быть восстановлена с помощью -r, тогда следует пытаться применять -o (отметим, что в тех маловероятных случаях, когда -r не срабатывает, файл данных остается неизменным), В случае большого объема памяти следует увеличить размер sort_buffer_size!

-f или --force Писать поверх старых временных файлов (`table_name.TMD') вместо аварийного прекращения.

myisamchk -a key_buffer=2048M -O sort_buffer=256M -O read_buffer=16M -O write_buffer=16M /ssd/mysql/*/*MYI

Кроме ремонта и проверки таблиц, myisamchk может выполнять другие операции:
-a или --analyze
Анализировать распределение ключей. Улучшает эффективность операции связывания за счет включения оптимизатора связей. Он обеспечивает лучший порядок связывания таблиц и определяет, какие ключи при этом следует использовать: myisamchk --describe --verbose table_name или посредством SHOW KEYS в MySQL.

-S или --sort-index
Сортировать блоки индексного дерева в порядке от больших к меньшим (high-low). Этим оптимизируются операции поиска и повышается скорость сканирования по ключу.
-R или --sort-records=#
Сортирует записи в соответствии с индексом. Это значительно повышает локализацию данных и может ускорить операции SELECT и ORDER BY, которые выполняются по индексу и выбирают данные по какому-либо интервалу. (Возможно, что первая сортировка будет выполняться очень медленно!) Чтобы узнать номера индексов таблицы, нужно использовать команду SHOW INDEX, показывающую индексы таблицы в том же порядке, в каком их видит myisamchk. Индексы нумеруются начиная с 1.