Нужна помощь в оптимизации mysql

Danyas · 3 Янв 2016

Привет форумчане, нужна помощь)
Заметил что время от времени падают таблицы hlstatsx, начал смотреть за sql сервером.
Вот что увидел mysqltuner

------— Recommendations —-------------------------------------------------—
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Restrict Host for user@% to user@SpecificDNSorIp
Reduce or eliminate unclosed connections and network issues
Adjust your join queries to always utilize indexes
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (1024) variable
should be greater than table_open_cache ( 431)

Variables to adjust:
query_cache_type (=1)
sort_buffer_size (> 256K)
read_rnd_buffer_size (> 256K)
join_buffer_size (> 256.0K, or always use indexes with joins)
table_open_cache (> 431)
innodb_buffer_pool_instances (=1)

В phpmyadmin'е ситуация похожая, правда сервер был недавно перезапущен и по этому ошибок \ варнов стало в несколько раз меньше.

Столкнулся с проблемами:
1) Отсутствие конфигурационного файла.
При написании /usr/sbin/mysqld --help --verbose
выдало

PHP:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Но всё эти файлы пустые, некоторых нету вообще.

2) phpmyadmin не сохраняет настройки после перезапуска бд.
На той страничке, которая на скрине можно менять настройки, так вот, они не сохраняются.

OS Debian, mysql взят из apt-get install sql-server

Tallanvor · 3 Янв 2016

Danyas написал(а):
Но всё эти файлы пустые, некоторых нету вообще.

apt-get install mysql-common (странно, что не поставился, этот пакет идёт в зависимостях у mysql-server - но это на будущее)

В общем и в целом - подбор конфы мускуля идёт сугубо индивидуально, подстраиваясь под условия того окружения, где он запущен.
Базовую конфу можно нагенерить вот этим вот неплохим инструментом:
https://tools.percona.com/wizard
Нужна регистрация, но утиля бесплатная.
Отвечаешь на вопросы про окружение и на выходе получаешь конфу.
Советую сначала поставить mysql-common, забекапить рядышком /etc/mysql/my.cnf и перезаписать его содержимое тем, что выдаст перкона.
Не забываем рестартить мускуль :)

Добавлено через 4 минуты

Danyas написал(а):
2) phpmyadmin не сохраняет настройки после перезапуска бд

Он и не сохранит их никогда, потому что он, во-первых, не имеет доступа к конфигу (да и не ищет его), во-вторых, пыхоадмин меняет настройки через set global, то-есть они актуальны для данного инстанса, но, как и в случае с оперативкой, очищаются при стопе сервиса.

Danyas · 3 Янв 2016

Tallanvor написал(а):
apt-get install mysql-common (странно, что не поставился, этот пакет идёт в зависимостях у mysql-server - но это на будущее)

server% sudo apt-get install mysql-common
[sudo] password for server:
Reading package lists... Done
Building dependency tree
Reading state information... Done
mysql-common is already the newest version (5.6.27-2).
mysql-common set to manually installed.

:sad:

Tallanvor написал(а):
В общем и в целом - подбор конфы мускуля идёт сугубо индивидуально, подстраиваясь под условия того окружения, где он запущен.
Базовую конфу можно нагенерить вот этим вот неплохим инструментом:
https://tools.percona.com/wizard
Нужна регистрация, но утиля бесплатная.
Отвечаешь на вопросы про окружение и на выходе получаешь конфу.

Понял, спасибо, попробую

Нашелся

root@server:/etc/mysql/mysql.conf.d# ls -l
total 8
-rw-r--r-- 1 root root 3050 Jan 3 13:04 mysqld.cnf
-rw-r--r-- 1 root root 21 Jun 26 2015 mysqld_safe_syslog.cnf

Tallanvor · 3 Янв 2016

Danyas написал(а):
/etc/mysql/mysql.conf.d

Точно Дебиан?)

cat /etc/lsb-release

Danyas · 3 Янв 2016

Tallanvor написал(а):
Точно Дебиан?)

cat /etc/lsb-release

root@server:/etc/mysql/mysql.conf.d# cat /etc/lsb-release
cat: /etc/lsb-release: No such file or directory

Возможно :-D

Tallanvor · 3 Янв 2016

Danyas, а если

C-подобный:

cat /etc/os-release

?

Danyas · 20 Янв 2016

Tallanvor написал(а):
Danyas, а если

C-подобный:

cat /etc/os-release

?

PHP:

PRETTY_NAME="Debian GNU/Linux stretch/sid"
NAME="Debian GNU/Linux"
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"

Как-то-так

Tallanvor · 21 Янв 2016

Danyas, та шоб тебя черти трах@ли!!!

Кто тебя надоумил нестабильный билд юзать?!!!

Старый стабильный (oldstable) = wheezy; стабильный (stable) = jessie; тестируемый (testing) = stretch; нестабильный (unstable) = sid

Данный релиз должны использовать разработчики и тестеры. Опытные пользователи могут рассмотреть возможность использования Debian Testing.

!!!

Danyas · 21 Янв 2016

Tallanvor написал(а):
Danyas, та шоб тебя черти трах@ли!!!
Кто тебя надоумил нестабильный билд юзать?!!!

А я то думаю, почему после апдейта время от времени что-то отваливается :-D

Tallanvor · 21 Янв 2016

Danyas, мой тебе настоятельный совет - сделай ресетап на stable...

Danyas · 21 Янв 2016

Tallanvor написал(а):
Danyas, мой тебе настоятельный совет - сделай ресетап на stable...

Ресетап - вряд ли, возможно даунгрейд сделаю.
Да и оно вроде сейчас не горит, хотя багов - хоть отбавляй

Tallanvor · 21 Янв 2016

Danyas, а в чём проблема ресетапа?)
Некуда забекапиться?

Danyas · 21 Янв 2016

Tallanvor написал(а):
Danyas, а в чём проблема ресетапа?)
Некуда забекапиться?

Лишних дня два нету, что бы на это выделить)

Tallanvor · 21 Янв 2016

Danyas,
༼ ºل͟º ༽

Danyas · 23 Янв 2016

Собстна продолжаю дальше мучатся с базой, дал серверу постоять в штатном режиме около трех дней.
phpmyadmin пишет такое

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

mysql tuner пишет такое, сейчас еще раз попытаюсь исправить

PHP:

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 18h 48m 0s (1M q [4.830 qps], 23K conn, TX: 709M, RX: 474M)
[--] Reads / Writes: 37% / 63%
[--] Binary logging is disabled
[--] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
[OK] Maximum reached memory usage: 197.1M (10.01% of installed RAM)
[OK] Maximum possible memory usage: 338.9M (17.20% of installed RAM)
[OK] Slow queries: 0% (6/1M)
[OK] Highest usage of available connections: 16% (25/151)
[!!] Aborted connections: 15.72%  (3739/23782)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 6% (8K temp sorts / 132K sorts)
[!!] Joins performed without indexes: 9048
[OK] Temporary tables created on disk: 1% (2K on disk / 111K total)
[OK] Thread cache hit rate: 99% (86 created / 23K connections)
[!!] Table cache hit rate: 0% (358 open / 40K opened)
[OK] Open file limit used: 34% (354/1K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

-------- MyISAM Metrics -----------------------------------------------------
[OK] Key buffer used: 100.0% (8M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/160.8M
[OK] Read Key buffer hit rate: 99.3% (213M cached / 1M reads)
[!!] Write Key buffer hit rate: 72.5% (2M cached / 578K writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/47.0M
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[OK] InnoDB Used buffer: 87.50% (7167 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 100.00% (110446460 hits/ 110449559 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 131539 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    Reduce or eliminate unclosed connections and network issues
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache ( 431)
Variables to adjust:
    query_cache_type (=1)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    table_open_cache (> 431)
    innodb_buffer_pool_instances (=1)

Конфиг mysql-я

# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name server-dpp generated for fmu1337@gmail.com at 2016-01-03 16:08:04

[mysql]

# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size = 16777216
myisam-recover = FORCE,BACKUP

# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now = 1
innodb = FORCE
innodb-strict-mode = 1

# DATA STORAGE #
datadir = /var/lib/mysql/

# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1

# CACHES AND LIMITS #
tmp-table-size = 48M
max-heap-table-size = 32M
query-cache-type = 1
query-cache-size = 32
max-connections = 150
thread-cache-size = 16
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 32M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 384M
innodb_buffer_pool_instances = 1

# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log

long_query_time = 5
sort_buffer_size = 2097144
read_rnd_buffer_size = 2097144

xek · 8 Окт 2021

Percona Toolkit

Simplify your Open Source Database operations and optimize performance by using Percona's command-line Open Source Database tools offered in Percona Toolkit to streamline complex server and system tasks

tools.percona.com

Как понимаю, они что то поменяли?

TOWUK · 8 Окт 2021

Ubuntu + mariadb, устанавливается за 2минуты, натсройка за 1минуту. Ебли 0, только по началу если не разбираться

xek · 8 Окт 2021

TOWUK написал(а):
Ubuntu + mariadb, устанавливается за 2минуты, натсройка за 1минуту. Ебли 0, только по началу если не разбираться

Вот речь только идёт не про установку, а оптимизацию

Нужна помощь в оптимизации mysql

Danyas

Участник

Tallanvor

НЕ ПИШУ я плагины!!! И не правлю! И ваще...

Danyas

Участник

Tallanvor

НЕ ПИШУ я плагины!!! И не правлю! И ваще...

Danyas

Участник

Tallanvor

НЕ ПИШУ я плагины!!! И не правлю! И ваще...

Danyas

Участник

Tallanvor

НЕ ПИШУ я плагины!!! И не правлю! И ваще...

Danyas

Участник

Tallanvor

НЕ ПИШУ я плагины!!! И не правлю! И ваще...

Danyas

Участник

Tallanvor

НЕ ПИШУ я плагины!!! И не правлю! И ваще...

Danyas

Участник

Tallanvor

НЕ ПИШУ я плагины!!! И не правлю! И ваще...

Danyas

Участник

xek

Рыба клоун

Percona Toolkit

TOWUK

Участник

xek

Рыба клоун