Форум программистов, компьютерный форум, киберфорум
MySQL
Войти
Регистрация
Восстановить пароль
Карта форума Темы раздела Блоги Сообщество Поиск Заказать работу  
 
Рейтинг 4.75/4: Рейтинг темы: голосов - 4, средняя оценка - 4.75
0 / 0 / 0
Регистрация: 09.09.2013
Сообщений: 15
1

Медленная работа MySQL

03.09.2022, 01:03. Показов 683. Ответов 19

Author24 — интернет-сервис помощи студентам
Так почему же SQL начинает медленно работать и рвать соединения?
Теория 1:
При большом количестве подключений воркбенч показывает нагрузку на цпу 2, может достигать 5-ти, хотя сам цпу по факту не загружен и простаивает, словно он использует всего 1 поток. Думаю из-за этого возникают исключения "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
mysql версии 8.0.30
мой конфиг:
Код
[mysqld]
datadir = /mnt/pData/prj/MySQL/mysql
bind-address = 0.0.0.0

connect_timeout = 120
wait_timeout = 120
max_allowed_packet = 500M
net_read_timeout = 600
net_write_timeout = 600
thread_cache_size=64

skip-log-bin
log-error=error.log

innodb_undo_log_truncate=off

# general
table_open_cache = 20000
table_open_cache_instances=64
back_log=3500
max_connections=4000

# files
innodb_file_per_table
innodb_log_file_size=15G
innodb_log_files_in_group=2
innodb_open_files=4000

# buffers
innodb_buffer_pool_size= 10G
innodb_buffer_pool_instances=8
innodb_log_buffer_size=64M

# tune
innodb_doublewrite= 1
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit= 0
innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_max_dirty_pages_pct=90
innodb_max_dirty_pages_pct_lwm=10
innodb_lru_scan_depth=2048
innodb_page_cleaners=4
join_buffer_size=256K
sort_buffer_size=256K
innodb_use_native_aio=1
innodb_stats_persistent = 1


innodb_adaptive_flushing = 1
innodb_flush_neighbors = 0
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity=1500
innodb_io_capacity_max=2500
innodb_purge_threads=4
innodb_adaptive_hash_index=0
max_prepared_stmt_count=100000
innodb_monitor_enable = '%'
performance_schema = ON
Теория 2:
Когда все клиенты(боты) создались и подключились к каналам для сбора данных, все работает нормально. Количество подключений ожидаемое, исключений про выполнении запросов нет. Проблемы начинаются именно во время создании новых клиентов. При нормальной работе 200 клиентов создают примерно 200-220 подключений к БД, но в момент создания они могут создать 1300-1500 подключений к БД. Я пока не могу понять почему, в коде все хорошо. В момент создании клиента он выполняет следующие запросы:
C#
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
private void CreateDB()
        {
            try
            {
                using MySqlConnection Connect = DBUtils.GetDBConnection(_DbUserName, _DbPassword);
                Connect.Open();
                string SQL = $"CREATE DATABASE IF NOT EXISTS {_DbName} DEFAULT CHARACTER SET utf8mb4";
                using MySqlCommand command = new MySqlCommand(SQL, Connect);
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                _logWriter.LogWriterTask(e, "CreateTable()");
            }
        }
private void CreateTable()
        {
            try
            {
                using MySqlConnection Connect = DBUtils.GetDBConnection(_DbName, _DbUserName, _DbPassword);
                Connect.Open();
 
                string SQL = $"CREATE TABLE IF NOT EXISTS {_DbName}.dbUserTable ( dbID INTEGER NOT NULL AUTO_INCREMENT, TwitchID INTEGER NOT NULL UNIQUE, Name VARCHAR(30), " +
                "isSub INTEGER, isVip INTEGER, isMod INTEGER, IsBroadcaster INTEGER, UvalCon INTEGER, messageCon INTEGER, roulettCon INTEGER, roulettCD DOUBLE, UvalTimer DOUBLE, banCount INTEGER, Points DOUBLE, IsOnline INTEGER, PRIMARY KEY(dbID))";
                using (MySqlCommand Command = new MySqlCommand(SQL, Connect))
                {
                    Command.ExecuteNonQuery();
                }
 
                SQL = $"CREATE TABLE IF NOT EXISTS {_DbName}.dbUserMessageTable ( dbID INTEGER NOT NULL AUTO_INCREMENT, TwitchID INTEGER NOT NULL, Name VARCHAR(30), Message VARCHAR(600), TimeStamp DOUBLE, PRIMARY KEY(dbID))";
                using (MySqlCommand Command = new MySqlCommand(SQL, Connect))
                {
                    Command.ExecuteNonQuery();
                }
 
 
                SQL = $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = '{_DbName}' AND table_name = 'dbUserTable' AND index_name = 'index_Name'";
                int i = 0;
                using (MySqlCommand Command = new MySqlCommand(SQL, Connect))
                {
                    using var sqlReader = Command.ExecuteReader();
                    sqlReader.Read();
                    i = Convert.ToInt32(sqlReader[0]);
                }
                if (i == 0)
                {
                    SQL = $"CREATE UNIQUE INDEX index_Name ON {_DbName}.dbUserTable (Name)";
                    using var createIndexCmd = new MySqlCommand(SQL, Connect);
                    createIndexCmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                _logWriter.LogWriterTask(e, "CreateTable()");                
            }
        }
Могут ли эти запросы так сильно нагружать сервер ?
0
Programming
Эксперт
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
03.09.2022, 01:03
Ответы с готовыми решениями:

Медленная обработка запросов в mysql
Всем привет. Имеется mysql 8.0 на шаред хостинге, 200-300 БД и 18000 и более таблиц, ОС Linux....

Медленная работа с views
Есть таблица gps CREATE TABLE `gps` ( `user_id` INT(11) NOT NULL, `created` INT(11) NOT...

Медленная работа с БД
Есть MS SQL-2008 на неплохом железе - ОЗУ 96 Гб. На нём есть база CRM Dynamics. Счёт записям идёт...

Медленная работа с БД
Две программы на Delphi обращаются к одной БД. Проблема в том, что когда одна программа вносит...

Медленная работа процедуры
Всем добрый день, Столкнулся с такой проблемой, есть некая процедура, раньше она отрабатывала в...

19
17 / 17 / 6
Регистрация: 11.11.2015
Сообщений: 142
03.09.2022, 08:34 2
Я не большой специалист по БД, но могу предложить сделать вам следующий тест:
Если ваши БД и таблицы, кот. вы создаете при каждом " В момент создании клиента " имеют одно и то же имя, то просто приготовьте их (т.е. создайте все это) заранее. Затем при каждом создании нового клиента оставьте только ту часть работы, кот. реально необходима в таком случае, например сделать выборку либо update/insert в УЖЕ СУЩЕСТВУЮЩУЮ таблицу.
Надеюсь, что смог передать свою мысль.
0
0 / 0 / 0
Регистрация: 09.09.2013
Сообщений: 15
03.09.2022, 10:57  [ТС] 3
Запрос
SQL
1
CREATE DATABASE IF NOT EXISTS
создает бд только если ее нет
0
17 / 17 / 6
Регистрация: 11.11.2015
Сообщений: 142
03.09.2022, 12:06 4
создает бд только если ее нет
Я знаю. Имелось в виду, что даже такие запросы стоит убрать, просто ради эксперимента.
0
385 / 224 / 83
Регистрация: 28.04.2022
Сообщений: 1,116
03.09.2022, 13:57 5
ronink, И сколько у вас таблиц в общей сложности в БД?
0
0 / 0 / 0
Регистрация: 09.09.2013
Сообщений: 15
03.09.2022, 22:19  [ТС] 6
Цитата Сообщение от vkiper Посмотреть сообщение
такие запросы стоит убрать, просто ради эксперимента.
Попробовал во время старта программы делать запрос
SQL
1
SHOW DATABASES;
и сохранять полученный список в памяти. и в время воздания клиента смотреть если ли БД с его именем и если нет, то создать бд запросом CREATE DATABASE. Это особо ничего не изменило.
Цитата Сообщение от Gluck99 Посмотреть сообщение
И сколько у вас таблиц
Пока 3111 баз данных, в каждой по 2 таблицы.
0
17 / 17 / 6
Регистрация: 11.11.2015
Сообщений: 142
04.09.2022, 07:37 7
Пока 3111 баз данных, в каждой по 2 таблицы.
Обычно под одну задачу/проект выделяется одна-две БД, в них может быть много таблиц, но не 3000, а 5-20, в противном случае теряется общая картина со стороны разработчика(а еще могут вылезать всякие гадости типа нынешней). Мое ИМХО - у вас прежде всего проблема с архитектурой в БД. При этом вынужден оговориться - я не экперт в этом, надо бы подождать, когда кто-то из более знающих отзовется.
0
385 / 224 / 83
Регистрация: 28.04.2022
Сообщений: 1,116
04.09.2022, 16:01 8
Цитата Сообщение от ronink Посмотреть сообщение
Пока 3111 баз данных, в каждой по 2 таблицы.
А чем вызвано такое необычное решение - использовать много БД с небольшим количеством таблиц в них?
0
0 / 0 / 0
Регистрация: 09.09.2013
Сообщений: 15
04.09.2022, 18:14  [ТС] 9
Мне показалось это логичным, под каждый канал делать свою бд. Просто так сложилось, что каналов очень много.
п.с. для ясности : эта бд для бота, который подключается к IRC каналам стримеров на твиче и хранит в ней данные пользователей. Первая таблица для информации о самих пользователях, вторая для их сообщений.
0
385 / 224 / 83
Регистрация: 28.04.2022
Сообщений: 1,116
04.09.2022, 19:43 10
Цитата Сообщение от ronink Посмотреть сообщение
под каждый канал делать свою бд.
Допустим. И сколько записей в среднем у вас в таблицах (в тех, которых 2 в каждой БД)?
0
0 / 0 / 0
Регистрация: 09.09.2013
Сообщений: 15
04.09.2022, 21:56  [ТС] 11
Цитата Сообщение от Gluck99 Посмотреть сообщение
И сколько записей в среднем у вас в таблицах
По разному, все зависит от канала. От 1000 до 3млн+.
0
385 / 224 / 83
Регистрация: 28.04.2022
Сообщений: 1,116
04.09.2022, 23:46 12
Цитата Сообщение от ronink Посмотреть сообщение
По разному, все зависит от канала. От 1000 до 3млн+.
Я правильно понимаю, что у вас создаются два соединения (код клиента) - одно на создание БД, а второе таблиц и всего остального? Почему их два?
0
0 / 0 / 0
Регистрация: 09.09.2013
Сообщений: 15
05.09.2022, 02:00  [ТС] 13
Не совсем. Для каждой операции (поиск, обновление, создание и т.д.) есть отдельный метод. В рамках этого метода создается соединение. На пример так выглядит метод обновления пользователя
C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public async Task UpdateUser(cUser.UserObject User)
        {
            using MySqlConnection Connect = DBUtils.GetDBConnection(_DbName, _DbUserName, _DbPassword);
            string SQL = $"UPDATE {_DbName}.dbUserTable SET Name = @Name, isSub = @isSub, isVip = @isVip, isMod = @isMod, IsBroadcaster = @IsBroadcaster, UvalCon = @UvalCon, messageCon = @messageCon, roulettCon = @roulettCon, roulettCD = @roulettCD, UvalTimer = @UvalTimer, banCount = @banCount WHERE TwitchID = @TwitchID";
            using MySqlCommand Command = new MySqlCommand(SQL, Connect);
            Command.Parameters.AddWithValue("@Name", User.Name);
            Command.Parameters.AddWithValue("@isSub", User.isSub);
            Command.Parameters.AddWithValue("@isVip", User.isVip);
            Command.Parameters.AddWithValue("@isMod", User.isMod);
            Command.Parameters.AddWithValue("@IsBroadcaster", User.IsBroadcaster);
            Command.Parameters.AddWithValue("@UvalCon", User.UvalCon);
            Command.Parameters.AddWithValue("@messageCon", User.messageCon);
            Command.Parameters.AddWithValue("@roulettCon", User.roulettCon);
            Command.Parameters.AddWithValue("@roulettCD", User.roulettCD);
            Command.Parameters.AddWithValue("@UvalTimer", User.UvalTimer);
            Command.Parameters.AddWithValue("@banCount", User.banCount);
            Command.Parameters.AddWithValue("@TwitchID", User.TwitchID);
            await Connect.OpenAsync().ConfigureAwait(false);
            await Command.ExecuteNonQueryAsync().ConfigureAwait(false);
        }
0
385 / 224 / 83
Регистрация: 28.04.2022
Сообщений: 1,116
05.09.2022, 02:20 14
Цитата Сообщение от ronink Посмотреть сообщение
Для каждой операции (поиск, обновление, создание и т.д.) есть отдельный метод. В рамках этого метода создается соединение.
Я это предполагал. Вопрос был - зачем?
0
0 / 0 / 0
Регистрация: 09.09.2013
Сообщений: 15
05.09.2022, 05:48  [ТС] 15
Я пробовал делать одним подключением, но в таком случае возникало исключение "это соединение ассоциируется с другой операцией" или как-то так.

Добавлено через 2 часа 49 минут
Странно. Сейчас еще раз попробовал все привязать к одному подключению и исключений не возникает. Стало работать шустрее, но все равно сильно тормозит.
0
0 / 0 / 0
Регистрация: 09.09.2013
Сообщений: 15
05.09.2022, 15:17  [ТС] 16
Вроде получилось побороть проблему. Дело было в умирающем hdd. Перебросил бд на новый ссд и все залетало
0
1136 / 881 / 152
Регистрация: 25.07.2015
Сообщений: 1,959
05.09.2022, 15:48 17
Цитата Сообщение от ronink Посмотреть сообщение
Дело было в умирающем hdd.
При ваших потребностях он и не умирающий тупил бы со страшной силой )))
Как-бы SSD предполагался по умолчанию, даже в голову не приходило
вопрос о нём поднимать )))
0
0 / 0 / 0
Регистрация: 09.09.2013
Сообщений: 15
05.09.2022, 19:30  [ТС] 18
Цитата Сообщение от ronink Посмотреть сообщение
попробовал все привязать к одному подключению и исключений не возникает
К слову, оказывается что для команд Read и NonQuery должны быть разные подключения.
0
385 / 224 / 83
Регистрация: 28.04.2022
Сообщений: 1,116
05.09.2022, 20:04 19
Цитата Сообщение от ronink Посмотреть сообщение
оказывается что для команд Read и NonQuery должны быть разные подключения.
На самом деле нет. Если мы говорим о сервере.
0
0 / 0 / 0
Регистрация: 09.09.2013
Сообщений: 15
05.09.2022, 23:52  [ТС] 20
На счет самого сервера не знаю, но .NET такое не поддерживает
While a DataReader is open, the Connection is in use exclusively by that DataReader. You cannot execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.
0
05.09.2022, 23:52
IT_Exp
Эксперт
87844 / 49110 / 22898
Регистрация: 17.06.2006
Сообщений: 92,604
05.09.2022, 23:52
Помогаю со студенческими работами здесь

Медленная работа с локальной переменной
Доброго дня! Объясните новичку, почему одни и те же запросы с и без использования локальных...

Медленная работа проекта в MS Access 2000
Подскажите, пожалуйста, как разрешить проблему: невероятно <b>долго сохраняются объекты БД</b>...

Медленная работа скрипта по подсчету цифр в столбце
Приветствую. Есть задача: определить, сколько раз каждая из цифр от 0 до 9 встречается в столбце...

Aser v5 i7 u - очень медленная загрузка системы и такая же медленная работа приложений
Всем привет. Полгода назад купил ноут ( aser v5 i7 u) . Все было нормально, но неделю назад...

Медленная работа 1С
Народ работает в терминале Есть сервер, i5-4460, 32GB, hdd Пользователей много потому решили...

Медленная работа 1С
Доброго дня ... Стала жутко медленно работать 1С даже когда один на сервере . В новогодние...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
20
Ответ Создать тему
КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin
Copyright ©2000 - 2024, CyberForum.ru