Сделайте вывод mysqltuner Xylitol - Администрирование серверов

675

vandamme

31 декабря 2016, 07:55

#21

dag:
Это я к тому, что может быть что угодно. Удивительно другое - 50 гиг данных это уже что то более менее серьезное.
Как оно вообще появилось на свет без единого специалиста?

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

X

47

Xylitol

31 декабря 2016, 08:25

#22

Vin_cent:
1. Сделать раздел mysql tmpdir на ram диске (tmpdir=/mnt/ramdisk):
mkdir -p /mnt/ramdisk
chown mysql:mysql /mnt/ramdisk
mount -t tmpfs -o size=8024M tmpfs /mnt/ramdisk

2. Если запросы короткие и простые, то можно попробовать вообще отключить mysql кэш: query_cache_type=0

3. Если таблицы myisam используются в основном для чтения, то в innodb переводить их нет смысла.

И покажи вывод mysqltuner.pl

Перевел временные файлы на рамдиск.

Вывод mysqltuner:


  >>  MySQLTuner 1.6.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[[0;34m--[0m] Skipped version check for MySQLTuner script
[[0;32mOK[0m] Currently running supported MySQL version 5.5.50-MariaDB
[[0;32mOK[0m] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[[0;34m--[0m] Status: [0;32m+ARCHIVE [0m[0;32m+Aria [0m[0;32m+BLACKHOLE [0m[0;32m+CSV [0m[0;32m+FEDERATED [0m[0;32m+InnoDB [0m[0;32m+MRG_MYISAM [0m
[[0;34m--[0m] Data in MyISAM tables: 21G (Tables: 3890)
[[0;34m--[0m] Data in InnoDB tables: 408M (Tables: 37)
[[0;34m--[0m] Data in MEMORY tables: 5M (Tables: 8)
[[0;31m!![0m] Total fragmented tables: 243

-------- Security Recommendations  -------------------------------------------
-------- Performance Metrics -------------------------------------------------
[[0;34m--[0m] Up for: 49s (150K q [3K qps], 6K conn, TX: 7B, RX: 19M)
[[0;34m--[0m] Reads / Writes: 95% / 5%
[[0;34m--[0m] Binary logging is disabled
[[0;34m--[0m] Total buffers: 3.0G global + 70.3M per thread (300 max threads)
[[0;32mOK[0m] Maximum reached memory usage: 6.6G (21.03% of installed RAM)
[[0;32mOK[0m] Maximum possible memory usage: 23.6G (75.39% of installed RAM)
[[0;32mOK[0m] Slow queries: 0% (58/150K)
[[0;32mOK[0m] Highest usage of available connections: 17% (52/300)
[[0;32mOK[0m] Aborted connections: 0.01%  (1/6945)
[[0;31m!![0m] Query cache efficiency: 15.2% (22K cached / 146K selects)
[[0;31m!![0m] Query cache prunes per day: 58774922
[[0;32mOK[0m] Sorts requiring temporary tables: 0% (0 temp sorts / 8K sorts)
[[0;32mOK[0m] Temporary tables created on disk: 0% (0 on disk / 8K total)
[[0;32mOK[0m] Thread cache hit rate: 91% (625 created / 6K connections)
[[0;31m!![0m] Table cache hit rate: 1% (401 open / 31K opened)
[[0;32mOK[0m] Open file limit used: 18% (744/4K)
[[0;32mOK[0m] Table locks acquired immediately: 99% (182K immediate / 182K locks)

-------- MyISAM Metrics -----------------------------------------------------
[[0;31m!![0m] Key buffer used: 41.3% (55M used / 134M cache)
[[0;32mOK[0m] Key buffer size / total MyISAM indexes: 128.0M/4.3G
[[0;32mOK[0m] Read Key buffer hit rate: 96.1% (5M cached / 200K reads)
[[0;31m!![0m] Write Key buffer hit rate: 0.1% (5K cached / 5K writes)

-------- InnoDB Metrics -----------------------------------------------------
[[0;34m--[0m] InnoDB is enabled.
[[0;32mOK[0m] InnoDB buffer pool / data size: 2.0G/408.4M
[[0;32mOK[0m] InnoDB buffer pool instances: 2
[[0;31m!![0m] InnoDB Used buffer: 2.59% (3393 used/ 131070 total)
[[0;32mOK[0m] InnoDB Read buffer efficiency: 95.63% (62690 hits/ 65555 total)
[[0;31m!![0m] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[[0;32mOK[0m] InnoDB log waits: 0.00% (0 waits / 346 writes)

-------- AriaDB Metrics -----------------------------------------------------
[[0;34m--[0m] AriaDB is disabled.

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

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Increasing the query_cache size over 128M may reduce performance
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://*******/1mi7c4C
    Beware that open_files_limit (4000) variable 
    should be greater than table_open_cache ( 400)
Variables to adjust:
    query_cache_limit (> 64M, or use smaller result sets)
    query_cache_size (> 256M) [see warning above]
    table_open_cache (> 400)

A

130

admak

31 декабря 2016, 09:14

#23

Xylitol, посмотрите индексы на большой табличке. если там есть примари по нескольким полям, то это повод присмотреться более внимательно. (обычно достаточно составной индекс примари перевести в unique)

когда ковыряете черный ящик(закрытый код), то полезно бывает смотреть sql запросы. временно включите slow log и поставьте время сначала одну секунду, потом 0.5 , затем 0.1 , если выживет, то еще уменьшайте. по полученному логу пытаемся понять логику разработчика приложения и думаем.

.............

165

Vin_cent

31 декабря 2016, 14:39

#24

Xylitol:
Перевел временные файлы на рамдиск.
Вывод mysqltuner:


  >>  MySQLTuner 1.6.0 - Major Hayden <major@mhtx.net>

 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

 >>  Run with '--help' for additional options and output filtering

[[0;34m--[0m] Skipped version check for MySQLTuner script

[[0;32mOK[0m] Currently running supported MySQL version 5.5.50-MariaDB

[[0;32mOK[0m] Operating on 64-bit architecture



-------- Storage Engine Statistics -------------------------------------------

[[0;34m--[0m] Status: [0;32m+ARCHIVE [0m[0;32m+Aria [0m[0;32m+BLACKHOLE [0m[0;32m+CSV [0m[0;32m+FEDERATED [0m[0;32m+InnoDB [0m[0;32m+MRG_MYISAM [0m
[[0;34m--[0m] Data in MyISAM tables: 21G (Tables: 3890)

[[0;34m--[0m] Data in InnoDB tables: 408M (Tables: 37)

[[0;34m--[0m] Data in MEMORY tables: 5M (Tables: 8)

[[0;31m!![0m] Total fragmented tables: 243



-------- Security Recommendations  -------------------------------------------

-------- Performance Metrics -------------------------------------------------

[[0;34m--[0m] Up for: 49s (150K q [3K qps], 6K conn, TX: 7B, RX: 19M)

[[0;34m--[0m] Reads / Writes: 95% / 5%

[[0;34m--[0m] Binary logging is disabled

[[0;34m--[0m] Total buffers: 3.0G global + 70.3M per thread (300 max threads)

[[0;32mOK[0m] Maximum reached memory usage: 6.6G (21.03% of installed RAM)

[[0;32mOK[0m] Maximum possible memory usage: 23.6G (75.39% of installed RAM)

[[0;32mOK[0m] Slow queries: 0% (58/150K)

[[0;32mOK[0m] Highest usage of available connections: 17% (52/300)

[[0;32mOK[0m] Aborted connections: 0.01%  (1/6945)

[[0;31m!![0m] Query cache efficiency: 15.2% (22K cached / 146K selects)

[[0;31m!![0m] Query cache prunes per day: 58774922

[[0;32mOK[0m] Sorts requiring temporary tables: 0% (0 temp sorts / 8K sorts)

[[0;32mOK[0m] Temporary tables created on disk: 0% (0 on disk / 8K total)

[[0;32mOK[0m] Thread cache hit rate: 91% (625 created / 6K connections)

[[0;31m!![0m] Table cache hit rate: 1% (401 open / 31K opened)

[[0;32mOK[0m] Open file limit used: 18% (744/4K)

[[0;32mOK[0m] Table locks acquired immediately: 99% (182K immediate / 182K locks)



-------- MyISAM Metrics -----------------------------------------------------

[[0;31m!![0m] Key buffer used: 41.3% (55M used / 134M cache)

[[0;32mOK[0m] Key buffer size / total MyISAM indexes: 128.0M/4.3G

[[0;32mOK[0m] Read Key buffer hit rate: 96.1% (5M cached / 200K reads)

[[0;31m!![0m] Write Key buffer hit rate: 0.1% (5K cached / 5K writes)



-------- InnoDB Metrics -----------------------------------------------------

[[0;34m--[0m] InnoDB is enabled.

[[0;32mOK[0m] InnoDB buffer pool / data size: 2.0G/408.4M

[[0;32mOK[0m] InnoDB buffer pool instances: 2

[[0;31m!![0m] InnoDB Used buffer: 2.59% (3393 used/ 131070 total)

[[0;32mOK[0m] InnoDB Read buffer efficiency: 95.63% (62690 hits/ 65555 total)

[[0;31m!![0m] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)

[[0;32mOK[0m] InnoDB log waits: 0.00% (0 waits / 346 writes)



-------- AriaDB Metrics -----------------------------------------------------

[[0;34m--[0m] AriaDB is disabled.



-------- Replication Metrics -------------------------------------------------

[[0;34m--[0m] No replication slave(s) for this server.

[[0;34m--[0m] This is a standalone server..



-------- Recommendations -----------------------------------------------------

General recommendations:

    Run OPTIMIZE TABLE to defragment tables for better performance

    Restrict Host for user@% to user@SpecificDNSorIp

    MySQL started within last 24 hours - recommendations may be inaccurate

    Increasing the query_cache size over 128M may reduce performance

    Increase table_open_cache gradually to avoid file descriptor limits

    Read this before increasing table_open_cache over 64: http://*******/1mi7c4C

    Beware that open_files_limit (4000) variable 

    should be greater than table_open_cache ( 400)

Variables to adjust:

    query_cache_limit (> 64M, or use smaller result sets)

    query_cache_size (> 256M) [see warning above]

    table_open_cache (> 400)

Up for: 49s

Пускай база без перезагрузок поработает хотя бы пару часов. И только тогда сделать вывод mysqltuner.

M

124

Mobiaaa

31 декабря 2016, 19:15

#25

Вроде же 50 гб было)

уже 20+

152

Metal Messiah

3 января 2017, 03:29

#26

Ну нельзя работать с такими большими таблицами. НЕЛЬЗЯ! Разве что если это не Amazon RDS или если вся таблица не лежит на RAM или SSD диске.

Таблицу надо бить на несколько. У меня пара гигов исторических записей на сколько-то лимонов тормозила, переписал скрипт под работу с разными таблицами - на актуальные данные из одной, если нужно подгрузить исторические (редко нужно) то и вторая тоже.

anonymous, думай что говоришь и не забывай подписать отзыв :)

X

47

Xylitol

3 января 2017, 10:15

#27

Таблица похудела потому, что очистил.

Мне кажется, что еще большой затык в дисковой подсистеме сервера

https://gyazo.com/d562e1d4d21cda1aef47b6e217ee046c

---------- Добавлено 03.01.2017 в 14:16 ----------

Metal_Messiah:
Ну нельзя работать с такими большими таблицами. НЕЛЬЗЯ! Разве что если это не Amazon RDS или если вся таблица не лежит на RAM или SSD диске.
Таблицу надо бить на несколько. У меня пара гигов исторических записей на сколько-то лимонов тормозила, переписал скрипт под работу с разными таблицами - на актуальные данные из одной, если нужно подгрузить исторические (редко нужно) то и вторая тоже.

на данный момент никак не могу изменить структуру таблиц. Не мой скрипт :(

1726

LEOnidUKG

3 января 2017, 10:37

#28

Ну можете докупить SSD и перекинуть туда mysql весь, должно полегче стать.

✅ Мой Телеграм канал по SEO, оптимизации сайтов и серверов: https://t.me/leonidukgLIVE ✅ Качественное и рабочее размещение SEO статей СНГ и Бурж: https://getmanylinks.ru/

X

47

Xylitol

3 января 2017, 10:54

#29

LEOnidUKG:
Ну можете докупить SSD и перекинуть туда mysql весь, должно полегче стать.

А не станет узким местом канал с сервером, на который бд поставить? Скорость обмена всяко меньше будет, чем с диском напрямую

N

419

netwind

4 января 2017, 10:42

#30

Xylitol, а станет. Или не станет.

Имелось ввиду, что вы диск SSD добавляете в тот же сервер.

Кнопка вызова админа ()

В 2023 году 36,9% всех DDoS-атак пришлось на сферу финансов

Все что нужно знать о DDоS-атаках грамотному менеджеру

Оптимизация работы с MySQL