ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Нужна консультация по оптимизации сервера MySQL
20 сообщений из 20, страница 1 из 1
Нужна консультация по оптимизации сервера MySQL
    #38440816
sergvic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergvic
Гость
Добрый день.
Вот столкнулся с большими объемами данных на MySQL сервере. Раньше с таким не работал.
Медленно идут запросы select и особенно удаление из таблиц при этом утилизация диска вырастает на 100%.
Посоветуйте как увеличить быстродействие системы.
Параметры
Сервер
Виртуальный на ESXi
5 CPU
18 Gb memory
OC Centos 6.3 Final
mysql 5.1.67

Сейчас БД занимает 1.2 Тб. Формат таблиц Innodb, одним файлом.
Код: plaintext
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.
my.cnf
[mysqld]
lower_case_table_names=1
datadir=/database
basedir=/database
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

character-set-server = cp1251
init-connect="SET NAMES cp1251"

max_connections=2000
thread_concurrency=10
thread_cache_size = 4

query_cache_type=1
query_cache_limit=10M
query_cache_size=512M
innodb_buffer_pool_size = 14G
table_cache = 100
innodb_log_file_size = 256M
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT

#innodb_read_ahead_threshold=2
#innodb_random_read_ahead
innodb_doublewrite
#innodb_io_capacity = 2000
#innodb_write_io_threads = 12
#innodb_read_io_threads = 12

slow_query_log
slow_query_log_file=/database/slow_query.log

long_query_time = 3
#log = /database/log
log-error = /database/log-errors.log
max_connect_errors = 50
max_connections = 1000

Заранее спасибо за советы.
...
Рейтинг: 0 / 0
25.10.2013, 10:23:05
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38440889
netwind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
netwind
Участник
sergvic, консультация в том, что консультация вам не нужна.
Пока вы не вникните в то, как работают ваши запросы, никакими танцами с бубнами с конфигом это не решить.
Надо анализировать запросы, менять схему и логику работы - это работает.
А тут уже никто из советчиков по интернету бесплатно не сможет потратить месяц чтобы понять что там у вас и как.
Поэтому не нужна консультация.

Однако, предостеречь от ошибок можно :
>query_cache_limit=10M
>query_cache_size=512M
перечитайте в документации как это работает. Большой кеш связан с большими проблемами по его очистке и поддержанию в актуальном состоянии. Скорее всего, вам такие завышенные значения не нужны.
Попробуйте cache_size=128M. Соответственно query_cache_limit возможно тоже стоит занизить, ну скажем как было по-умолчанию 1M. Но это опять же просто предположение исходя из общей практики. У вас может быть другая ситуация.


>Виртуальный на ESXi
Это фактор риска. Кто еще на том же сервере и чем они занятны?
Вроде как в ESXi заявлено разделение ввода-вывода, но по отзывам у людей оно как-то плоховато работает. Вы уверены, что соседи не грузят тот же диск или хранилище или что там у вас?
...
Рейтинг: 0 / 0
25.10.2013, 11:01:27
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38440919
sergvic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergvic
Гость
netwindsergvic, консультация в том, что консультация вам не нужна.
Пока вы не вникните в то, как работают ваши запросы, никакими танцами с бубнами с конфигом это не решить.
Надо анализировать запросы, менять схему и логику работы - это работает.
А тут уже никто из советчиков по интернету бесплатно не сможет потратить месяц чтобы понять что там у вас и как.
Поэтому не нужна консультация.

Однако, предостеречь от ошибок можно :
>query_cache_limit=10M
>query_cache_size=512M
перечитайте в документации как это работает. Большой кеш связан с большими проблемами по его очистке и поддержанию в актуальном состоянии. Скорее всего, вам такие завышенные значения не нужны.
Попробуйте cache_size=128M. Соответственно query_cache_limit возможно тоже стоит занизить, ну скажем как было по-умолчанию 1M. Но это опять же просто предположение исходя из общей практики. У вас может быть другая ситуация.


>Виртуальный на ESXi
Это фактор риска. Кто еще на том же сервере и чем они занятны?
Вроде как в ESXi заявлено разделение ввода-вывода, но по отзывам у людей оно как-то плоховато работает. Вы уверены, что соседи не грузят тот же диск или хранилище или что там у вас?

Стоит стороннее ПО, которое писали не мы. Сейчас с ними ведем переписку что бы понять что делать. На ESXi работает только две виртуалки БД и сервер вэб который общается с БД.

Еще такой вопрос. Разделение таблиц по разным файлам может уменьшить нагрузку на сервер ?
...
Рейтинг: 0 / 0
25.10.2013, 11:16:43
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38440951
netwind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
netwind
Участник
>Стоит стороннее ПО, которое писали не мы. Сейчас с ними ведем переписку что бы понять что делать.
Зато у вас есть slow query log (кстати, можно же long_query_time ставит равной 1 или даже 0.1, а анализировать не глазами а специальным ПО) и возможность добавить индексов. Стороннее ПО обычно эти действия даже не замечает.


>На ESXi работает только две виртуалки БД и сервер вэб который общается с БД.
но ведь вторая БД тоже нагружена? Да и веб всякий бывает. Там где-то в ESXi можно посмотреть текущую загрузку.
А самый верный способ - временно отключить соседей.

>Еще такой вопрос. Разделение таблиц по разным файлам может уменьшить нагрузку на сервер ?
Если имеется ввиду innodb_file_per_table=1, то может, но опять же не слишком осмысленно.
...
Рейтинг: 0 / 0
25.10.2013, 11:33:18
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441131
sergvic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergvic
Гость
Смущает скорость удаление записей в таблицах.
Н-р таблица занимающая 600 Гб. Удаление записей из таблицы за месяц занимает около 8 часов. Кол-во записей за месяц может быть около 800 тыс и больше. Раньше я с такими размерами БД не работал.
Это нормально или нет ?
...
Рейтинг: 0 / 0
25.10.2013, 13:02:58
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441335
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
авторmysql 5.1.67
обновлять до 5.6

авторРаньше я с такими размерами БД не работал.
Это нормально или нет ?
партиционировать и дропать партицию.
slow query log в студию.

explain запросов в студию. show create table в студию.
...
Рейтинг: 0 / 0
25.10.2013, 14:27:40
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441399
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
sergvic
Код: plaintext
max_connections=2000
sergvic
Код: plaintext
1.
max_connections = 1000
Зачем два раза и зачем так много?
...
Рейтинг: 0 / 0
25.10.2013, 15:09:11
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441416
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
sergvic
Код: plaintext
table_cache = 100
Устаревший параметр. В этой версии используется table_open_cache
И почему так мало? Сколько таблиц в базе?

Кстати, MyISAM не используется?
...
Рейтинг: 0 / 0
25.10.2013, 15:15:59
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441432
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
sergvicСейчас БД занимает 1.2 Тб. Формат таблиц Innodb, одним файлом.1.2 Тб - размер файла или объем данных? Если база старая, то эти величины могут различаться в разы.
...
Рейтинг: 0 / 0
25.10.2013, 15:21:19
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441452
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
авторФормат таблиц Innodb, одним файлом.
ну понятно. для начала сделать таблицу на файл. потом почитать про партиционирование.
...
Рейтинг: 0 / 0
25.10.2013, 15:28:30
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441474
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
автор1.2 Тб - размер файла или объем данных? Если база старая, то эти величины могут различаться в разы.
судя по
авторУдаление записей из таблицы за месяц занимает около 8 часов
она еще и фрагментированная вконец.
...
Рейтинг: 0 / 0
25.10.2013, 15:37:38
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441667
netwind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
netwind
Участник
sergvicСмущает скорость удаление записей в таблицах.
Н-р таблица занимающая 600 Гб. Удаление записей из таблицы за месяц занимает около 8 часов. Кол-во записей за месяц может быть около 800 тыс и больше.

А меня смущает только когда непрограммисты или ленящиеся программисты думают, что смогут исправить ситуацию на сервере одними настройками.
От перехода на file_per_table вы наверняка сейчас получите некоторое улучшение, потому что еще и дефрагментация произойдет.
Но если не разобраться в планах и запросах, потом все равно начнет тормозить.

Раньше я с такими размерами БД не работал.
Это нормально или нет ?

Вообще - не очень.
Стандартное использование бд подразумевает какой-то объем фактов или документов, которые генерирует человек, а у вас терабайты. Кто это все писал, читал, нажимал или подписывал?
Действительно ли нужно хранить эти данные ? Может быть пожертвовать какой-нибудь детализацией и изменить логику системы ?
Если уж решили использовать БД, придется разбираться.
...
Рейтинг: 0 / 0
25.10.2013, 17:20:09
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441685
sergvic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergvic
Гость
miksoftsergvicСейчас БД занимает 1.2 Тб. Формат таблиц Innodb, одним файлом.1.2 Тб - размер файла или объем данных? Если база старая, то эти величины могут различаться в разы.
Размер файла ibdata1 и постоянно увеличивается. Что значит старая ?
...
Рейтинг: 0 / 0
25.10.2013, 17:27:55
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441689
sergvic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergvic
Гость
netwindsergvicСмущает скорость удаление записей в таблицах.
Н-р таблица занимающая 600 Гб. Удаление записей из таблицы за месяц занимает около 8 часов. Кол-во записей за месяц может быть около 800 тыс и больше.

А меня смущает только когда непрограммисты или ленящиеся программисты думают, что смогут исправить ситуацию на сервере одними настройками.
От перехода на file_per_table вы наверняка сейчас получите некоторое улучшение, потому что еще и дефрагментация произойдет.
Но если не разобраться в планах и запросах, потом все равно начнет тормозить.

Раньше я с такими размерами БД не работал.
Это нормально или нет ?

Вообще - не очень.
Стандартное использование бд подразумевает какой-то объем фактов или документов, которые генерирует человек, а у вас терабайты. Кто это все писал, читал, нажимал или подписывал?
Действительно ли нужно хранить эти данные ? Может быть пожертвовать какой-нибудь детализацией и изменить логику системы ?
Если уж решили использовать БД, придется разбираться.
В базу сыпется данные с GPS приемников с 1000 машин. Это не наша система. И в логику мы не можем вмешиваться.
...
Рейтинг: 0 / 0
25.10.2013, 17:29:53
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441697
sergvic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergvic
Гость
miksoftsergvic
Код: plaintext
table_cache = 100
Устаревший параметр. В этой версии используется table_open_cache
И почему так мало? Сколько таблиц в базе?

Кстати, MyISAM не используется?

В бызе 115 таблиц. MyISAM не используется в текущей БД.
...
Рейтинг: 0 / 0
25.10.2013, 17:32:26
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441725
netwind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
netwind
Участник
sergvicВ базу сыпется данные с GPS приемников с 1000 машин. Это не наша система. И в логику мы не можем вмешиваться.
да что вы так в этот факт уцепились ?
В вашей ситуации запросы переделать "незаметно" можно с помощью, например, mysql-proxy. Индексы можно просто добавить или удалить так что никакое приложение их наличие не заметит. Версию mysql можно пытаться апгрейдить тоже.

А еще может вам производитель, что подскажет если деньгами поделитесь. У некоторых стартаперов такая бизнес-модель. вываливать всякий посредственный опенсорс для всех, а проблемы с ним решают за деньги и вполне успешно.
...
Рейтинг: 0 / 0
25.10.2013, 17:48:19
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441760
sergvic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergvic
Гость
netwindsergvicВ базу сыпется данные с GPS приемников с 1000 машин. Это не наша система. И в логику мы не можем вмешиваться.
да что вы так в этот факт уцепились ?
В вашей ситуации запросы переделать "незаметно" можно с помощью, например, mysql-proxy. Индексы можно просто добавить или удалить так что никакое приложение их наличие не заметит. Версию mysql можно пытаться апгрейдить тоже.

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

Пока предлагают обновить железо. и заменить RAID5 на RAID10
...
Рейтинг: 0 / 0
25.10.2013, 18:05:09
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441762
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
авторПока предлагают обновить железо. и заменить RAID5 на RAID10

«А нельзя ли выкинуть труп петуха, традиционно замурованного в левую пассажирскую дверцу»?
...
Рейтинг: 0 / 0
25.10.2013, 18:06:27
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441779
netwind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
netwind
Участник
sergvicА еще может вам производитель, что подскажет если деньгами поделитесь. У некоторых стартаперов такая бизнес-модель. вываливать всякий посредственный опенсорс для всех, а проблемы с ним решают за деньги и вполне успешно.

Пока предлагают обновить железо. и заменить RAID5 на RAID10[/quot]
Вообще-то разумно, плохо raid5 подходит для бд с активной записью.
Если думать в эту сторону подумайте сообразить SSD-ускоритель. Так можно довольно паршиво спроектированные системы вытянуть на приемлемый уровень.
...
Рейтинг: 0 / 0
25.10.2013, 18:35:04
| Ответить | Цитировать | Написать  
Нужна консультация по оптимизации сервера MySQL
    #38441829
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
sergvic,

в базу сыплется с ... и удаляется по прошествии 8-и часов? С 1000 машин, а удаляется по 800 тыс. записей? Стало быть с каждого приемника "сыплется" по 800 записей за 8 часов .. по 100шт в час с каждого, так?

Откуда файл на 1.2Тб? Сделайте дамп и залейте его, думаю вопрос отпадет "сам собой". Там, действительно скорее всего жуткая дефрагментация.
...
Рейтинг: 0 / 0
25.10.2013, 19:35:12
| Ответить | Цитировать | Написать  
20 сообщений из 20, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Нужна консультация по оптимизации сервера MySQL
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=38441399&tid=1835834]:
 0ms
get settings:
 5ms
get forum list:
 9ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 29ms
get topic data:
 6ms
get forum data:
 1ms
get page messages:
 35ms
get tp. blocked users:
 1ms
others: 202ms
total:  292ms
созд. / загр.: 292ms
x
x
Закрыть


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