Где можно оптимизировать работу в многопотоке с бд и/или сам mysql сервер

[melutsk]

Итак начну с того, что железа хватает достаточно, а именно e5-2692 v3 x2 + 128Гб ddr4 + ssd nvme m2. Работа идет в 48 шаблонов post/get, каждый шаблон еще работает в 30 потоков (все шаблоны одинаковые, кроме использования прокси - так давно еще сделал, чтобы более информативно каждый поток видеть). В самом постере отключено все, что только можно отключить - любые уведомления как самого постера, так и шаблонов. Количество потоков в одном процессе - 1. Сервер бд - openserver. Конфиг 5,6-x64

[client]

port                            = %mysqlport%
character_sets_dir              = "%dprogdir%\\modules\\database\\%mysql_driver%\\share\\charsets"

[mysql]

no-auto-rehash
no-beep
character_sets_dir              = "%dprogdir%\\modules\\database\\%mysql_driver%\\share\\charsets"

[mysqld]

# Required Settings

basedir                         = "%dprogdir%\\modules\\database\\%mysql_driver%"
bind-address                    = %ip%
character_sets_dir              = "%dprogdir%\\modules\\database\\%mysql_driver%\\share\\charsets"
character_set_server            = utf8mb4
collation_server                = utf8mb4_unicode_ci
datadir                         = "%dprogdir%\\userdata\\%mysql_driver%"
default_storage_engine          = InnoDB
ft_min_word_len                 = 3
local_infile                    = 0
lower_case_table_names          = 1
max_allowed_packet              = 256M
pid_file                        = "%dprogdir%\\userdata\\temp\\mysql.pid"
port                            = %mysqlport%
secure-file-priv                = "%dprogdir%\\userdata\\php_upload"
#skip_name_resolve              = 1
tmpdir                          = "%dprogdir%\\userdata\\temp"

key_buffer_size        = 512M
max_allowed_packet    = 200M
table_open_cache     = 100000
sort_buffer_size     = 16M
read_buffer_size     = 16M
read_rnd_buffer_size     = 16M
myisam_sort_buffer_size = 64M
thread_cache_size     = 16
query_cache_size     = 16M
query_cache_limit     = 256M
query_cache_type     = 0
join_buffer_size    = 64M
max_connections        = 10000
tmp_table_size        = 128M
max_heap_table_size    = 128M
open_files_limit     = 200000

default_authentication_plugin=mysql_native_password

# InnoDB Settings

innodb_data_file_path           = ibdata1:10M:autoextend
innodb_data_home_dir            = "%dprogdir%\\userdata\\%mysql_driver%"

innodb_buffer_pool_size     = 20G
innodb-log-buffer-size        = 16M
innodb_flush_log_at_trx_commit     = 2
innodb_lock_wait_timeout     = 50
transaction-isolation         = READ-COMMITTED
innodb_buffer_pool_instances     = 20

# Logging

%log%general_log                = 1
%log%general_log_file           = "%dprogdir%\\userdata\\logs\\%mysql_driver%_queries.log"
log_error                       = "%dprogdir%\\userdata\\logs\\%mysql_driver%_error.log"
#log_queries_not_using_indexes  = 1
#long_query_time                = 5
#slow_query_log                 = 1
#slow_query_log_file            = "%dprogdir%\\userdata\\logs\\%mysql_driver%_slow.log"

# MyISAM Settings

key_buffer_size                 = 32M
myisam_max_sort_file_size       = 256M
myisam_recover_options          = backup,force


[mysqldump]

quick
quote_names

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

Базы все в InnoDB.
1. База юзерагенты - 4 млн записей, беру кодом случайную, перед этом генерируя из диапазона номер строки случайную

string host = "localhost";   
string port = "3306";
string user = "root";         
string password = "pass123";           
string name = "useragents";   
string charset = "utf8mb4";
string ua_nomer2 = project.Variables["ua_nomer"].Value;

string connectionString = string.Format(
    "server={0};port={1};user={2};password={3};database={4};charset={5};pooling=False;SslMode=None;",
    host, port, user, password, name, charset
);
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
MySqlCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandText = "SELECT * FROM table_1 WHERE id = @id";
command.Parameters.AddWithValue("@id", ua_nomer2);
MySqlDataReader reader = command.ExecuteReader();
string id = "";
string text = "";
if (reader.Read())
{
    id = reader["id"].ToString();
    text = reader["text"].ToString();
}
reader.Close();
connection.Close();

return text;

2. База имена, 100к записей, беру кодом случайную, перед этом генерируя из диапазона номер строки случайную

string host = "localhost";   
string port = "3306";
string user = "root";         
string password = "pass123";           
string name = "imena";   
string charset = "utf8mb4";
string imya_nomer2 = project.Variables["imya_nomer"].Value;

string connectionString = string.Format(
    "server={0};port={1};user={2};password={3};database={4};charset={5};pooling=False;SslMode=None;",
    host, port, user, password, name, charset
);
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
MySqlCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandText = "SELECT * FROM table_1 WHERE id = @id";
command.Parameters.AddWithValue("@id", imya_nomer2);
MySqlDataReader reader = command.ExecuteReader();
string id = "";
string text = "";
if (reader.Read())
{
    id = reader["id"].ToString();
    text = reader["text"].ToString();
}
reader.Close();
connection.Close();

return text;

3. База фамилии, 100к записей, беру кодом случайную, перед этом генерируя из диапазона номер строки случайную

string host = "localhost";   
string port = "3306";
string user = "root";         
string password = "pass123";           
string name = "familii";   
string charset = "utf8mb4";
string familia_nomer2 = project.Variables["familia_nomer"].Value;

string connectionString = string.Format(
    "server={0};port={1};user={2};password={3};database={4};charset={5};pooling=False;SslMode=None;",
    host, port, user, password, name, charset
);
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
MySqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM table_1 WHERE id = @id";
command.Parameters.AddWithValue("@id", familia_nomer2);
MySqlDataReader reader = command.ExecuteReader();
string id = "";
string text = "";
if (reader.Read())
{
    id = reader["id"].ToString();
    text = reader["text"].ToString();
}
reader.Close();
connection.Close();

return text;

4. База ящики, 9 млн записей, берет каждый поток свою запись, ее изменяет (статус), чтобы другой поток эту запись уже не брал

string host = "localhost";   
string port = "3306";
string user = "root";         
string password = "pass123";           
string name = "emailsall";   
string charset = "utf8mb4";

string connectionString = string.Format(
    "server={0};port={1};user={2};password={3};database={4};charset={5};pooling=False;SslMode=None;",
    host, port, user, password, name, charset
);
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
MySqlCommand command = connection.CreateCommand();
MySqlTransaction transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = "SELECT * FROM table_1 WHERE status = 0 LIMIT 1 FOR UPDATE";
MySqlDataReader reader = command.ExecuteReader();

string id = "";
string text = "";
string status = "";
if (reader.Read())
{
    id = reader["id"].ToString();
    text = reader["text"].ToString();
    status = reader["status"].ToString();
}
reader.Close();
command.CommandText = "UPDATE table_1 SET status = 1 WHERE id = @id";
command.Parameters.AddWithValue("@id", id);
command.ExecuteNonQuery();
transaction.Commit();
connection.Close();

return text;

5. База использованые, добавляю кодом

string host = "localhost";   
string port = "3306";
string user = "root";         
string password = "pass123";           
string name = "used";   
string charset = "utf8mb4";

string connectionString = string.Format(
    "server={0};port={1};user={2};password={3};database={4};charset={5};pooling=False;SslMode=None;",
    host, port, user, password, name, charset
);
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
MySqlCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandText = "INSERT INTO table_1 SET text = @text";
command.Parameters.AddWithValue("@text", project.Variables["mail"].Value);
command.ExecuteNonQuery();
connection.Close();

return "ok";

Перед каждым кодом есть рандомные паузы от 0 до 5 секунд, так и внутри самого шаблона есть паузы.

И в многопотоке таком сервер бд откидывается, как мне кажется, ошибкой

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Вот вопрос - что при этом делать? Что и где поправить и что попробовать? Съехал с работы списков как раз изза тупняков, а с бд вот не могу справится.

 

[andrey-ka]

Тебе этот материал можно в конкурс ))

 

[melutsk]

Тебе этот материал можно в конкурс ))

Лучше в конкурс - решение по материалу)))

 

[andrey-ka]

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

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

 

[Crucifer]

Для статичных баз поставьте Sphinx (за исключением мыл которые у вас постоянно перезаписываются). Создадите индекс их, выборка будет раз в 10 быстрее стандартного индекса mysql. Уменьшите нагрузку на mysql.
Обращение к базе через SphinxQL как к Mysql только на другом порту. Пересоздание индекса можно поставить на крон.

nvme m2 - родной порт на матери, не через адаптер? Какие скорости ссдшника в нем? Все таки приличная нагрузка у вас.

В конфиге почему то не указан размер лога
innodb_log_file_size
В папке MySQL-5.6-x64 файлы ib_logfile0 и 1 сколько сейчас занимают места? С вашими данными можно выставить 3-4 гига. Останавливаете mysql, удаляете файлы, меняете конфиг. Потом запускаете с новыми параметрами, она сама создаст новые логи. Иначе будет ошибка при запуске.

 

[Lord_Alfred]

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

а точнее:

command.CommandText = "SELECT * FROM table_1 WHERE status = 0 LIMIT 1 FOR UPDATE";

проверь есть ли индекс на столбец status, плюс проверь его тип данных (лучше если будет `TINYINT(1) UNSIGNED`, если используешь только значения 0 и 1).

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

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

Советую ещё раз то же самое, что говорил в предыдущем посте:

slow query log смотри + explain + индексы проверяй + структуру возможно меняй.

Плюс после 24 часовой работы базы без перезапуска можешь прогнать https://github.com/major/MySQLTuner-perl и посмотреть что он предложит пофиксить, но к каждому изменяемому параметру стоит отнестись с долей скептицизма, т.к. он на 100% не сможет сказать в чем трабл, нужно будет изучать что ты меняешь и думать.
Ну и я хз как его под виндой запустить, но наверняка это можно сделать.

PS:

Перед каждым кодом есть рандомные паузы от 0 до 5 секунд, так и внутри самого шаблона есть паузы.

Это не решает проблему, просто оттягивает её.

PPS:
В противовес предложенному выше Sphinx - советую для начала попробовать переписать вот это:

1. База юзерагенты - 4 млн записей, беру кодом случайную, перед этом генерируя из диапазона номер строки случайную

2. База имена, 100к записей, беру кодом случайную, перед этом генерируя из диапазона номер строки случайную

на простые файлы, которые лежат на RAMDisk (ибо ОЗУ хватает), только не грузить весь файл целиком в память (и не использовать встроенные "списки"!), а чанком постараться прочитать рандомную позицию в файле. Это не просто на C#, но скорее всего возможно.

 

[Manfred]

Не стоит писать:
SELECT * FROM table_1 WHERE id = @id

Корректнее вместо * указывать конкретные поля.

Это сказывается на производительности.

 

[melutsk]

Не стоит писать:
SELECT * FROM table_1 WHERE id = @id

Корректнее вместо * указывать конкретные поля.

Это сказывается на производительности.

на сколько существенно?

 

[Manfred]

на сколько существенно?

Это зависит.



"Индексы являются средством эффективного поиска строк, но MySQL может также использовать индекс для извлечения данных, не считывая строку таблицы. В конце концов, листовые узлы индекса содержат те значения, которые они индексируют. Зачем просматривать саму строку, если чтение индекса уже может дать нужные данные? Индекс, который содержит (или «покрывает») все данные, необходимые для формирования результатов запроса, называется покрывающим индексом. "

Почитай про покрывающие индексы:

delphiplus.org

This domain may be for sale!

www.delphiplus.org

 

[DmitryAk]

кусок

MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
--
MySqlCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandText = "INSERT INTO table_1 SET text = @text";
command.Parameters.AddWithValue("@text", project.Variables["mail"].Value);
command.ExecuteNonQuery();
--
connection.Close();

от MySqlCommand command = connection.CreateCommand(); и до exequte обернуть в try..finally. В finally закрывать соединение, иначе в случае ошибок при исполнении ExecuteNonQuery соединение останется незакрытым.
Чем больше ошибок, тем больше незакрытых соединений, которые постепенно начнут отваливаться по таймауту.
Что видимо у вас и происходит.
Висящие соединения это еще и занятые хэндлы, кол-во которых в системе тоже ограничено пара десятков тысяч, насколько я помню.

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

Третий пункт оптимизации - убирать дрочево соединения. Стандарт нормальной работы - открыли соединение, работаем, работаем, работаем, закрыли. Открытие/закрытие в большинстве субд - весьма тяжелая процедура. Поэтому в начале шаба открыли один коннект для работы, в конце - закрыли.