RTzRa's hive - software:mysql

MySQL - информация и ссылки

anonymous@undisclosed.example.com (Anonymous) — Tue, 09 May 2017 15:34:00 +0000

MySQL - информация и ссылки

InnoDB — http://dev.mysql.com/doc/refman/5.1/en/innodb.html
MyISAM — http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html

Visual Representation of SQL Joins

anonymous@undisclosed.example.com (Anonymous) — Wed, 15 Dec 2021 23:35:58 +0000

Visual Representation of SQL Joins

Ссылка: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

Картинка

sql, join

MySQL (MariaDB и так далее) - тюним innodb_buffer_pool_size

anonymous@undisclosed.example.com (Anonymous) — Tue, 09 May 2017 15:34:00 +0000

MySQL (MariaDB и так далее) - тюним innodb_buffer_pool_size

Источники:

Решение 1: http://dba.stackexchange.com/a/27341
Решение 2: http://dba.stackexchange.com/a/19181
Решение 3: https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/

Решение 1

The biggest table you have makes up 16.47% (28/170) of the total data. Even if the table was highly written and highly read, not all 28G of the table is loaded in the buffer pool at one given moment. What you need to calculate is how much of the InnoDB Buffer Pool is loaded at any given moment on the current DB Server.

Here is a more granular way to determine innodb_buffer_pool_size for a new DB Server given the dataset currently loaded in the current DB Server's InnoDB Buffer Pool.

Run the following on your current MySQL Instance (server you are migrating from)

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_data';  -- IBPDataPages 
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total'; -- IBPTotalPages
SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; -- IPS

Run the formula IBPPctFull = IBPDataPages * 100.0 / IBPTotalPages.

SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPTotalPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'); -- SELECT @IBPTotalPages;
SET @IBPPctFull = CAST(@IBPDataPages * 100.0 / @IBPTotalPages AS DECIMAL(5,2));
SELECT @IBPPctFull;

If IBPPctFull is 95% or more, you should set innodb_buffer_pool_size to 75% of the DB Server's RAM.

If IBPPctFull is less than 95%, run this formula : IBPSize = IPS X IBPDataPages / (1024*1024*1024) X 1.05.

SET @IBPSize = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_page_size'); -- SELECT @IBPSize;
SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPSize = concat(ROUND(@IBPSize * @IBPDataPages / (1024*1024*1024) * 1.05, 2), ' GB' );
SELECT @IBPSize;

The number for IBPSize (in GB) is the number that more closely fits your actual working dataset.

Now, if IBPSize is still too big for the biggest Amazon EC2 RAM Config, use 75% of the RAM for the Amazon EC2 DB Server.

Решение 2

Here is what you should do. First run this query

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

This will give you the RIBPS, Recommended InnoDB Buffer Pool Size based on all InnoDB Data and Indexes with an additional 60%.

For Example

mysql>     SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
    ->     (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
    ->     FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
|     8 |
+-------+
1 row in set (4.31 sec)

mysql>

With this output, you would set the following in /etc/my.cnf

[mysqld]
innodb_buffer_pool_size=8G

Next, service mysql restart

After the restart, run mysql for a week or two. Then, run this query:

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM (SELECT variable_value PagesData FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A, (SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

This will give you how many actual GB of memory is in use by InnoDB Data in the InnoDB Buffer Pool at this moment.

Решение 3

Here are 3 MySQL performance tuning settings that you should always look at. If you do not, you are very likely to run into problems very quickly.

innodb_buffer_pool_size: this is the #1 setting to look at for any installation using InnoDB. The buffer pool is where data and indexes are cached: having it as large as possible will ensure you use memory and not disks for most read operations. Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM).

innodb_log_file_size: this is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery. Up to MySQL 5.1, it was hard to adjust, as you wanted both large redo logs for good performance and small redo logs for fast crash recovery. Fortunately crash recovery performance has improved a lot since MySQL 5.5 so you can now have good write performance and fast crash recovery. Until MySQL 5.5 the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted in MySQL 5.6.

Starting with innodb_log_file_size = 512M (giving 1GB of redo logs) should give you plenty of room for writes. If you know your application is write-intensive and you are using MySQL 5.6, you can start with innodb_log_file_size = 4G.

max_connections: if you are often facing the ‘Too many connections’ error, max_connections is too low. It is very frequent that because the application does not close connections to the database correctly, you need much more than the default 151 connections. The main drawback of high values for max_connections (like 1000 or more) is that the server will become unresponsive if for any reason it has to run 1000 or more active transactions. Using a connection pool at the application level or a thread pool at the MySQL level can help here.

mysql, mariadb, производительность, innodb buffer pool size

Ускоряем MySQL при помощи tmpfs

anonymous@undisclosed.example.com (Anonymous) — Tue, 09 May 2017 15:34:00 +0000

Ускоряем MySQL при помощи tmpfs

Зачем

В процессе работы MySQL время от времени создает временные таблицы, место хранения которых задается переменной tmpdir. Если таких файликов создается много (не хватает памяти или еще куча причин), то начинается активный i/o, что создает нагрузку на систему.

Один из способов избежать данной проблемы - смонтировать папку временных файлов на RAM-диск, в данном случае tmpfs.

Правим fstab

Добавляем в /etc/fstab следующее

# Temporary filesystem for MySQL
tmpfs	/dev/shm	tmpfs	rw,defaults,noexec,nosuid,size=512m	0	0

Размер можно изменять в параметре size

Монтируем свежедобавленный раздел

# mount -a

Правим my.cnf

Добавляем

tmpdir		= /dev/shm

и закомментариваем старое значение tmpdir, перезапускаем mysqld

$ sudo service mysql restart

Вы используете apparmor?

Если используется appamor, то в логах mysql будут сообщения вида

/usr/sbin/mysqld: Can't create/write to file '/dev/shm/ibMoIFzB' (Errcode: 13)
120805 22:22:14  InnoDB: Error: unable to create temporary file; errno: 13

Все просто - в профиле настроек mysql запрещено писать в новое месторасположение временных файлов.

Вносим изменения в файл /etc/apparmor.d/usr.sbin.mysqld, добавляем в самый конец строки так, чтобы получилось следующее:

  /sys/devices/system/cpu/ r,
  /dev/shm/** rw,
}

и перечитываем правила

$ sudo service apparmor restart

mysql, tmpfs, ускоряем mysql, apparmor

mysqlcheck - обслуживание базы

anonymous@undisclosed.example.com (Anonymous) — Tue, 09 May 2017 15:34:00 +0000

mysqlcheck - обслуживание базы

С помощью этой утилиты можно выполнить сразу несколько полезных операций над всеми или определенными базами MySQL.

Восстановление и оптимизация
```
$ sudo mysqlcheck -u root -p -Aor
```
Только востановление
```
$ sudo mysqlcheck -u root -p -Ar
```
Только оптимизация
```
$ sudo mysqlcheck -u root -p -Ao
```

Описание ключей:

A - Проверить на ошибки все Mysql базы данных

r - Отремонтировать выбранные базы данных

o - Оптимизировать выбранные базы данных

mysqlcheck, восстановление, оптимизиция

MySQL - Инструменты

anonymous@undisclosed.example.com (Anonymous) — Tue, 09 May 2017 15:34:00 +0000

MySQL - Инструменты

Разработка

Вот тут много ссылок и информации: http://habrahabr.ru/post/142385/

MySQL Workbench http://wb.mysql.com/
HeidiSQL. Бесплатная, очень быстрая и компактная http://www.heidisql.com/
dbForge Studio for MySQL. Есть бесплатная версия (надо зарегистрироваться) http://www.devart.com/ru/dbforge/mysql/studio/download.html
Execute Query http://executequery.org
DBDesigner 4 http://www.fabforce.net/dbdesigner4/index.php

Maatkit

Maatkit — это набор консольных утилит, в которых реализованы расширенные средства по администрированию MySQL: быстрому и очень гибкому резервированию и восстановлению информации, выполнению мониторинга и сервисных функций, сбору аналитической информации и ее визуальному отображению, автоматизации проведения рутинных операций, и многое-многое другое. http://blogerator.ru/page/mysql-server-maatkit-howto-1

Профайлер Neor Profile SQL

http://www.profilesql.com/

Тюнинг и оптимизация

mysqltuner — http://mysqltuner.com/mysqltuner.pl
Масса теории и советов - http://www.mysqlperformanceblog.com/

Бэкап

mylvmbackup - http://www.lenzg.net/mylvmbackup/
Sypex Dumper - http://sypex.net/

Бэкапим все базы MySQL

#!/bin/bash
USER="root"
PASSWORD="123123"
mkdir /var/backup/database/`date +%F`;
for DB in `mysql -u$USER -p$PASSWORD -N -e 'show databases' | awk '{print $1}'`; do
     mysqldump --user=$USER --host=$HOST --password=$PASSWORD ${DB} | gzip > /var/backup/database/`date +%F`/${DB}.sql.gz;
        echo "${DB} Backup";
    done
done

mysql, разработка, тюнинг, оптимизация, бэкап, mysqltuner