Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация работы MySQL при выборке 50кк строк / 25 сообщений из 33, страница 1 из 2
  1  все
09.10.2015, 11:34:21
    #39072746
Electronnnnn
Electronnnnn
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
Здравствуйте,

возникла проблема - при сложном запросе он выполняется порядка 11-15 секунд, требуется помощь в оптимизации конфигурации MySQL без изменения запроса или кода движка.

После перезагрузки демона MySQL в течение 3-4 часов запрос выполняется за 1-2 секунды, далее стандартные 11-15 секунд.

Прошу сильно не ругать за вывернутые настройки буферов в my.cnf , потому как через них ищется узкое место.

БД - InnoDB

MySQL : mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

CPU : 8
RAM : 32Gb
БД : 9Gb

slow.log :
Код: plsql
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.
# Time: 151009 11:18:46
# User@Host: X[X] @ localhost []
# Query_time: 11.432807  Lock_time: 0.000096 Rows_sent: 267  Rows_examined: 52616886
SET timestamp=1444378726;
SELECT
                                task.id AS task_id

                        FROM streb_task AS task

                        INNER JOIN streb_item AS item ON task.id = item.id
                        INNER JOIN streb_person AS person ON item.created_by = person.id
                        INNER JOIN streb_project AS project ON item.project = project.id
                        INNER JOIN streb_taskperson AS taskperson ON taskperson.task = task.id
                        INNER JOIN streb_item AS item2 ON taskperson.id = item2.id
            INNER JOIN streb_projectperson AS projectperson ON projectperson.project = project.id
            INNER JOIN streb_item AS item3 ON projectperson.id = item3.id

                        WHERE

                                (taskperson.person = 199129 OR (projectperson.person = 199129 AND projectperson.is_pm = 1 ) OR project.id in (SELECT project_id FROM streb_task_notifications_users WHERE people_id = 199129) OR task.id IN (SELECT task_id FROM streb_task_notifications_users WHERE people_id = 199129))
                                AND item.pub_level >= 4
                                AND item2.state = 1
                                AND item3.state = 1
                                AND item.state = 1
                                AND task.status NOT IN (4, 8)

                        GROUP BY task.id;



profile :
Код: plsql
1.
|        3 | 12.01711100 | SELECT                                 task.id AS task_id                          FROM streb_task AS task                          INNER JOIN streb_item AS item ON task.id = item.id                         INNER JOIN streb_person AS person ON item.created_by = person.id                         INNE |


Код: plsql
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.
+-------------------------------+----------+
| Status                        | Duration |
+-------------------------------+----------+
| Sending data                  | 0.000285 |
| executing                     | 0.000001 |
| Sending data                  | 0.000284 |
| executing                     | 0.000001 |
| Sending data                  | 0.000284 |
| executing                     | 0.000002 |
| Sending data                  | 0.000284 |
| executing                     | 0.000002 |
| Sending data                  | 0.000283 |
| executing                     | 0.000001 |
| Sending data                  | 0.000286 |
| executing                     | 0.000002 |
| Sending data                  | 0.000285 |
| executing                     | 0.000002 |
| Sending data                  | 0.000282 |
| executing                     | 0.000002 |
| Sending data                  | 0.000287 |
| executing                     | 0.000002 |
| Sending data                  | 0.000288 |
| executing                     | 0.000002 |
| Sending data                  | 0.000282 |
| executing                     | 0.000001 |
| Sending data                  | 0.000284 |
| executing                     | 0.000002 |
| Sending data                  | 0.000283 |
| executing                     | 0.000001 |
| Sending data                  | 0.000282 |
| executing                     | 0.000002 |
| Sending data                  | 0.000284 |
| executing                     | 0.000001 |
| Sending data                  | 0.000283 |
| executing                     | 0.000001 |
| Sending data                  | 0.000285 |
| executing                     | 0.000001 |
| Sending data                  | 0.000282 |
| executing                     | 0.000002 |
| Sending data                  | 0.000282 |
| executing                     | 0.000002 |
| Sending data                  | 0.000284 |
| executing                     | 0.000001 |
| Sending data                  | 0.000284 |
| executing                     | 0.000002 |
| Sending data                  | 0.000283 |
| executing                     | 0.000001 |
| Sending data                  | 0.000285 |
| executing                     | 0.000001 |
| Sending data                  | 0.000285 |
| executing                     | 0.000002 |
| Sending data                  | 0.000282 |
| executing                     | 0.000001 |
| Sending data                  | 0.000284 |
| executing                     | 0.000002 |
| Sending data                  | 0.000287 |
| executing                     | 0.000001 |
| Sending data                  | 0.000284 |
| executing                     | 0.000002 |
| Sending data                  | 0.000282 |
| executing                     | 0.000002 |
| Sending data                  | 0.000281 |
| executing                     | 0.000002 |
| Sending data                  | 0.000289 |
| executing                     | 0.000002 |
| Sending data                  | 0.000286 |
| executing                     | 0.000002 |
| Sending data                  | 0.000283 |
| executing                     | 0.000002 |
| Sending data                  | 0.000285 |
| executing                     | 0.000001 |
| Sending data                  | 0.000286 |
| executing                     | 0.000002 |
| Sending data                  | 0.000294 |
| executing                     | 0.000002 |
| Sending data                  | 0.000151 |
| executing                     | 0.000001 |
| Sending data                  | 0.000145 |
| executing                     | 0.000002 |
| Sending data                  | 0.000145 |
| executing                     | 0.000002 |
| Sending data                  | 0.000144 |
| executing                     | 0.000002 |
| Sending data                  | 0.000142 |
| executing                     | 0.000002 |
| Sending data                  | 0.000144 |
| executing                     | 0.000002 |
| Sending data                  | 0.000144 |
| executing                     | 0.000002 |
| Sending data                  | 0.000144 |
| executing                     | 0.000002 |
| Sending data                  | 0.000155 |
| Sorting result                | 0.000033 |
| Sending data                  | 0.000035 |
| end                           | 0.000003 |
| removing tmp table            | 0.000025 |
| end                           | 0.000005 |
| query end                     | 0.000003 |
| freeing items                 | 0.000023 |
| storing result in query cache | 0.000010 |
| logging slow query            | 0.000002 |
| logging slow query            | 0.000037 |
| cleaning up                   | 0.000004 |
+-------------------------------+----------+
100 rows in set (0.00 sec)



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.
[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock


[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16000M
max_allowed_packet = 6400M
sort_buffer_size = 20480M
read_buffer_size = 20480M
read_rnd_buffer_size = 2560M
net_buffer_length = 200M
thread_stack = 192K
max_connections = 400
table_open_cache = 512
query_cache_size = 20240M
query_cache_type = 1
thread_cache_size = 8
tmp_table_size = 2560M
max_heap_table_size = 256M
join_buffer_size = 20480M
table_cache = 128
query_cache_limit = 10000M
open_files_limit = 8192
long_query_time = 4
log-slow-queries = /var/log/mysql.slow.log
log_error = /var/log/mysql.err
innodb_thread_concurrency = 0
server-id       = 1
innodb_buffer_pool_size = 20480M
innodb_additional_mem_pool_size = 20M
innodb_file_io_threads = 8
innodb_log_buffer_size = 10000M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_flush_method = O_DIRECT
transaction-isolation = READ-COMMITTED

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 2560M
sort_buffer_size = 25600M

[mysqlhotcopy]
interactive-timeout



Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.10.2015, 11:42:50
    #39072760
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
Electronnnnn5.1.73
Electronnnnn
Код: plsql
1.
WHERE <...> project.id in (SELECT <...>

Удивительно, что у вас хоть когда-то получается 1-2 секунды.
А вообще стоило бы ещё и план выполнения показать. Или планы, если они различаются.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.10.2015, 11:52:18
    #39072766
Electronnnnn
Electronnnnn
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
tanglir,

explain :
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+----+--------------------+--------------------------------+----------------+---------------------------------------+---------+---------+------------------------------------+------+----------------------------------------------+
| id | select_type        | table                          | type           | possible_keys                         | key     | key_len | ref                                | rows | Extra                                        |
+----+--------------------+--------------------------------+----------------+---------------------------------------+---------+---------+------------------------------------+------+----------------------------------------------+
|  1 | PRIMARY            | task                           | range          | PRIMARY,status                        | status  | 1       | NULL                               | 3082 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | taskperson                     | ref            | PRIMARY,person,task                   | task    | 4       | X.task.id               |    2 |                                              |
|  1 | PRIMARY            | item                           | eq_ref         | PRIMARY,project,state                 | PRIMARY | 4       | X.task.id               |    1 | Using where                                  |
|  1 | PRIMARY            | projectperson                  | ref            | PRIMARY,project,person,person_2,is_pm | project | 4       | X.item.project          |   11 | Using where                                  |
|  1 | PRIMARY            | person                         | eq_ref         | PRIMARY                               | PRIMARY | 4       | X.item.created_by       |    1 | Using index                                  |
|  1 | PRIMARY            | project                        | eq_ref         | PRIMARY                               | PRIMARY | 4       | X.projectperson.project |    1 | Using where; Using index                     |
|  1 | PRIMARY            | item2                          | eq_ref         | PRIMARY,state                         | PRIMARY | 4       | X.taskperson.id         |    1 | Using where                                  |
|  1 | PRIMARY            | item3                          | eq_ref         | PRIMARY,state                         | PRIMARY | 4       | X.projectperson.id      |    1 | Using where                                  |
|  3 | DEPENDENT SUBQUERY | streb_task_notifications_users | index_subquery | task_id                               | task_id | 10      | func,const                         |    1 | Using index; Using where                     |
|  2 | DEPENDENT SUBQUERY | streb_task_notifications_users | ALL            | NULL                                  | NULL    | NULL    | NULL                               | 1252 | Using where                                  |
+----+--------------------+--------------------------------+----------------+---------------------------------------+---------+---------+------------------------------------+------+----------------------------------------------+
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.10.2015, 12:27:06
    #39072825
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
Ну, что и ожидалось, DEPENDENT SUBQUERY, которые НСД ни разу не dependent.
Насчёт оптимизации самого запроса - я вижу только 2 пути, оба не особо хорошие:
1)расписать запрос в несколько запросов, объединённых юнионом, заменив в каждом подзапрос на join:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SELECT <...>
WHERE (taskperson.person = 199129 OR (projectperson.person = 199129 AND projectperson.is_pm = 1 ) 
AND item.pub_level >= 4
<...>
union
SELECT <...>
JOIN (SELECT project_id FROM streb_task_notifications_users WHERE people_id = 199129) t0 on task.project_id=t0.project_id
WHERE
item.pub_level >= 4
<...>
union
SELECT <...>
JOIN (SELECT task_id FROM streb_task_notifications_users WHERE people_id = 199129) t0 on task.task_id=t0.task_id
WHERE
item.pub_level >= 4
<...>

Плюс - нет depented squbquery, минус - запрос, хоть он и быстрей, но выполняется трижды. Что выгодней - надо смотреть.
2)Сначала выполнять эти подзапросы, потом собирать результирующий запрос с прямой подстановкой полученных списков ид-ов. Плюс тот же, минусы - вынос логики работы с данными в приложение (впрочем, можно всё сделать на сервере через prep statement...).

ЗЫ. Странно, конечно, что сначала всё работает относительно быстро... вы не пробовали после замедления пересобрать статистику по используемым таблицам?
ЗЗЫ. Архистранно, что один из подзапросов использует индекс, а другой нет, хотя различаются они только одним получаемым полем. Это точно эксплейн этого запроса? :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.10.2015, 12:40:38
    #39072837
Electronnnnn
Electronnnnn
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
tanglirНасчёт оптимизации самого запроса - я вижу только 2 пути, оба не особо хорошие:
К сожалению, на данном этапе запрос не переписать, требуется помощь или совет, каким образом можно оптимизировать именно сам MySQL сервер для более быстрой обработки.

tanglirЗЫ. Странно, конечно, что сначала всё работает относительно быстро... вы не пробовали после замедления пересобрать статистику по используемым таблицам?
Каким образом это можно сделать ?

tanglirЗЗЫ. Архистранно, что один из подзапросов использует индекс, а другой нет, хотя различаются они только одним получаемым полем. Это точно эксплейн этого запроса? :)
К сожалению, да.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.10.2015, 12:52:18
    #39072854
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
ElectronnnnnКаким образом это можно сделать ? https://dev.mysql.com/doc/refman/5.0/en/analyze-table.html
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2015, 08:34:34
    #39073416
VGrey
VGrey
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
Electronnnnn ,
1) покажите, пожалуйста из вывода mysqltuner.pl секции "Storage Engine Statistics" и "Performance Metrics". Только эти, остальное неинтересно;
2) покажите вывод tuning-primer.sh;
3) какая у Вас ОС, есть ли у Вас возможность заменить mysql на более новый или на Percona, Maria?

Если мне не изменяет мой склероз, в mysql 5.1 с чем то похожим на Вашу ситуацию встречался. А в более поздних - такого не припомню..

---
Виктор
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2015, 13:11:35
    #39073510
Electronnnnn
Electronnnnn
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
VGrey1) покажите, пожалуйста из вывода mysqltuner.pl секции "Storage Engine Statistics" и "Performance Metrics". Только эти, остальное неинтересно;

Код: sql
1.
2.
3.
4.
5.
6.
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 1G (Tables: 5124)
[--] Data in InnoDB tables: 7G (Tables: 7384)
[--] Data in MEMORY tables: 0B (Tables: 17)
[!!] Total fragmented tables: 7724


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 1h 12m 12s (10M q [113.105 qps], 27K conn, TX: 26B, RX: 1B)
[--] Reads / Writes: 91% / 9%
[--] Binary logging is disabled
[--] Total buffers: 65.4G global + 44.0G per thread (400 max threads)
[!!] Maximum reached memory usage: 2705.4G (8665.43% of installed RAM)
[!!] Maximum possible memory usage: 17665.5G (56582.07% of installed RAM)
[OK] Slow queries: 0% (4K/10M)
[OK] Highest usage of available connections: 15% (60/400)
[OK] Aborted connections: 1.20%  (336/27901)
[OK] Query cache efficiency: 83.5% (8M cached / 9M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 650K sorts)
[!!] Joins performed without indexes: 3011
[!!] Temporary tables created on disk: 40% (42K on disk / 106K total)
[OK] Thread cache hit rate: 97% (732 created / 27K connections)
[!!] Table cache hit rate: 0% (2K open / 765K opened)
[OK] Open file limit used: 35% (2K/8K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)


По MyISAM проходился "mysqlcheck -o " , знаю, что InnoDB так не сделать и нужно делать дамп бд, удалять и создавать заново. Сильно ли влияет в данном случае фрагментация на производительность, если после перезапуска в течение некоторого времени проблемный запрос выполняется буквально за 1-2 секунды ?
VGrey2) покажите вывод tuning-primer.sh;

Код: 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.
        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.1.73-log x86_64

Uptime = 1 days 1 hrs 7 min 36 sec
Avg. qps = 113
Total Questions = 10247959
Threads Connected = 46

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 4.000000 sec.
You have 4975 out of 10247980 that take longer than 4.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 3
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 400
Current threads_connected = 46
Historic max_used_connections = 60
The number of used connections is 15% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 1.80 G
Current InnoDB data space = 5.85 G
Current InnoDB buffer pool free = 93 %
Current innodb_buffer_pool_size = 20.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 2705.18 G
Configured Max Per-thread Buffers : 17600.07 G
Configured Max Global Buffers : 65.17 G
Configured Max Memory Limit : 17665.24 G
Physical Memory : 31.22 G

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 424 M
Current key_buffer_size = 15.62 G
Key cache miss rate is 1 : 15793
Key buffer free ratio = 81 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 19.76 G
Current query_cache_used = 393 M
Current query_cache_limit = 9.76 G
Current Query cache Memory fill ratio = 1.94 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 20.00 G
Current read_rnd_buffer_size = 1.99 G
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 20.00 G
You have had 3005 queries where a join could not use an index properly
join_buffer_size >= 4 M
This is not advised
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.

OPEN FILES LIMIT
Current open_files_limit = 8192 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 2000 tables
Current table_definition_cache = 256 tables
You have a total of 12548 tables
You have 2000 open tables.
Current table_cache hit rate is 0%
, while 100% of your table cache is in use
You should probably increase your table_cache
You should probably increase your table_definition_cache value.

TEMP TABLES
Current max_heap_table_size = 256 M
Current tmp_table_size = 2.50 G
Of 106335 temp tables, 28% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 1.99 G
Current table scan ratio = 180571 : 1
read_buffer_size is over 8 MB there is probably no need for such a large read_buffer

TABLE LOCKING
Current Lock Wait ratio = 1 : 140
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.


3) какая у Вас ОС, есть ли у Вас возможность заменить mysql на более новый или на Percona, Maria?
Код: sql
1.
2.
3.
[root@X ~]# cat /etc/issue
CentOS release 6.7 (Final)
Kernel \r on an \m
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2015, 21:07:47
    #39073658
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
ElectronnnnnRAM : 32GbElectronnnnn
Код: sql
1.
[--] Total buffers: 65.4G global + 44.0G per thread (400 max threads)

Что ж вы творите-то???
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2015, 21:09:52
    #39073663
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
Electronnnnn
Код: sql
1.
Current query_cache_size = 19.76 G

Сделайте 128 или 256 Мбайт.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2015, 21:10:42
    #39073664
Electronnnnn
Electronnnnn
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
miksoftЧто ж вы творите-то???

Прошу сильно не ругать за вывернутые настройки буферов в my.cnf , потому как через них ищется узкое место.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2015, 23:58:45
    #39073721
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
Electronnnnn
Код: sql
1.
2.
3.
4.
Current InnoDB index space = 1.80 G
Current InnoDB data space = 5.85 G
Current InnoDB buffer pool free = 93 %
Current innodb_buffer_pool_size = 20.00 G

innodb_buffer_pool_size нет смысла ставить больше, чем в размер всех InnoDB-таблиц и их индексов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.10.2015, 00:00:46
    #39073723
Electronnnnn
Electronnnnn
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
miksoftinnodb_buffer_pool_size нет смысла ставить больше, чем в размер всех InnoDB-таблиц и их индексов.
снизил в два раза, но оптимизацией тут не пахнет =)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.10.2015, 00:02:49
    #39073725
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
Electronnnnn
Код: sql
1.
2.
Current MyISAM index space = 424 M
Current key_buffer_size = 15.62 G

key_buffer_size нет смысла делать больше, чем суммарный размер индексов MyISAM-таблиц.

Кстати, странно, что используется смесь таблиц на разных движках. Обычно это не требуется.
Если нет специальных противопоказаний, то переведите MyISAM-таблицы (кроме системных) в InnoDB.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.10.2015, 00:05:01
    #39073727
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
Electronnnnnно оптимизацией тут не пахнет =)Тут хотя бы приблизительный порядок надо навести, иначе вы систему до свопа доведете. Или уже довели.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.10.2015, 00:09:13
    #39073729
Electronnnnn
Electronnnnn
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
miksoftElectronnnnnно оптимизацией тут не пахнет =)Тут хотя бы приблизительный порядок надо навести, иначе вы систему до свопа доведете. Или уже довели.
в текущем треде только одна цель - ускорить эти тяжелые неоптимизированные запросы. Если нужно будет больше памяти серверу - добавим, если надо будет под базы подключить ссд - сделаем, но нужно точно знать из-за чего сначала после ребута сервиса запросы идут по одной секунде, после 4-6 часов работы по 11-15 секунд. Нет нужды перекраивать лимиты под более низкие именно для оптимизации потребляемой сервисом памяти.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.10.2015, 00:11:04
    #39073730
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
Electronnnnn
Код: sql
1.
2.
3.
Current max_heap_table_size = 256 M
Current tmp_table_size = 2.50 G
Of 106335 temp tables, 28% were created on disk

Эти два параметра обычно изменяются парно и должны быть равны. Для внутренних временных таблиц работет меньше значение из них.
http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values.Я бы предложил оба их установить в 2-4 ГБ для начала. И посмотреть сколько временных таблиц после этого будет валиться на диск.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.10.2015, 00:13:17
    #39073731
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
Electronnnnn
Код: sql
1.
[!!] Maximum possible memory usage: 17665.5G (56582.07% of installed RAM)

ElectronnnnnЕсли нужно будет больше памяти серверу - добавим17 Терабайт добавите??? смешно...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.10.2015, 00:16:47
    #39073732
Electronnnnn
Electronnnnn
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
miksoftElectronnnnn
Код: sql
1.
[!!] Maximum possible memory usage: 17665.5G (56582.07% of installed RAM)

ElectronnnnnЕсли нужно будет больше памяти серверу - добавим17 Терабайт добавите??? смешно...
Собственно в первом посте и было указано, что на лимиты по памяти на обращать внимания. Лимиты вывернуты именно для того, чтобы найти хотя бы выход из ситуации.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.10.2015, 00:23:33
    #39073734
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
Electronnnnn,

Ну так вы уверены, что вы не загоняте систему в своп?
Я вот не уверен:Electronnnnn
Код: sql
1.
Max Memory Ever Allocated : 2705.18 G

Эта величина, конечно, не настоящая, но, тем не менее, перерасход памяти потрясает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.10.2015, 00:24:34
    #39073735
Electronnnnn
Electronnnnn
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
miksoftElectronnnnn,

Ну так вы уверены, что вы не загоняте систему в своп?
Я вот не уверен:Electronnnnn
Код: sql
1.
Max Memory Ever Allocated : 2705.18 G

Эта величина, конечно, не настоящая, но, тем не менее, перерасход памяти потрясает.
Код: sql
1.
2.
3.
4.
5.
[root@X ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         31970      19935      12034          0       1171       4533
-/+ buffers/cache:      14230      17740
Swap:        16383         71      16312
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.10.2015, 00:26:38
    #39073736
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
Electronnnnn
Код: sql
1.
2.
3.
4.
5.
[root@X ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         31970      19935      12034          0       1171       4533
-/+ buffers/cache:      14230      17740
Swap:        16383         71      16312

Это картина "после 4-6 часов работы" ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.10.2015, 00:30:16
    #39073738
Electronnnnn
Electronnnnn
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
miksoftElectronnnnn
Код: sql
1.
2.
3.
4.
5.
[root@X ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         31970      19935      12034          0       1171       4533
-/+ buffers/cache:      14230      17740
Swap:        16383         71      16312

Это картина "после 4-6 часов работы" ?
После суток-двух. Картина примерно одна и та же с начала перезагрузки сервера до начала торможений по запросам.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.10.2015, 08:27:42
    #39073774
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
miksoftElectronnnnn
Код: sql
1.
Current query_cache_size = 19.76 G

Сделайте 128 или 256 Мбайт.


да в 0 эту шнягу надо ставить, в ноль.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.10.2015, 08:33:07
    #39073775
VGrey
VGrey
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация работы MySQL при выборке 50кк строк
ElectronnnnnЛимиты вывернуты именно для того, чтобы найти хотя бы выход из ситуации.

Electronnnnn , именно "вывернутые лимиты" и могут быть причиной Ваших проблем.

miksoftElectronnnnn
Код: sql
1.
Current query_cache_size = 19.76 G

Сделайте 128 или 256 Мбайт.

miksoft очень правильно отмечает, что "вывернутый" query_cache_size вполне может быть причиной Ваших проблем . Я бы рекомендовал начать именно с него - 64 - 128М, не больше, или, для опыта, вообще отключить, временно, и посмотреть, как себя будет вести система.
Вообще, проблема локов, связанная с завышенным значением query_cache_size, изместна давно.

Еще раз обращаю Ваше внимание на возможность заменить mysql5.1 на, например, MariaDB10, Percona Server, или просто на более новый mysql. Но, полюбому, не увлекайтесь "вывернутыми лимитами" - любое увеличение ресурсов сверх необходимых - накладные расходы и уменьшение производительности. Или проблемы с блокировками, как в случае с завышенным query_cache_size.

---
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация работы MySQL при выборке 50кк строк / 25 сообщений из 33, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 408ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1832620]:
 0ms
get settings:
 10ms
get forum list:
 17ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 67ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 67ms
get tp. blocked users:
 1ms
others: 230ms
total:  408ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]