Оптимизация MySQL по результатам mysqltuner.pl / MySQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация MySQL по результатам mysqltuner.pl

50 сообщений из 50, показаны все 2 страниц

все

Оптимизация MySQL по результатам mysqltuner.pl

#39141182

Vladimir_Praha

Гость

Добрый день всем, честно говоря я не профи в настройке mysql но после переноса части сайтов мускул стал грузить процессор под 300-500 процентов что и заставило меня полезть в более глубокую настройку.

Временные файлы вынес уже в ОЗУ, таблицы чекал раза четыре и разными способами но количество фрагментированных таблиц остается в пределах 300-500 таблиц, как ни крути. Не знаю почему, но вопрос в другом. Следую плавно советам mysqltuner и подкручиваю значения, паралелльно читая что за что отвечает и где какие должны быть оптимальные значения но уже начинаю волноватся за некоторые параметры которые кажутся "большими" скромно говоря. Можете проверить и подсказать что где не так или на что стоит обратить внимание, буду очень признателен.

вот содержимое файла my.cnf

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
default-character-set = utf8

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /dev/shm
language	= /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#
# * Fine Tuning
#
key_buffer		= 3048M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 32
join_buffer_size	= 256K
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 300
table_cache            = 8096
open_files_limit	= 16192
#interactive_timeout	= 28800
#wait_timeout		= 28800
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit	= 18M
query_cache_size        = 896M
table_open_cache	= 8000
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
log_slow_queries	= /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
default-character-set = utf8
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem


tmp_table_size = 512M
max_heap_table_size = 256M
table_cache = 4048
innodb_buffer_pool_size = 2048M



[mysqldump]
quick
quote-names
max_allowed_packet	= 16M
default-character-set = utf8

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

default-character-set = utf8
[isamchk]
key_buffer		= 2048M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

вот что выдает myslqtuner

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.63-0+squeeze1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 11G (Tables: 3504)
[--] Data in InnoDB tables: 1G (Tables: 292)
[--] Data in MEMORY tables: 0B (Tables: 4)
[!!] Total fragmented tables: 461

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 29m 5s (523K q [300.139 qps], 18K conn, TX: 2B, RX: 63M)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 6.1G global + 2.8M per thread (300 max threads)
[OK] Maximum possible memory usage: 6.9G (22% of installed RAM)
[OK] Slow queries: 1% (10K/523K)
[OK] Highest usage of available connections: 21% (65/300)
[OK] Key buffer size / total MyISAM indexes: 3.0G/1.5G
[OK] Key buffer hit rate: 100.0% (966M cached / 219K reads)
[OK] Query cache efficiency: 64.7% (284K cached / 439K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (260 temp sorts / 54K sorts)
[!!] Joins performed without indexes: 1227
[!!] Temporary tables created on disk: 42% (12K on disk / 28K total)
[OK] Thread cache hit rate: 99% (65 created / 18K connections)
[OK] Table cache hit rate: 26% (4K open / 15K opened)
[OK] Open file limit used: 44% (7K/16K)
[OK] Table locks acquired immediately: 99% (191K immediate / 191K locks)
[OK] InnoDB data size / buffer pool: 1.5G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with joins)

вот что творится в топе

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.

top - 00:48:28 up 237 days,  7:29,  1 user,  load average: 5.15, 6.64, 7.16
Tasks: 307 total,   5 running, 302 sleeping,   0 stopped,   0 zombie
Cpu(s): 75.1%us, 14.2%sy,  0.0%ni,  9.8%id,  0.7%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  32703444k total, 31550156k used,  1153288k free,  2300076k buffers
Swap: 33553332k total,     8296k used, 33545036k free, 21243024k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
15750 mysql     20   0 6620m 2.6g 7896 S  345  8.4  70:06.29 mysqld
23246 www-data  20   0  344m 127m 7276 R   73  0.4   0:35.28 apache2
24004 www-data  20   0  290m  77m 5928 S   44  0.2   0:06.39 apache2
23957 www-data  20   0  292m  77m 5976 R   41  0.2   0:19.32 apache2
24025 www-data  20   0  272m  60m 4292 S   30  0.2   0:00.90 apache2
23913 www-data  20   0  295m  83m 6716 R   29  0.3   0:37.49 apache2
23912 www-data  20   0  277m  65m 6768 S   25  0.2   0:47.11 apache2
24003 www-data  20   0  273m  60m 6232 S   24  0.2   0:16.04 apache2
23292 www-data  20   0  290m  78m 7556 S   21  0.2   1:06.56 apache2
23935 www-data  20   0  284m  69m 6780 S   19  0.2   0:32.12 apache2
23922 www-data  20   0  291m  78m 6120 R   16  0.2   1:02.37 apache2
23920 www-data  20   0  278m  65m 6872 S   15  0.2   0:21.00 apache2
23958 www-data  20   0  279m  67m 6920 S   11  0.2   0:18.90 apache2
24023 www-data  20   0  268m  57m 4784 S   10  0.2   0:01.65 apache2
23286 www-data  20   0  281m  67m 6816 S    9  0.2   0:07.07 apache2
23291 www-data  20   0  288m  74m 7092 S    1  0.2   0:33.66 apache2
 9591 root      20   0     0    0    0 S    0  0.0   1:51.62 kworker/4:1
18429 root      20   0     0    0    0 S    0  0.0   0:37.95 kworker/1:1
23311 www-data  20   0  277m  66m 6568 S    0  0.2   0:03.41 apache2
24024 root      20   0 19256 1456  948 R    0  0.0   0:00.02 top
26329 gitlab    20   0  943m 133m 2168 S    0  0.4 167:56.34 ruby1.9.1
28214 www-data  20   0 56732  28m 1788 S    0  0.1   1:08.11 nginx
    1 root      20   0  8404  716  592 S    0  0.0   2:09.28 init
    2 root      20   0     0    0    0 S    0  0.0   0:00.22 kthreadd
    3 root      20   0     0    0    0 S    0  0.0   9:10.40 ksoftirqd/0
    6 root      RT   0     0    0    0 S    0  0.0   0:26.19 migration/0
    7 root      RT   0     0    0    0 S    0  0.0   0:47.69 watchdog/0
    8 root      RT   0     0    0    0 S    0  0.0   0:32.35 migration/1
   10 root      20   0     0    0    0 S    0  0.0   5:38.45 ksoftirqd/1
   12 root      RT   0     0    0    0 S    0  0.0   0:31.18 watchdog/1
   13 root      RT   0     0    0    0 S    0  0.0   0:23.97 migration/2
   15 root      20   0     0    0    0 S    0  0.0   5:21.56 ksoftirqd/2
   16 root      RT   0     0    0    0 S    0  0.0   0:23.93 watchdog/2
   17 root      RT   0     0    0    0 S    0  0.0   0:18.57 migration/3
   19 root      20   0     0    0    0 S    0  0.0   5:15.37 ksoftirqd/3

в целом в данный момент показатели вроде в норме но иногда без видимой причины резко растет la и вырастает до 20, иногда до 50 и mysqltuner выдает совет увеличить

query_cache_limit = 18M
query_cache_size = 896M

которые на мой взгляд уже нереально большие обосновывая это большим количеством

Query cache prunes per day

Баз на сервере очень много, 32гб памяти, поэтому все так сложно. Подскажите, буду очень признателен за любые советы и мнения.

...

Рейтинг:

0 / 0

31.12.2015, 23:52

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141185

ScareCrow

Участник

Откуда: Белый город

Сообщения: 16 205

Рейтинг: 0 / 0

автор[!!] Joins performed without indexes: 1227
[!!] Temporary tables created on disk: 42% (12K on disk / 28K total)
запросы кривые зело

...

Рейтинг:

0 / 0

31.12.2015, 23:57

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141197

vkle

Участник

Откуда: Самара

Сообщения: 14 253

Рейтинг: 0 / 0

авторlong_query_time = 2
.....................
[--] Up for: 29m 5s (523K q [300.139 qps]
[OK] Slow queries: 1% (10K/523K)

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

Собственно, можно начинать с просмотра лога медленных запросов и их оптимизации.

...

Рейтинг:

0 / 0

01.01.2016, 01:08

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141229

VGrey

Гость

Vladimir_Prahaquery_cache_limit = 18M
query_cache_size = 896M

Владимир, ради опыта, попробуйте сделать так:

Код: sql

1.
2.

#query_cache_limit	= 18M
query_cache_size        = 32M

...

Рейтинг:

0 / 0

01.01.2016, 10:33

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141231

Vladimir_Praha

Гость

VGreyVladimir_Prahaquery_cache_limit = 18M
query_cache_size = 896M

Владимир, ради опыта, попробуйте сделать так:

Код: sql

1.
2.

#query_cache_limit	= 18M
query_cache_size        = 32M

и расскажите нам, как себя будет вести mysql? И не обращайте внимание на "Query cache prunes per day".
Вполне возможно, Вы будете приятно удивлены.

При настройке mysql нужно исходить из предпоссылки: излишне увеличенные параметры НЕ способствуют увеличению производительности поскользу вносят дополнительные накладные расходы. Насмотря на имеющуюся в системе свободную память. Например:
Vladimir_Praha[OK] Key buffer size / total MyISAM indexes: 3.0G/1.5G

Зачем Вам 3G Key buffer size, если у Вас всего индексов 1.5G??? Я бы поставил размер Key buffer size в 80% от размера индексов: в буфер должны попасть наиболее часто испульзуемые данные, а не те, которые будут нужны раз в год.

Спасибо огромное за конкретные советы, закомментировал сейчас query_cache_limit и выставил query_cache_size в 32мб, заодно Key buffer size подправил на 2гб, перезапустил и посмотрим что получится.

Еще заодно расскоментировал вот эти строчки:

interactive_timeout = 28800
wait_timeout = 28800
thread_concurrency = 10

Надеюсь что этим ничего не испорчу, но если что пишите, сразу уберу. Пока сажусь мониторить работу базы. Честно говоря устойчивой работы базы добивался но пугают "Query cache prunes per day" иногда достигающие несколько миллионов

...

Рейтинг:

0 / 0

01.01.2016, 11:32

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141232

Vladimir_Praha

Гость

Вот через 16 минут после рестарта с выше указанными параметрами, тюнер выдает такое:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.63-0+squeeze1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 11G (Tables: 3504)
[--] Data in InnoDB tables: 1G (Tables: 292)
[--] Data in MEMORY tables: 0B (Tables: 4)
[!!] Total fragmented tables: 580

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 16m 33s (321K q [323.755 qps], 12K conn, TX: 1B, RX: 40M)
[--] Reads / Writes: 95% / 5%
[--] Total buffers: 4.3G global + 2.8M per thread (300 max threads)
[OK] Maximum possible memory usage: 5.1G (16% of installed RAM)
[OK] Slow queries: 2% (8K/321K)
[OK] Highest usage of available connections: 10% (32/300)
[OK] Key buffer size / total MyISAM indexes: 2.0G/1.5G
[OK] Key buffer hit rate: 100.0% (886M cached / 192K reads)
[OK] Query cache efficiency: 59.5% (161K cached / 270K selects)
[!!] Query cache prunes per day: 5101428
[OK] Sorts requiring temporary tables: 1% (616 temp sorts / 42K sorts)
[!!] Joins performed without indexes: 916
[!!] Temporary tables created on disk: 40% (9K on disk / 22K total)
[OK] Thread cache hit rate: 99% (62 created / 12K connections)
[OK] Table cache hit rate: 25% (3K open / 15K opened)
[OK] Open file limit used: 44% (7K/16K)
[OK] Table locks acquired immediately: 99% (135K immediate / 135K locks)
[OK] InnoDB data size / buffer pool: 1.5G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_size (> 32M)
    join_buffer_size (> 256.0K, or always use indexes with joins)

Сам конфиг выглядит сейчас вот так

Код: sql

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
default-character-set = utf8

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /dev/shm
language	= /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#
# * Fine Tuning
#
key_buffer		= 2048M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 16
join_buffer_size	= 256K
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 300
table_cache            = 4096
open_files_limit	= 16192
interactive_timeout	= 28800
wait_timeout		= 28800
thread_concurrency     = 10
#
# * Query Cache Configuration
#
#query_cache_limit	= 2M
query_cache_size        = 32M
table_open_cache	= 8000
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
log_slow_queries	= /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
default-character-set = utf8
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem


tmp_table_size = 512M
max_heap_table_size = 256M
table_cache = 4048
innodb_buffer_pool_size = 2048M



[mysqldump]
quick
quote-names
max_allowed_packet	= 16M
default-character-set = utf8

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

default-character-set = utf8
[isamchk]
key_buffer		= 2048M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

В топе творится вот это

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.

top - 12:45:01 up 237 days, 19:25,  1 user,  load average: 8.10, 7.63, 7.71
Tasks: 315 total,   9 running, 306 sleeping,   0 stopped,   0 zombie
Cpu(s): 88.5%us, 10.2%sy,  0.0%ni,  1.1%id,  0.0%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  32703444k total, 30728260k used,  1975184k free,  2946000k buffers
Swap: 33553332k total,    13320k used, 33540012k free, 19487280k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
29235 mysql     20   0 4795m 2.0g 7720 S  608  6.4  72:06.89 mysqld
 2969 www-data  20   0  277m  65m 4348 S   24  0.2   0:00.73 apache2
 2971 www-data  20   0  261m  50m 4728 R   20  0.2   0:00.60 apache2
 2972 www-data  20   0  257m  45m 4212 S   20  0.1   0:00.60 apache2
 2937 www-data  20   0  470m 257m 5704 S   18  0.8   0:13.12 apache2
 2936 www-data  20   0  606m 393m 6260 S   16  1.2   0:19.21 apache2
 2964 www-data  20   0  274m  61m 5132 R   15  0.2   0:03.34 apache2
 2965 www-data  20   0  272m  60m 4420 R   14  0.2   0:00.87 apache2
 2970 www-data  20   0  277m  64m 5600 S   14  0.2   0:00.42 apache2
 2957 www-data  20   0  273m  61m 5520 S   10  0.2   0:07.87 apache2
 2966 www-data  20   0  263m  51m 4188 S    8  0.2   0:00.26 apache2
 2963 www-data  20   0  279m  65m 5408 R    5  0.2   0:02.49 apache2
 2843 www-data  20   0  287m  75m 6952 R    2  0.2   0:29.27 apache2
 2894 www-data  20   0  277m  64m 6692 R    1  0.2   0:26.59 apache2
 2895 www-data  20   0  286m  73m 6188 S    1  0.2   0:33.02 apache2
 2931 www-data  20   0  290m  76m 6064 R    1  0.2   0:12.48 apache2
 2938 www-data  20   0  267m  54m 5916 S    1  0.2   0:07.84 apache2
 2967 www-data  20   0  279m  67m 4376 S    1  0.2   0:00.45 apache2
   10 root      20   0     0    0    0 S    0  0.0   5:39.35 ksoftirqd/1
 2968 root      20   0 19256 1468  948 R    0  0.0   0:00.01 top
13276 root      20   0     0    0    0 S    0  0.0   0:01.94 kworker/2:2
26329 gitlab    20   0  943m 133m 2164 S    0  0.4 168:43.54 ruby1.9.1
28218 www-data  20   0 56732  28m 1788 S    0  0.1   1:21.58 nginx
28223 www-data  20   0 56732  28m 1792 S    0  0.1   1:32.02 nginx
28231 root      20   0 46740 9500 1008 S    0  0.0   2:21.86 ruby1.8
30054 gitlab    20   0  946m 131m 2172 S    0  0.4 168:03.79 ruby1.9.1
    1 root      20   0  8404  716  592 S    0  0.0   2:09.52 init

...

Рейтинг:

0 / 0

01.01.2016, 11:45

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141233

Vladimir_Praha

Гость

Причем я заметил что чем больше параметр query_cache_size тем позже появляются сообщения о Query cache prunes per day и тем их меньше. Но судя по наблюдениям он исчезнет если я выставлю несколько гигабайтов, что судя по мнениям которые нагуглил просто абсурд.

Может другой какой то параметр на это еще может повлиять чтобы эти prunes не лезли с такой дикой активностью?

...

Рейтинг:

0 / 0

01.01.2016, 11:49

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141234

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

Vladimir_Praha,

Joins performed without indexes: 1227 -- ВОТ ЭТО ГЛАВНОЕ!

Включаешь slow query log, включаешь логирование запросов без индексов -- и вперёд, пахать, создавать индексы.
Можно пойти и другим путём -- путём понимания -- просмотреть глазами всю схему и для каждой таблицы подумать, как с нею
будут JOIN-иться, и создать индексы на условия JOIN-а.

...

Рейтинг:

0 / 0

01.01.2016, 12:34

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141235

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

VGrey#query_cache_limit = 18M
query_cache_size = 32M

[/src]

query_cache_size надо ставить в НОЛЬ

а не в то, что вы там советуете.
Эта хрень только мешает и память жрёт.

...

Рейтинг:

0 / 0

01.01.2016, 12:36

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141239

Vladimir_Praha

Гость

Поменял в ноль, теперь картина вроде честнее выглядит

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.63-0+squeeze1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 11G (Tables: 3504)
[--] Data in InnoDB tables: 1G (Tables: 292)
[--] Data in MEMORY tables: 0B (Tables: 4)
[!!] Total fragmented tables: 580

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 51m 39s (1M q [332.063 qps], 32K conn, TX: 5B, RX: 129M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 4.3G global + 2.8M per thread (300 max threads)
[OK] Maximum possible memory usage: 5.1G (16% of installed RAM)
[!!] Slow queries: 7% (74K/1M)
[OK] Highest usage of available connections: 15% (47/300)
[OK] Key buffer size / total MyISAM indexes: 2.0G/1.5G
[OK] Key buffer hit rate: 100.0% (2B cached / 257K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 2% (4K temp sorts / 214K sorts)
[!!] Joins performed without indexes: 4153
[!!] Temporary tables created on disk: 40% (39K on disk / 96K total)
[OK] Thread cache hit rate: 99% (59 created / 32K connections)
[OK] Table cache hit rate: 24% (4K open / 16K opened)
[OK] Open file limit used: 44% (7K/16K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 1.5G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 256.0K, or always use indexes with joins)

но меня по прежнему беспокоит нагрузка на процессор дикая со стороны мускула

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.

top - 15:16:29 up 237 days, 21:57,  1 user,  load average: 12.30, 11.13, 9.00
Tasks: 309 total,   5 running, 304 sleeping,   0 stopped,   0 zombie
Cpu(s): 89.3%us,  8.8%sy,  0.0%ni,  1.6%id,  0.2%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  32703444k total, 31147140k used,  1556304k free,  3023248k buffers
Swap: 33553332k total,    21716k used, 33531616k free, 20693180k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
30112 mysql     20   0 4822m 2.1g 7900 S  572  6.7 171:45.95 mysqld
 9091 www-data  20   0  293m  78m 6192 R   23  0.2   0:20.49 apache2
 9728 www-data  20   0  269m  57m 4596 S   22  0.2   0:00.92 apache2
 9080 www-data  20   0  277m  65m 6352 S   22  0.2   0:20.39 apache2
 9719 www-data  20   0  279m  65m 5280 S   19  0.2   0:05.67 apache2
 9726 www-data  20   0  260m  46m 5684 S   19  0.1   0:02.68 apache2
 9722 www-data  20   0  273m  60m 5648 S   18  0.2   0:04.39 apache2
 9693 www-data  20   0  280m  66m 5472 S   14  0.2   0:09.81 apache2
 9727 www-data  20   0  276m  63m 5544 S   12  0.2   0:02.33 apache2
 9724 www-data  20   0  272m  61m 4532 S   12  0.2   0:04.31 apache2
 9087 www-data  20   0  275m  63m 6460 S   12  0.2   0:16.36 apache2
 9100 www-data  20   0  284m  71m 5856 R   11  0.2   0:17.04 apache2
 9730 www-data  20   0  260m  48m 4392 S   10  0.2   0:00.30 apache2
 9698 www-data  20   0  284m  71m 5404 R    7  0.2   0:09.55 apache2
 9689 www-data  20   0  277m  64m 5648 S    6  0.2   0:08.88 apache2
 9700 www-data  20   0  279m  65m 5312 S    2  0.2   0:04.66 apache2
 9701 www-data  20   0  268m  54m 5312 R    2  0.2   0:03.40 apache2
 9725 www-data  20   0  267m  54m 5520 S    2  0.2   0:01.65 apache2
 1734 gitlab    20   0  846m  96m 2480 S    0  0.3 296:12.24 ruby1.9.1
 6038 root      20   0     0    0    0 S    0  0.0   3:34.47 kworker/6:0
 9729 root      20   0 19256 1468  948 R    0  0.0   0:00.01 top
28218 www-data  20   0 56732  28m 1788 S    0  0.1   1:26.25 nginx
28223 www-data  20   0 56732  28m 1792 S    0  0.1   1:36.77 nginx
30054 gitlab    20   0  946m 131m 2176 S    0  0.4 168:14.47 ruby1.9.1
32005 root      20   0     0    0    0 S    0  0.0   0:21.96 kworker/4:0
    1 root      20   0  8404  716  592 S    0  0.0   2:09.57 init
    2 root      20   0     0    0    0 S    0  0.0   0:00.22 kthreadd
    3 root      20   0     0    0    0 S    0  0.0   9:11.43 ksoftirqd/0
    6 root      RT   0     0    0    0 S    0  0.0   0:26.26 migration/0
    7 root      RT   0     0    0    0 S    0  0.0   0:47.83 watchdog/0
    8 root      RT   0     0    0    0 S    0  0.0   0:32.45 migration/1
   10 root      20   0     0    0    0 S    0  0.0   5:39.57 ksoftirqd/1
   12 root      RT   0     0    0    0 S    0  0.0   0:31.26 watchdog/1
   13 root      RT   0     0    0    0 S    0  0.0   0:24.04 migration/2
   15 root      20   0     0    0    0 S    0  0.0   5:22.68 ksoftirqd/2

можно на это как то повлиять или с этим просто жить придется? Медленные запросы переделывать не вариант, так как сайтов огромное количество и многие из них не мои, поэтому ищу как вывернутся оптимизацией именно этих настроек (спасибо всем за понимание и заранее извиняюсь что игнорирую такие советы)

...

Рейтинг:

0 / 0

01.01.2016, 14:18

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141249

miksoft

Участник

Сообщения: 36 746

Рейтинг: 0 / 0

Vladimir_Praha,

Много ли запросов на модификацию данных?
query_cache_size не стоит делать слишком большим, т.к. его частичная очистка при модификации данных становится слишком дорогой.

Устанавливать его в ноль, имхо, имеет смысл только если доля запросов на модификацию данных очень велика.
По моим наблюдениям, во многих случаях оптимальное значение находится в районе 64-256 Мбайт.
Попробуйте его по варьировать и посмотреть на Query cache efficiency.

И нельзя ли все MyISAM-таблицы перевести в InnoDB ?

max_heap_table_size и tmp_table_size, как правило, делают одинаковыми.
Если запросы, которые создают временные таблицы на диске, не поддаются оптимизации, то эти параметры можно попробовать увеличить.
Но лучше таки попытаться оптимизировать запросы. Возможно, нужно досоздать индексов (на это, кстати, указывает тот факт, что MyISAM-индексов всего 1.5G при 11G данных) или переписать запросы.
Начните с тех запросов, который попадают в Slow queries log.

...

Рейтинг:

0 / 0

01.01.2016, 15:57

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141250

miksoft

Участник

Сообщения: 36 746

Рейтинг: 0 / 0

Еще вариант, поскольку памяти навалом, использовать Tmpfs для временных файлов MySQL.

...

Рейтинг:

0 / 0

01.01.2016, 15:58

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141251

miksoft

Участник

Сообщения: 36 746

Рейтинг: 0 / 0

Vladimir_Praha

Код: sql

1.
2.

Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with joins)

Тоже имеет смысл попробовать.
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_join_buffer_size Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible.

...

Рейтинг:

0 / 0

01.01.2016, 16:03

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141259

Vladimir_Praha

Гость

miksoftЕще вариант, поскольку памяти навалом, использовать Tmpfs для временных файлов MySQL.

они итак уже там если не ошибаюсь

join_buffer_size если начинаю увеличивать то это становится похоже на бездонную бочку, сперва пишет
join_buffer_size > 256.0K
если подкручу то
join_buffer_size > 512.0K

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

max_heap_table_size и tmp_table_size сделал одинаковыми, 512 выставил у обоих.

Еще раз спасибо большое за конкретные и дельные советы.

...

Рейтинг:

0 / 0

01.01.2016, 17:41

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141262

Vladimir_Praha

Гость

query_cache_size если делаю небольшим то очень быстро буквально за 10-20 минут появляется огромное количество prunes
а при увеличении все улучшается, по наблюдениям при параметре query_cache_size порядка 756мб или даже 1гб перестают появлятся prunes но мне субьективно кажется что сервер через сутки более туповат или просто кажется что параметр слишком завышен, но в общем боюсь пока с ним экспериментировать, поэтому лучше всего как советовали выше выставил 0 и пытаюсь решить другие узкие места.

В данное время все вроде нормально, единственно меня беспокоит большая нагрузка на процессор, топ постоянно показывает цифры в районе 300-550%, что несомненно слишком много и как эту нагрузку снять или что улучшить ума не приложу.

...

Рейтинг:

0 / 0

01.01.2016, 18:38

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141274

vkle

Участник

Откуда: Самара

Сообщения: 14 253

Рейтинг: 0 / 0

Vladimir_Prahaединственно меня беспокоит большая нагрузка на процессор, топ постоянно показывает цифры в районе 300-550%, что несомненно слишком много и как эту нагрузку снять или что улучшить ума не приложу.Лог медленных запросов смотрели? Их количество в общем потоке весьма ощутимо. Пока не разберётесь с наиболее часто повторяющимися, отжирающими значительные ресурсы, нагрузка так и будет высокой. Утилитка mysqldumpslow может оказаться весьма полезной.

Vladimir_Prahaquery_cache_size если делаю небольшим то очень быстро буквально за 10-20 минут появляется огромное количество prunes
а при увеличении все улучшается, по наблюдениям при параметре query_cache_size порядка 756мб или даже 1гб перестают появлятся prunesНе вижу смысла хранить абсолютно все данные в кеше. Особенно те, на получение которых тратится мало ресурсов. Поставьте значение в несколько мегабайт или в несколько десятков мегабайт и сохраните статистику использования кеша за пару дней. Если после двукратного увеличения размера кеша (не трогая другие параметры) ситуация в следующую пару дней заметно не улучилась, то нет смысла пытаться далее увеличивать размер.

...

Рейтинг:

0 / 0

01.01.2016, 19:57

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141302

miksoft

Участник

Сообщения: 36 746

Рейтинг: 0 / 0

Vladimir_PrahamiksoftЕще вариант, поскольку памяти навалом, использовать Tmpfs для временных файлов MySQL.они итак уже там если не ошибаюсьВозможно, я что-то пропустил, но не вижу как это следует из представленных данных.
Если оно реально так, то с загрузкой CPU, не трогая запросов и индексов, имхо, уже ничего не сделаешь. Разумеется те или иные параметры влияют на то, сколько временных файлов вывалится на диск. Но если диск находится в оперативке, то изменение этих параметров не даст существенного улучшения.

...

Рейтинг:

0 / 0

01.01.2016, 21:58

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141304

Vladimir_Praha

Гость

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.

# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/md2             1016G   30G  936G   4% /
tmpfs                  16G     0   16G   0% /lib/init/rw
udev                   10M  172K  9.9M   2% /dev
tmpfs                  16G   62M   16G   1% /dev/shm
/dev/md1              496M   53M  418M  12% /boot
/dev/md3              1.7T  676G  963G  42% /home

выше конфиг выкладывал, там указано что
tmpdir = /dev/shm

или мы о разных вещах говорим?

...

Рейтинг:

0 / 0

01.01.2016, 22:00

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141307

ScareCrow

Участник

Откуда: Белый город

Сообщения: 16 205

Рейтинг: 0 / 0

авторв буфер должны попасть наиболее часто испульзуемые данные, а не те, которые будут нужны раз в год
бред. раз в год так можно положить сервер.

...

Рейтинг:

0 / 0

01.01.2016, 22:05

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141308

ScareCrow

Участник

Откуда: Белый город

Сообщения: 16 205

Рейтинг: 0 / 0

авторquery_cache_size надо ставить в НОЛЬ

а не в то, что вы там советуете.
Эта хрень только мешает и память жрёт.
перестаньте давать явно неправильные ответы.

...

Рейтинг:

0 / 0

01.01.2016, 22:06

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141309

ScareCrow

Участник

Откуда: Белый город

Сообщения: 16 205

Рейтинг: 0 / 0

автор[OK] Query cache efficiency: 64.7% (284K cached / 439K selects)
[OK] Query cache prunes per day: 0

...

Рейтинг:

0 / 0

01.01.2016, 22:06

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141311

miksoft

Участник

Сообщения: 36 746

Рейтинг: 0 / 0

Vladimir_Prahaвыше конфиг выкладывал, там указано что
tmpdir = /dev/shmПохоже, что оно.

А занятое место меняется во время работы MySQL? Там появляются временные файлы?

...

Рейтинг:

0 / 0

01.01.2016, 22:13

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141329

Vladimir_Praha

Гость

miksoftVladimir_Prahaвыше конфиг выкладывал, там указано что
tmpdir = /dev/shmПохоже, что оно.

А занятое место меняется во время работы MySQL? Там появляются временные файлы?

да файлы появляются, занятое место постоянно меняется, но там совсем немного, от 0 до 400мб что я пока видел

...

Рейтинг:

0 / 0

01.01.2016, 23:58

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141334

Vladimir_Praha

Гость

подправил конфиг, сам обнаружил что table cache почему то дважды в конфиге фигурирует, плюс еще чуток подкрутил, перезапустил, пока все выглядит более чем ободряюще но боюсь утром опять будет куча восклицательных знаков а главное нагрузка на процессор осталась на месте, но тюнер пока что выдает вот это

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.63-0+squeeze1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 11G (Tables: 3504)
[--] Data in InnoDB tables: 1G (Tables: 292)
[--] Data in MEMORY tables: 0B (Tables: 4)
[!!] Total fragmented tables: 349

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4m 46s (116K q [408.378 qps], 6K conn, TX: 520M, RX: 13M)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 4.4G global + 6.6M per thread (300 max threads)
[OK] Maximum possible memory usage: 6.3G (20% of installed RAM)
[OK] Slow queries: 2% (3K/116K)
[OK] Highest usage of available connections: 9% (29/300)
[OK] Key buffer size / total MyISAM indexes: 2.0G/1.5G
[OK] Key buffer hit rate: 99.9% (212M cached / 130K reads)
[OK] Query cache efficiency: 57.3% (55K cached / 96K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (66 temp sorts / 13K sorts)
[!!] Joins performed without indexes: 433
[!!] Temporary tables created on disk: 40% (2K on disk / 7K total)
[OK] Thread cache hit rate: 99% (29 created / 6K connections)
[OK] Table cache hit rate: 25% (3K open / 15K opened)
[OK] Open file limit used: 44% (7K/16K)
[OK] Table locks acquired immediately: 99% (48K immediate / 48K locks)
[OK] InnoDB data size / buffer pool: 1.5G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    join_buffer_size (> 4.0M, or always use indexes with joins)

смотрю в топ и визуально кажется что база теперь меньше грузит процессор, ибо показания все чаще мелькают в диапазоне 120-300% но это все равно многовато, завтра буду думать дальше как это побороть

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

top - 01:09:47 up 238 days,  7:50,  1 user,  load average: 7.74, 10.34, 12.46
Tasks: 309 total,   8 running, 301 sleeping,   0 stopped,   0 zombie
Cpu(s): 82.6%us, 16.0%sy,  0.0%ni,  1.2%id,  0.1%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  32703444k total, 30803432k used,  1900012k free,  2031016k buffers
Swap: 33553332k total,   272364k used, 33280968k free, 20872284k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
27170 mysql     20   0 4830m 2.0g 7744 S  307  6.5  24:30.05 mysqld
31739 www-data  20   0  292m  79m 6168 R   83  0.2   0:29.98 apache2
31768 www-data  20   0  395m 181m 5540 R   70  0.6   0:05.46 apache2
31773 www-data  20   0  291m  78m 5672 S   50  0.2   0:03.74 apache2
26945 www-data  20   0  281m  67m 7364 R   39  0.2   0:33.29 apache2
31699 www-data  20   0  270m  56m 7052 R   34  0.2   0:35.32 apache2
31743 www-data  20   0  276m  64m 5652 S   32  0.2   0:10.65 apache2
31770 www-data  20   0  260m  47m 5320 S   31  0.1   0:04.04 apache2
31763 www-data  20   0  277m  63m 5616 S   27  0.2   0:12.73 apache2
31614 www-data  20   0  287m  74m 7312 R   26  0.2   2:01.64 apache2
31771 www-data  20   0  283m  68m 5236 S   25  0.2   0:02.36 apache2
31774 www-data  20   0  262m  51m 4168 R   16  0.2   0:00.48 apache2
31769 www-data  20   0  276m  63m 5316 S   15  0.2   0:03.72 a

на данный момент конфиг выглядит вот так, поэтому если у кого будут какие умные мысли буду очень признателен

Код: sql

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
default-character-set = utf8

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /dev/shm
language	= /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#
# * Fine Tuning
#
key_buffer		= 2048M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 32
join_buffer_size	= 4M
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 300
table_cache            = 12096
open_files_limit	= 10192
interactive_timeout	= 28800
wait_timeout		= 28800
thread_concurrency     = 10
#
# * Query Cache Configuration
#
#query_cache_limit	= 2M
query_cache_size        = 128M
table_open_cache	= 8000
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
log_slow_queries	= /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
default-character-set = utf8
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem


tmp_table_size = 256M
max_heap_table_size = 256M
#table_cache = 4048 dublikat zapisi - nado steret
innodb_buffer_pool_size = 2048M



[mysqldump]
quick
quote-names
max_allowed_packet	= 16M
default-character-set = utf8

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

default-character-set = utf8
[isamchk]
key_buffer		= 2048M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

...

Рейтинг:

0 / 0

02.01.2016, 00:13

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141336

Vladimir_Praha

Гость

ну вот, при кеше в 128мб уже через 15 минут полезли prunes

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 14m 53s (328K q [368.365 qps], 12K conn, TX: 1B, RX: 43M)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 4.4G global + 6.6M per thread (300 max threads)
[OK] Maximum possible memory usage: 6.3G (20% of installed RAM)
[OK] Slow queries: 2% (8K/328K)
[OK] Highest usage of available connections: 16% (49/300)
[OK] Key buffer size / total MyISAM indexes: 2.0G/1.5G
[OK] Key buffer hit rate: 100.0% (545M cached / 178K reads)
[OK] Query cache efficiency: 63.3% (176K cached / 278K selects)
[!!] Query cache prunes per day: 1996875
[OK] Sorts requiring temporary tables: 0% (141 temp sorts / 35K sorts)
[!!] Joins performed without indexes: 1069
[!!] Temporary tables created on disk: 41% (7K on disk / 18K total)
[OK] Thread cache hit rate: 99% (49 created / 12K connections)
[OK] Table cache hit rate: 25% (4K open / 15K opened)
[OK] Open file limit used: 44% (7K/16K)
[OK] Table locks acquired immediately: 99% (128K immediate / 128K locks)
[OK] InnoDB data size / buffer pool: 1.5G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_size (> 128M)
    join_buffer_size (> 4.0M, or always use indexes with joins)

Сейчас поставлю 256 но уверен что и этого будет мало и будет просить дальше, до примерно отметки в 1гб (1гб долго не гонял, возможно и там будет лезть)

...

Рейтинг:

0 / 0

02.01.2016, 00:18

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141340

Vladimir_Praha

Гость

при 256мб через 15 минут уже гораздо меньше количество prunes но они все равно есть

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 16m 9s (358K q [370.232 qps], 12K conn, TX: 2B, RX: 46M)
[--] Reads / Writes: 95% / 5%
[--] Total buffers: 4.5G global + 6.6M per thread (300 max threads)
[OK] Maximum possible memory usage: 6.4G (20% of installed RAM)
[OK] Slow queries: 1% (6K/358K)
[OK] Highest usage of available connections: 19% (59/300)
[OK] Key buffer size / total MyISAM indexes: 2.0G/1.5G
[OK] Key buffer hit rate: 100.0% (634M cached / 184K reads)
[OK] Query cache efficiency: 56.1% (172K cached / 308K selects)
[!!] Query cache prunes per day: 416396
[OK] Sorts requiring temporary tables: 0% (149 temp sorts / 48K sorts)
[!!] Joins performed without indexes: 540
[!!] Temporary tables created on disk: 41% (8K on disk / 19K total)
[OK] Thread cache hit rate: 99% (61 created / 12K connections)
[OK] Table cache hit rate: 25% (3K open / 15K opened)
[OK] Open file limit used: 44% (7K/16K)
[OK] Table locks acquired immediately: 99% (154K immediate / 154K locks)
[OK] InnoDB data size / buffer pool: 1.5G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Increasing the query_cache size over 128M may reduce performance
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_size (> 256M) [see warning above]
    join_buffer_size (> 4.0M, or always use indexes with joins)

Если поставлю 512 то они все равно появятся, но поменьше и попозже

...

Рейтинг:

0 / 0

02.01.2016, 00:36

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141342

Vladimir_Praha

Гость

вот при query_cache_size = 512M через 15 минут что тюнер показывает

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15m 20s (287K q [312.220 qps], 10K conn, TX: 1B, RX: 34M)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 4.8G global + 6.6M per thread (300 max threads)
[OK] Maximum possible memory usage: 6.7G (21% of installed RAM)
[OK] Slow queries: 1% (4K/287K)
[OK] Highest usage of available connections: 11% (33/300)
[OK] Key buffer size / total MyISAM indexes: 2.0G/1.5G
[OK] Key buffer hit rate: 100.0% (565M cached / 171K reads)
[OK] Query cache efficiency: 64.2% (157K cached / 245K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (119 temp sorts / 34K sorts)
[!!] Joins performed without indexes: 422
[!!] Temporary tables created on disk: 41% (7K on disk / 17K total)
[OK] Thread cache hit rate: 99% (33 created / 10K connections)
[OK] Table cache hit rate: 25% (3K open / 15K opened)
[OK] Open file limit used: 44% (7K/16K)
[OK] Table locks acquired immediately: 99% (101K immediate / 101K locks)
[OK] InnoDB data size / buffer pool: 1.5G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
join_buffer_size (> 4.0M, or always use indexes with joins)

но беда в том, что к утру все равно prunes полезут, пусть их меньше будет но будут. Думаю проведу эксперимент, поставлю 1гб и посмотрю что будет.

...

Рейтинг:

0 / 0

02.01.2016, 00:54

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141360

VGrey

Гость

Vladimir_Prahaвот при query_cache_size = 512M через 15 минут
...
но беда в том, что к утру все равно prunes полезут, пусть их меньше будет но будут. Думаю проведу эксперимент, поставлю 1гб и посмотрю что будет.

Vladimir_Praha , определитесь, Вам нужны красивые показатели тюнера, или быстрая работа mysql?
Еще раз повторяюсь: не смотрите на "Query cache efficiency" и "Query cache prunes per day", эти показатели были важны на системах с одним ядром. У Вас же, надеюсь, это не так?
Петр Зайцев в одном из своих выступлений говорил, что чаще всего, оптимальное значение query_cache_size меньше 128М.
Как по мне, авторитет Зайцев существенно выше Major Hayden.

...

Рейтинг:

0 / 0

02.01.2016, 09:42

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141377

Vladimir_Praha

Гость

VGreyVladimir_Prahaвот при query_cache_size = 512M через 15 минут
...
но беда в том, что к утру все равно prunes полезут, пусть их меньше будет но будут. Думаю проведу эксперимент, поставлю 1гб и посмотрю что будет.

Vladimir_Praha , определитесь, Вам нужны красивые показатели тюнера, или быстрая работа mysql?
Еще раз повторяюсь: не смотрите на "Query cache efficiency" и "Query cache prunes per day", эти показатели были важны на системах с одним ядром. У Вас же, надеюсь, это не так?
Петр Зайцев в одном из своих выступлений говорил, что чаще всего, оптимальное значение query_cache_size меньше 128М.
Как по мне, авторитет Зайцев существенно выше Major Hayden.

ядра четыре, я вас понял еще в первый раз но все пытался избавится от prunes но раз говорите игнорировать то буду игнорировать. Посмотрим что будет в долгосрочной перспективе.

Еще сейчас обнаружил странную деталь, сервер плавно начал грузится по ЛА а когда показатели стали жуткими я выключил демон сперва мускула, потом апача но картинка стала еще хуже:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.

top - 13:17:37 up 238 days, 19:58,  1 user,  load average: 99.28, 78.45, 47.06
Tasks: 386 total,   1 running, 385 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.1%sy,  0.0%ni, 86.8%id, 13.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  32703444k total, 30035752k used,  2667692k free,  3066248k buffers
Swap: 33553332k total,    22540k used, 33530792k free, 17743044k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
  576 root      20   0     0    0    0 S    0  0.0   0:04.94 kworker/0:1
 1734 gitlab    20   0  846m  96m 2480 S    0  0.3 297:23.16 ruby1.9.1
14619 www-data  20   0  274m  61m 5272 D    0  0.2   0:06.12 apache2
16814 root      20   0     0    0    0 S    0  0.0   0:08.32 kworker/0:0
24779 root      20   0     0    0    0 S    0  0.0   3:04.98 kworker/2:0
26329 gitlab    20   0  943m 133m 2152 S    0  0.4 170:21.55 ruby1.9.1
28219 www-data  20   0 56732  28m 1784 S    0  0.1   2:14.73 nginx
29135 root      20   0 19392 1616 1020 R    0  0.0   0:00.61 top
    1 root      20   0  8404  716  592 S    0  0.0   2:09.98 init
    2 root      20   0     0    0    0 S    0  0.0   0:00.22 kthreadd
    3 root      20   0     0    0    0 S    0  0.0   9:13.25 ksoftirqd/0
    6 root      RT   0     0    0    0 S    0  0.0   0:26.34 migration/0
    7 root      RT   0     0    0    0 S    0  0.0   0:48.03 watchdog/0
    8 root      RT   0     0    0    0 S    0  0.0   0:32.58 migration/1
   10 root      20   0     0    0    0 S    0  0.0   5:41.47 ksoftirqd/1
   12 root      RT   0     0    0    0 S    0  0.0   0:31.40 watchdog/1
   13 root      RT   0     0    0    0 S    0  0.0   0:24.16 migration/2
   15 root      20   0     0    0    0 S    0  0.0   5:24.50 ksoftirqd/2
   16 root      RT   0     0    0    0 S    0  0.0   0:24.09 watchdog/2
   17 root      RT   0     0    0    0 S    0  0.0   0:18.74 migration/3
   19 root      20   0     0    0    0 S    0  0.0   5:18.28 ksoftirqd/3
   20 root      RT   0     0    0    0 S    0  0.0   0:22.60 watchdog/3
   21 root      RT   0     0    0    0 S    0  0.0   0:14.01 migration/4
   23 root      20   0     0    0    0 S    0  0.0   3:48.87 ksoftirqd/4
   24 root      RT   0     0    0    0 S    0  0.0   0:23.20 watchdog/4
   25 root      RT   0     0    0    0 S    0  0.0   0:15.24 migration/5
   27 root      20   0     0    0    0 S    0  0.0   4:13.30 ksoftirqd/5
   28 root      RT   0     0    0    0 S    0  0.0   0:22.77 watchdog/5
   29 root      RT   0     0    0    0 S    0  0.0   0:12.60 migration/6
   31 root      20   0     0    0    0 S    0  0.0   4:11.91 ksoftirqd/6
   32 root      RT   0     0    0    0 S    0  0.0   0:22.10 watchdog/6
   33 root      RT   0     0    0    0 S    0  0.0   0:11.38 migration/7
   35 root      20   0     0    0    0 S    0  0.0   4:10.99 ksoftirqd/7
   36 root      RT   0     0    0    0 S    0  0.0   0:22.40 watchdog/7
   37 root       0 -20     0    0    0 S    0  0.0   0:00.00 cpuset
   38 root       0 -20     0    0    0 S    0  0.0   0:00.00 khelper
   39 root      20   0     0    0    0 S    0  0.0   0:00.00 kdevtmpfs
   40 root       0 -20     0    0    0 S    0  0.0   0:00.00 netns

Что может грузить сервер так сильно? Повторюсь что mysql и apache были выключены, никаких бэкапов не работало (проверял) и больше ничего насколько я знаю на сервере не имеется, что это могло быть? И как обнаружить причину?

...

Рейтинг:

0 / 0

02.01.2016, 12:23

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141421

vkle

Участник

Откуда: Самара

Сообщения: 14 253

Рейтинг: 0 / 0

Вероятно, кроме мускуля и апача на сервере ещё много чего работает.
автор

Код: sql

Tasks: 386 total,   1 running, 385 sleeping,   0 stopped,   0 zombie

Количество процессов сейчас стало заметно больше по сравнению с предыдущими (307-315). Обычно, на ничего не делающем сервере количество запущенных процессов не превышает сотню-полторы. Возможно, это какие-то зависшие задания, запущенные из крона. Top в таком случае мало чего покажет, посмотрите полный список процессов ps, наверняка, увидите что-то.

Может, есть смысл перезагрузить сервер, чтоб начать наблюдение "с чистого листа"?автор

Код: sql

up 238 days

...

Рейтинг:

0 / 0

02.01.2016, 15:55

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141437

Vladimir_Praha

Гость

vkleВероятно, кроме мускуля и апача на сервере ещё много чего работает.
автор

Код: sql

Tasks: 386 total,   1 running, 385 sleeping,   0 stopped,   0 zombie

Код: sql

up 238 days

спасибо большое, вы правы, перезапущу и попробую смотреть через ps

...

Рейтинг:

0 / 0

02.01.2016, 17:30

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141438

Vladimir_Praha

Гость

еще пока ковырялся в конфиге внимательно то обнаружил две строчки

table_open_cache
и
table_cache

а потом узнал что это одно и то же, просто в версии 5.13 переименовали, сейчас закомментировал одну, уже нерабочую по идее, посмотрим, если не испорчу чего :)

...

Рейтинг:

0 / 0

02.01.2016, 17:32

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141453

netwind

Участник

Сообщения: 14 195

Рейтинг: 0 / 0

VGreyПетр Зайцев в одном из своих выступлений говорил, что чаще всего, оптимальное значение query_cache_size меньше 128М.
Как по мне, авторитет Зайцев существенно выше Major Hayden.
Так этот скрипт тоже выводит рекомендацию про query_cache <= 128M. Посмотрите внимательно.
Раньше не выводил, но мне кажется уже несколько лет выводит.

Все равно эти танцы с параметрами ничего вам не дадут. Они популярны, потому что все остальные меры оптимизации сложны. Про результативность ничего не говорится в многочисленных статьях и советах.

авторЕще сейчас обнаружил странную деталь, сервер плавно начал грузится по ЛА а когда показатели стали жуткими я выключил демон сперва мускула, потом апача но картинка стала еще хуже:
Теперь видно, что процессы ruby "сбесились". Ребут - неплохая идея.

...

Рейтинг:

0 / 0

02.01.2016, 19:16

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141523

VGrey

Гость

Vladimir_Praha , Прочесс оптимизации чего-либо, в частности, mysql, должен состоять из нескольких шагов, например:

1) Определяем цель(цели).
Например:
- максимально быстрая работа;
- минимальная нагрузка на систему;
- максимальное соответствие рекомендациям mysqltuner.
Обратите внимание, все три приведенные для примера цели могут противоречить друг дгугу. Думаю, очевидно, что максимально быстрая работа и минимальная нагрузка взаимо исключающие требования
Еще нюанс - скорее всего, целей будет несколько и нужно будет расставить приоритеты между ними. Например, минимальную нагрузку можно получить просто выключив мскул.

2) Если цели поставлены, определяемся с механизмами контроля достижения цели.
Чем, когда и в каких единицах измерять нагрузку на систему? Речь идет о нагрузке на проц, на дисковую систему, на память, еще на что?
Чем измерять быстродействие mysql? sysbench, скорость генерации страницы, наличие и количество записей в log_slow_queries, еще какой способ?

3) Измеряем текущее состояние и проверяем достижение цели. Может, у нас и так все в лучшем виде и ничего больше делать не нужно.

4) Выдвигаем предположение.
Наприер, предположение: mysql работает меделнно по причине локов в слишком большом query_cache.

5) Меняем соответствующий параметр.

6) Проверяем правильность предположения - то есть, переходим к шагу 3).

Vladimir_Praha , что Вы хотите получить от mysql? Как Вы измеряете эту хотелку? Не субьективно ли, случаем?

И последний вопрос: причина Ваших проблем точно в mysql?

...

Рейтинг:

0 / 0

03.01.2016, 09:10

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141808

Vladimir_Praha

Гость

VGrey,

Хороший вопрос, изначально было два сервера, но в связи с кризисом и прочими событиями с рублем один значительно прохудился и было принято решение свезти все хозяйство на один сервер. После того как перевезли 2/3 хозяйства заметили что LA с обычных 3-4 резко подскочил до 30-40 что и вызвало беспокойства и попытки выяснить где слабое место.

Так как в топе из всех процессов только мускул всегда был лидером с его 300-550% загрузкой процессора то и решения где искать слабое место собственно не было - взор пал автоматически туда. Да и остальное (апач и нгинкс не вызывали причин для беспокойства) честно говоря.

После была взята утилита mysqltuner и была предпринята попытка соответствовать ее требованиям, но в процессе обсуждений я разузнал очень много в том числе и то, что она не показатель идеальности настроек мускула, в итоге я вчера игрался с настройками кэша пытаясь его обуздать но в итоге от этих манипуляций отказался и просто слегка увеличил join_buffer_size до 24мб.

Ну и перезагрузил систему как тут советовали. В итоге на сервере порядка 200 вордпресс блогов, на каждый еще в течение сегодняшнего дня был установлен кэширующий плагин показатели следующие:

top - 02:25:00 up 4:30, 1 user, load average: 5.79, 5.55, 5.48
Tasks: 162 total, 6 running, 155 sleeping, 0 stopped, 1 zombie
Cpu(s): 34.4%us, 12.2%sy, 0.0%ni, 52.2%id, 1.1%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 32703444k total, 31806544k used, 896900k free, 2569504k buffers
Swap: 33553332k total, 3216k used, 33550116k free, 21218508k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2632 mysql 20 0 4934m 4.6g 12m S 105 14.8 518:41.60 mysqld
31002 www-data 20 0 290m 76m 7168 R 55 0.2 0:31.10 apache2
32554 www-data 20 0 0 0 0 Z 47 0.0 0:03.91 apache2 <defunct>
32544 www-data 20 0 274m 62m 6348 S 23 0.2 0:10.29 apache2
32526 www-data 20 0 266m 54m 6628 S 19 0.2 0:24.24 apache2
32530 www-data 20 0 289m 73m 5612 S 19 0.2 0:12.70 apache2
32552 www-data 20 0 265m 54m 6144 R 18 0.2 0:06.92 apache2
32556 www-data 20 0 277m 63m 5448 S 18 0.2 0:01.81 apache2
32417 www-data 20 0 276m 64m 7280 R 17 0.2 0:55.38 apache2
32549 www-data 20 0 266m 54m 5832 R 15 0.2 0:07.54 apache2
32514 www-data 20 0 283m 72m 7236 S 9 0.2 0:32.93 apache2
32550 www-data 20 0 267m 54m 5744 S 9 0.2 0:04.02 apache2
32528 www-data 20 0 276m 64m 6500 S 7 0.2 0:21.35 apache2
32448 www-data 20 0 278m 66m 6784 S 7 0.2 0:53.80 apache2
32527 www-data 20 0 275m 62m 6284 S 2 0.2 0:19.64 apache2
31876 www-data 20 0 276m 63m 7272 S 1 0.2 0:52.85 apache2
227 root 20 0 0 0 0 S 0 0.0 0:05.81 kworker/4:1
1305 redis 20 0 26212 1588 680 S 0 0.0 0:02.50 redis-server
1809 gitlab 20 0 919m 104m 2920 S 0 0.3 0:29.54 ruby1.9.1
8576 www-data 20 0 45996 17m 1824 S 0 0.1 0:06.44 nginx
8578 www-data 20 0 45920 17m 1804 S 0 0.1 0:06.52 nginx
31858 www-data 20 0 277m 63m 7288 S 0 0.2 0:48.72 apache2
32547 www-data 20 0 273m 61m 6176 S 0 0.2 0:07.81 apache2
32555 root 20 0 19120 1340 948 R 0 0.0 0:00.01 top
1 root 20 0 8404 704 668 S 0 0.0 0:01.22 init
2 root 20 0 0 0 0 S 0 0.0 0:00.00 kthreadd

еще я выяснил что для 8-ядерного процессора показатель ла 5.5 это отлично ибо по единичке идет на каждый процессор следовательно для чистоты нужно 5.5 делить на 8 чтобы получить картинку от обычного ла, так что в остальном все уже
просто замечательно и дальше просто буду мониторить состояние в долгосрочной перспективе.

Еще есть с десяток блогов где базы больших размеров (от 500мб до 1.7гб) и сейчас я в раздумьях если сконвертировать майисам в иннодб снизит ли это нагрузку на процессор или нет, но это уже другой вопрос из другой оперы, поэтому мой вопрос похоже можно закрывать. Кому если будет любопытно могу еще раз скинуть уже готовый конфиг и показатели тюнера но я так понял все это сугубо индивидуально всегда. Посему еще раз всем большое спасибо за помощь и советы!

...

Рейтинг:

0 / 0

04.01.2016, 01:28

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141818

vkle

Участник

Откуда: Самара

Сообщения: 14 253

Рейтинг: 0 / 0

Vladimir_Prahaизначально было два сервера, но в связи с кризисом и прочими событиями с рублем один значительно прохудился и было принято решение свезти все хозяйство на один сервер.Не понятно, что означает термин "прохудился". Аргументы про кризисы и рубли практически не влияют на работоспособность серверов. А если и влияют, то экономия на грамотных программистах и толковых админах только усугубляет ситуацию, вынуждая приобретать ещё более мощный сервер. Тут либо экономить на всём подряд и уронить, либо экономить грамотно и осторожно. Разумеется, если каждый из двух серверов был очень слабо (менее, чем на 50%) загружен до стаскивания всего хозяйства на один из них, то можно рассматривать вариант стащить всё в одно место, но с большой оглядкой.

Мне не совсем понятно ещё Ваше нежелание порыться в логе медленных запросов. Тут надо принять во внимание вот какой момент. Когда ЛА превышает количество ядер, а сами процессы долгоиграющие, то легко поставить сервер колом. Например, как уже было показано выше, на примере мускуля, за секунду в среднем отрабатывают пять запросов длительностью более двух секунд, то четырёх ядер для этого в долговременной перспективе недостаточно. Исключение - если все (или бОльшая часть) долгоиграйки работают от одного mysql-пользователя и аккуратненько встают в очередь выполнения. Если же они раскиданы по многим пользователям, то очереди не будет. В любом случае, есть смысл смотреть, кто конкретно "злоупотребляет" и принимать к нему меры. Даже, если это всего один пользователь из сотни - негоже ему одному отдавать целое процессорное ядро под его тормоза. А будет второй такой же злоупотребитель - ему второе... Иногда для исправления ситуации достаточно правильно поставить индексы.

Далее. Предположим, что на каждом из 4-ядерных серверов до перетаскивания в среднем нормально работали, скажем грубо, две сотни пользовательских процессов + сотня для всяких системных служб. Итого 300, а одно ядро должно переключаться, образно говоря, между 75 процессами, выделяя каждому из них какие-то кванты времени. После переезда количество пользовательских удвоится и в сумме будет 500, значит, на одно ядро будет приходиться уже 125 процессов. Штука в том, что переключение ядра между контекстами процессов тоже требует некоторых затрат времени. Таким образом, можно вполне нарваться на ситуацию, когда до переезда загрузка каждого сервера была менее 50%, а после переезда стала более 100%. Это очень упрощенное и грубое описание, конечно. Если упрощенно, то при неизменной очереди входящих запросов серверные процессы не будут успевать отрабатывать в приемлемый отрезок времени. Отсюда и лавинообразное возрастание ЛА возможно вполне. Ах да, ещё и благодарные пользователи, не получив в приемлемое время свою страничку, начнут F5 жмакать, отправляя дополнительные запросы...

Vladimir_Prahaеще я выяснил что для 8-ядерного процессора показатель ла 5.5 это отлично ибо по единичке идет на каждый процессорВ общем, да, для долговременной работы можно ориентироваться на такие или немного большие цифры. Конечно, в реальной жизни вполне могут быть относительно безболезненные кратковременные скачки до 15...20 (первое число в ЛА).

Vladimir_PrahaЕще есть с десяток блогов где базы больших размеров (от 500мб до 1.7гб) и сейчас я в раздумьях если сконвертировать майисам в иннодб снизит ли это нагрузку на процессор или нетЕсли размер ОЗУ позволяет держать все таблицы целиком в памяти, то можно ожидать разгрузки дискового ввода/вывода (не придётся при чтении лазить по каждому чиху на диск) и некоторой экономии на понижении времени ожидания дисковых операций. Однако, сложно сказать, действительно ли при майисаме происходит фактическое обращение к диску при выполнении запроса. Результат то может быть и из кеша запросов отдан или таблица столь популярна, что не успевает вымываться из дискового буфера. Тут надо очень тщательно анализировать перед изменением типа таблицы. Иннодб работает медленнее не просто так. :)

...

Рейтинг:

0 / 0

04.01.2016, 03:20

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141821

miksoft

Участник

Сообщения: 36 746

Рейтинг: 0 / 0

vkleИсключение - если все (или бОльшая часть) долгоиграйки работают от одного mysql-пользователя и аккуратненько встают в очередь выполнения.В MySQL разве есть такой механизм?

...

Рейтинг:

0 / 0

04.01.2016, 03:29

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141829

javajdbc

Участник

Откуда: Montreal

Сообщения: 18 207

Рейтинг: 0 / 0

...

Рейтинг:

0 / 0

04.01.2016, 04:40

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141831

javajdbc

Участник

Откуда: Montreal

Сообщения: 18 207

Рейтинг: 0 / 0

Vladimir_Praha,

Без контроля за инспользованием ресурсов пользователями --
вы не можете гарантировать стабильную работу системы.
Всегда найдется пользователь-умник который простейшим
кодом или запросом положит mysql, RoR, ngnix и apache2
всместе взятые.

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

Контроль начинается с мониторинга расхода ресурсов.
top (htop) , mysqlturner -- неплохое начало, добавьте
mytop, iotop, monit, New Relic APM, gem install god.

Но главное -- контроль над конкретнимы аккаунатми.
Вам 10 раз уже сказали посмотрите в slow query log.
там есть юзер наме -- т.е. вы поймете кто и каким образом
ставит палки в колеса вашей большой телеге.
Разгильдяев будет не так много -- их можно просто выселить,
посадить отлучить от сервера на 15 суток -- ну на что у вас
силенок хватит.
Возможно проблему можно будет решить если стукнуть рельсой по руби
девелоперам, которые безалаберно используют дата акксесс фрейвок.

И еше вариант активного контроля -- ставьте лимит на
длину запросов -- убивать нафик все что длится более, допустим 5 секунд.
систему можно будет подстроить разрешеним конкретным юзерам
гонять длинные квери -- если докажут что им реально надо и они
полностью оптимизировали запросы.

Вы можете постить лимиты за количетво одновременных
соединений на юзера, на количество запросов на юзера в час,
убивать зависшие коннекции... если чё, вам
еше идей накидают тутошние опытные админы.

Главное -- чтоб вы начали активно контролировать систему
а не гадать на top-e...

Успехов.

...

Рейтинг:

0 / 0

04.01.2016, 05:13

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39141918

netwind

Участник

Сообщения: 14 195

Рейтинг: 0 / 0

javajdbcmiksoftпропущено...
В MySQL разве есть такой механизм?

http://dev.mysql.com/doc/refman/5.7/en/user-resources.html
Так запросы в очередь не встают. Ошибка возникает. Страничка не отобразится.

Я такие темы много раз видел. Никакого толка тут не будет.
Программисту можно что-то советовать. Они воспринимают процесс как взаимодействие программ и готовы разбираться .
Бызнысмены-сеонизаторы надеются что сейчас посоветуют волшебное решение и бабло потечет.

...

Рейтинг:

0 / 0

04.01.2016, 12:44

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39142125

vkle

Участник

Откуда: Самара

Сообщения: 14 253

Рейтинг: 0 / 0

miksoftvkleИсключение - если все (или бОльшая часть) долгоиграйки работают от одного mysql-пользователя и аккуратненько встают в очередь выполнения.В MySQL разве есть такой механизм?В чистом виде нет вроде, а на практике получается нечто вроде очереди. Насколько понимаю, это происходит по вот каким причинам. Во первых, из-за блокировок таблиц (типично, например, селект будет ждать снятия блокировки таблицы, которую установил перед выполнением апдейт). Во-вторых (тут могу ошибаться, поправьте), запросы с разных коннектов могут выполняться параллельно, но какие-то запросы отрабатывают, а заведомо тормозные впираются в блокировку той же самой таблицы, которую залочил такой же запрос от другого коннекта.

...

Рейтинг:

0 / 0

04.01.2016, 20:41

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39142144

netwind

Участник

Сообщения: 14 195

Рейтинг: 0 / 0

vkle, в стандартной схеме хостинга это обеспечивает nginx и apache с небольшим значением настройки MaxClients.

...

Рейтинг:

0 / 0

04.01.2016, 21:50

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39142254

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

Vladimir_Praha
можно на это как то повлиять или с этим просто жить придется? Медленные запросы переделывать не вариант, так как сайтов огромное количество и многие из них не мои, поэтому ищу как вывернутся оптимизацией именно этих настроек (спасибо всем за понимание и заранее извиняюсь что игнорирую такие советы)

нет, это как раз и есть единственный вариант.

...

Рейтинг:

0 / 0

05.01.2016, 07:40

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39142255

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

ScareCrowавторquery_cache_size надо ставить в НОЛЬ

а не в то, что вы там советуете.
Эта хрень только мешает и память жрёт.
перестаньте давать явно неправильные ответы.
чего тут неправильного?
нафиг эту хрень вообще нужна?
какой идиот ее придумал?
если тебе надо 200 раз один и тот же запрос делать, то данные нужно кэшировать в приложении, а не в СУБД, чтобы запрос вообще не делать 200 раз.

память в СУБД нужно на кэш данных тратить,
а не на всякую хрень.

...

Рейтинг:

0 / 0

05.01.2016, 07:46

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39142257

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

Vladimir_Praha,
еще раз нужно отметить, если еще не прозвучало, что высокая нагрузка CPU от СУБД - это хорошо, а не плохо. это значит, что СУБД имеет все данные в кешах , не висит на IO и при наличии запросов на входе достаточно эффективно работает.
С самой высокой загрузкой cpu бороться не нужно, нужно искать медленные запросы и оптимизировать их, например, создавая под них индексы.
а под это уже тут писали, как делать.

...

Рейтинг:

0 / 0

05.01.2016, 07:55

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39142271

Vladimir_Praha

Гость

спасибо всем большое за советы, ушел в slow_logs

...

Рейтинг:

0 / 0

05.01.2016, 09:40

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39142297

netwind

Участник

Сообщения: 14 195

Рейтинг: 0 / 0

MasterZivScareCrowпропущено...

перестаньте давать явно неправильные ответы.
чего тут неправильного?
нафиг эту хрень вообще нужна?
какой идиот ее придумал?

Действительно, эта фича уникальна для РСУБД.
Как можно было не догадаться ее везде сделать ?

У многих окупается, что бы вы там себе не думали.

...

Рейтинг:

0 / 0

05.01.2016, 11:13

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39142563

ScareCrow

Участник

Откуда: Белый город

Сообщения: 16 205

Рейтинг: 0 / 0

netwindMasterZivпропущено...

чего тут неправильного?
нафиг эту хрень вообще нужна?
какой идиот ее придумал?

Действительно, эта фича уникальна для РСУБД.
Как можно было не догадаться ее везде сделать ?

У многих окупается, что бы вы там себе не думали.

http://www.oracle.com/technetwork/articles/sql/11g-caching-pooling-088320.html

...

Рейтинг:

0 / 0

06.01.2016, 00:44

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39142577

javajdbc

Участник

Откуда: Montreal

Сообщения: 18 207

Рейтинг: 0 / 0

MasterZiv...то данные нужно кэшировать в приложении, а не в СУБД...

...проблема в инвалидации кеша в момент изменении данных в базе...

внутри базы каш сначала проверяется на валидность а потом --
если не было апдейтов -- выдается вместо полного запроса...

кеширование на клиенте -- тоже полезная штука если
бизнес задача разрешает отлючится на определенное
время (ttl) от меняюшихся данных.

Говорить что одно решение лучше чем друго -- бессмыслено.
разные задачи -- разные решение.

...

Рейтинг:

0 / 0

06.01.2016, 03:29

| Ответить | Цитировать | Написать

Оптимизация MySQL по результатам mysqltuner.pl

#39142619

netwind

Участник

Сообщения: 14 195

Рейтинг: 0 / 0

ScareCrownetwindпропущено...

Действительно, эта фича уникальна для РСУБД.
Как можно было не догадаться ее везде сделать ?

У многих окупается, что бы вы там себе не думали.

http://www.oracle.com/technetwork/articles/sql/11g-caching-pooling-088320.html
ну вот я и говорю : как можно было только лишь через 20 лет развития oracle решиться скопировать эту фичу у mysql ?

...

Рейтинг:

0 / 0

06.01.2016, 10:24

| Ответить | Цитировать | Написать

50 сообщений из 50, показаны все 2 страниц

все

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация MySQL по результатам mysqltuner.pl

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?all=1&fid=47&tid=1832312]:	0ms
get settings:	7ms
get forum list:	10ms
check forum access:	2ms
check topic access:	2ms
track hit:	169ms
get topic data:	8ms
get forum data:	2ms
get page messages:	63ms
get tp. blocked users:	1ms
others:	207ms

total:	471ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы