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

ReSQL.ru

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

Гость

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

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

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

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

25 сообщений из 50, страница 1 из 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

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

25 сообщений из 50, страница 1 из 2

все

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

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

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

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

start [/forum/topic.php?fid=47&msg=39141309&tid=1832312]:	0ms
get settings:	7ms
get forum list:	14ms
check forum access:	3ms
check topic access:	3ms
track hit:	160ms
get topic data:	6ms
get forum data:	1ms
get page messages:	36ms
get tp. blocked users:	1ms
others:	216ms

total:	447ms

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