производительность MySql
От: vvv848165@ya.ru  
Дата: 25.09.19 11:16
Оценка:
Выполнение запросов на чтение занимает наносекунды,
а вот добавление новой строки в таблицу занимает примерно 1-0.5 сек (винт при этом загружен полностью)...
Почему так падает производительность ??? Или это нормально?
пробыал под C# и под C++ аналогично...
Re: производительность MySql
От: vvv848165@ya.ru  
Дата: 25.09.19 12:05
Оценка:
на PHP скорость вставки точно повыше http://forum.php.su/topic.php?forum=28&topic=4026&postid=1318871174#1318871174
НО В ЧЁМ может быть косяк???
Re: производительность MySql
От: vvv848165@ya.ru  
Дата: 25.09.19 12:22
Оценка:
 static void AddToDB()
        {
            string constring = "server=localhost;user=root;pwd=1234;database=usersdb;";

            using (MySqlConnection conn = new MySqlConnection(constring))
            {
                conn.Open();

                for (int i = 0; i < 1000000; i++)
                {
                    MySqlCommand cmd = new MySqlCommand("INSERT INTO `usersdb`.`users` (`firstname`, `age`) VALUES(@fn, @ag);", conn);

                    cmd.Parameters.Add(new MySqlParameter("@fn", "Name" + new Random().Next(int.MaxValue).ToString()));
                    cmd.Parameters.Add(new MySqlParameter("@ag", new Random().Next(120)));

                    cmd.ExecuteNonQuery();
                    Console.WriteLine("{0}", i);//~ раз в секунду
                }

                conn.Clone();
            }
        }
Re[2]: производительность MySql
От: Qt-Coder  
Дата: 25.09.19 12:28
Оценка:
Здравствуйте, vvv848165@ya.ru, Вы писали:

Дык у тебя транзакция каждый раз открывается. Ставь одну на все.
Re[2]: производительность MySql
От: Слава  
Дата: 25.09.19 12:46
Оценка:
Здравствуйте, vvv848165@ya.ru, Вы писали:


VYR> conn.Clone();


Это зачем? Почему Clone?

У вас в параметре соединения стоит autocommit?
Re[3]: производительность MySql
От: vvv848165@ya.ru  
Дата: 25.09.19 13:23
Оценка:
Здравствуйте, Слава, Вы писали:

С>Здравствуйте, vvv848165@ya.ru, Вы писали:



VYR>> conn.Clone();

опечатка на скорость не влияет
С>Это зачем? Почему Clone?

С>У вас в параметре соединения стоит autocommit?


вроде стоит
Re[3]: производительность MySql
От: vvv848165@ya.ru  
Дата: 25.09.19 13:28
Оценка:
Здравствуйте, Qt-Coder, Вы писали:

QC>Здравствуйте, vvv848165@ya.ru, Вы писали:


QC>Дык у тебя транзакция каждый раз открывается. Ставь одну на все.

Типо совет ... можно вообще всё на asm-e без БД, тогда быстрее будет (мне важна скорость на запрос)
Поидее не должно так много занимать времени даже поодиночке ...
на PHP видел тесты — там всё гораздо быстрее
Re[4]: производительность MySql
От: vvv848165@ya.ru  
Дата: 25.09.19 13:46
Оценка:
С>>У вас в параметре соединения стоит autocommit?

что-то не догнал как установить...
Re: производительность MySql
От: Буравчик Россия  
Дата: 25.09.19 14:38
Оценка:
Здравствуйте, vvv848165@ya.ru, Вы писали:

VYR>Выполнение запросов на чтение занимает наносекунды,

VYR>а вот добавление новой строки в таблицу занимает примерно 1-0.5 сек (винт при этом загружен полностью)...
VYR>Почему так падает производительность ??? Или это нормально?
VYR>пробыал под C# и под C++ аналогично...

Вставляй пачками, а не по одной записи
Best regards, Буравчик
Re[5]: производительность MySql
От: Слава  
Дата: 25.09.19 15:05
Оценка:
Здравствуйте, vvv848165@ya.ru, Вы писали:


С>>>У вас в параметре соединения стоит autocommit?


VYR>что-то не догнал как установить...


Вот его как раз ставить и не надо. И вставлять надо не по одной записи, а по несколько. Начало и завершение транзакции имеют свои накладные расходы T, и если сама вставка занимает R времени, то при вставке 100_000 строк по транзакции на строку у вас будут затраты 100_000 * (T + R), а при вставке по 1000 строк на транзакцию получится 1000 * T + 100_000 * R

И ещё я подозреваю, что драйвер mysql для php зверски оптимизирован. Основная база для языка, всё же.
Re: производительность MySql
От: Maniacal Россия  
Дата: 25.09.19 15:21
Оценка: 5 (1)
Здравствуйте, vvv848165@ya.ru, Вы писали:

VYR>Выполнение запросов на чтение занимает наносекунды,

VYR>а вот добавление новой строки в таблицу занимает примерно 1-0.5 сек (винт при этом загружен полностью)...
VYR>Почему так падает производительность ??? Или это нормально?
VYR>пробыал под C# и под C++ аналогично...

Если в таблице овердофига записей, есть индексы и вставка строки происходит не с увеличением значения индекса (для ASCENDING-индексов), а в середину куда-то, то при коммите происходит перестройка всего индекса, а это очень накладная операция.
Re[2]: производительность MySql
От: Anton Batenev Россия https://github.com/abbat
Дата: 25.09.19 19:52
Оценка:
Здравствуйте, vvv848165@ya.ru, Вы писали:

Я не знаю как это реализовано в C#, но скорее всего вот это:

MySqlCommand cmd = new MySqlCommand("INSERT INTO `usersdb`.`users` (`firstname`, `age`) VALUES(@fn, @ag);", conn);


надо вынести за цикл, т.к. оно скорее всего на каждой итерации создает prepared statement и не освобождает его за собой пока не придет gc (IDisposable вроде явно намекает на желательность ручного освобождения).

Далее, ты не показал схему таблицы. Если у тебя там PK, а ты вставляешь в середину, то будет тормозить на перестройке индекса.
github.com/abbat
Re: производительность MySql
От: vvv848165@ya.ru  
Дата: 26.09.19 05:33
Оценка:
Установил всё дома (там дешевый ssd винт) — 100 записей в секунду (загружено одно ядро на максимум а винт на 50%)
откуда такая оптимизация (ведь всё тоже самое)???
Re[3]: производительность MySql
От: BlackEric http://black-eric.lj.ru
Дата: 26.09.19 07:15
Оценка:
Здравствуйте, Anton Batenev, Вы писали:

AB>Я не знаю как это реализовано в C#, но скорее всего вот это:


AB>
AB>MySqlCommand cmd = new MySqlCommand("INSERT INTO `usersdb`.`users` (`firstname`, `age`) VALUES(@fn, @ag);", conn);
AB>


AB>надо вынести за цикл, т.к. оно скорее всего на каждой итерации создает prepared statement и не освобождает его за собой пока не придет gc (IDisposable вроде явно намекает на желательность ручного освобождения).


Да, нужно выносить.
https://github.com/BlackEric001
Re[2]: производительность MySql
От: Sinclair Россия https://github.com/evilguest/
Дата: 27.09.19 03:33
Оценка: +1
Здравствуйте, Maniacal, Вы писали:M>Если в таблице овердофига записей, есть индексы и вставка строки происходит не с увеличением значения индекса (для ASCENDING-индексов), а в середину куда-то, то при коммите происходит перестройка всего индекса, а это очень накладная операция.
Это что за индексы такие, в которых вставка в середину имеет асимптотику хуже O(logN)?
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Re[3]: производительность MySql
От: Maniacal Россия  
Дата: 27.09.19 12:38
Оценка:
Здравствуйте, Sinclair, Вы писали:

S>Это что за индексы такие, в которых вставка в середину имеет асимптотику хуже O(logN)?


Это количество итераций O(logN). А вот если индекс фрагментирован (когда близкие по значению элементы хранятся в крайне отдалённых друг от друга местах памяти/диска), что с ним происходит при добавлении значений в середину, то скакание по страницам памяти, а тем более по кластерам на диске, значительно замедляет поиск. Выход: перестройка индекса. Или после каждой INSERT/DELETE/UPDATE (как пишут на форумах, у MySQL она происходит после каждого commit'а (возможно, это не так)) или же периодическая перестройка индексов (вручную или автоматическая). Впрочем, перестройка индекса ONLINE, которая реализована в MsSQL, например, производит перестройку индекса в фоновом режиме, предоставляя клиентам старый индекс на это время. Имеет смысл, если перестройка производится не часто. Как подсказывает логика, самая нетривиальная операция, требующая перестройки индекса — UPDATE (с затрагиванием значений индексированных полей).

Вот, проверил быстродействие алгоритмов дерева двоичного поиска и метода интервалов на непрерывном участке памяти (именно так индексы хранятся в файлах) + для сравнения std::set. Сначала вставка 10 млн. случайных значений в интервале 0-(2⁶³-1), потом поск 10 млн. других случайных значений того же порядка.

Тип Операция Длительность Всего итераций Мин. итераций Макс. итераций
Двоичное дерево вставка 10343 ms 300396976 1 62
Двоичное дерево поиск 13120 ms 320397750 6 63
Массив вставка+сортировка(в конце) 1654 ms n/a n/a n/a
Массив поиск методом интервалов 4430 ms 243179347 9 25
std::set вставка 11840 ms n/a n/a n/a
std::set поиск 12480 ms n/a n/a n/a
Понятно, что с массивом (он же перестроенный индекс) работать быстрее при поиске, но... Вставка, например, 10-ти значений в массив из 10 млн. записей с пересортировкой после каждой вставки — 4040 ms.

P.S. Помнится, работал в CBOSS, там была таблица, хранящая все звонки всех абонентов сотового оператора за последние несколько месяцев. При добавлении звонков, совершённых в роуминге, заполнялось индексированное поле, хранящее PK из таблицы то ли с роуимнговыми звонками, то ли с информацией о роуминговом файле или PK таблицы заданий на его загрузку. Что-то там было связанное то ли с повторной загрузкой роуминговых файлов, то ли с откатом ранее загруженных, в общем там неслабые велосипедные костыли на основе шаманского бубна применялись (не мной), чтобы не вешать оракловый сервер БД. Оттуда такие воспоминания.

  Исходник теста на VC++, накидал по быстрому "на коленке"
#include "stdafx.h"
#include <Windows.h>
#include <time.h>
#include <vector>
#include <algorithm>
#include <set>

struct STreeLeaf
{
    int m_nData;
    size_t m_nLeftIdx;
    size_t m_nRightIdx;

    STreeLeaf(int nData = 0) : m_nData(nData), m_nLeftIdx(0), m_nRightIdx(0) {}
};

int main()
{

    int nAddCnt = 10000000;

    std::vector<STreeLeaf> vecIdx1;
    vecIdx1.reserve(nAddCnt);
    std::vector<int> vecIdx2;
    vecIdx2.reserve(nAddCnt);
    std::set<int> setIdx;

    int nMinIterCnt = 0, nMaxIterCnt = 0, nTotalIterCnt = 0;
    srand(0);
    unsigned t0 = GetTickCount();
    for (int i = 0; i < nAddCnt; i++)
    {
        int nData = (rand() << 15) | rand();
        int nIterCnt = 1;
        STreeLeaf sLeaf(nData);
        if (vecIdx1.empty())
        {
            vecIdx1.push_back(sLeaf);
        }
        else
        {
            size_t nCurIdx = 0;
            size_t nIdxMax = vecIdx1.size() - 1;
            for(;;)
            {
                STreeLeaf& sCurLeaf = vecIdx1[nCurIdx];
                size_t nNextLeafIdx = nData < sCurLeaf.m_nData ? sCurLeaf.m_nLeftIdx : sCurLeaf.m_nRightIdx;
                if (!nNextLeafIdx)
                {
                    vecIdx1.push_back(sLeaf);
                    STreeLeaf& sCurLeaf = vecIdx1[nCurIdx];
                    size_t& nNextLeafIdx = nData < sCurLeaf.m_nData ? sCurLeaf.m_nLeftIdx : sCurLeaf.m_nRightIdx;
                    nNextLeafIdx = nIdxMax + 1;
                    break;
                }
                nCurIdx = nNextLeafIdx;
                nIterCnt++;
            }
        }
        if (!i || nMinIterCnt > nIterCnt) nMinIterCnt = nIterCnt;
        if (!i || nMaxIterCnt < nIterCnt) nMaxIterCnt = nIterCnt;
        nTotalIterCnt += nIterCnt;
    }
    unsigned t1 = GetTickCount();

    printf("B-Tree insert. Index size: %d, min iter cnt: %d, max iter cnt: %d, total iter cnt: %d, time estimated: %d ms\r\n", nAddCnt, nMinIterCnt, nMaxIterCnt, nTotalIterCnt, t1 - t0);

    srand(0);
    t0 = GetTickCount();
    for (int i = 0; i < nAddCnt; i++)
    {
        int nData = (rand() << 15) | rand();
        vecIdx2.push_back(nData);
    }
    std::stable_sort(vecIdx2.begin(), vecIdx2.end());
    t1 = GetTickCount();

    printf("Array insert+final sort. Index size: %d, time estimated: %d ms\r\n", nAddCnt, t1 - t0);

    srand(0);
    t0 = GetTickCount();
    int nAddCnt2 = 10;
    for (int i = 0; i < nAddCnt2; i++)
    {
        int nData = (rand() << 15) | rand();
        vecIdx2.push_back(nData);
        std::stable_sort(vecIdx2.begin(), vecIdx2.end());
    }
    t1 = GetTickCount();

    printf("Array insert+sort. Index size: %d, intem inserted: %d, time estimated: %d ms\r\n", nAddCnt, nAddCnt2, t1 - t0);

    srand(0);
    t0 = GetTickCount();
    for (int i = 0; i < nAddCnt; i++)
    {
        int nData = (rand() << 15) | rand();
        setIdx.insert(nData);
    }
    t1 = GetTickCount();

    printf("std::set insert. Index size: %d, time estimated: %d ms\r\n", nAddCnt, t1 - t0);

    nMinIterCnt = 0, nMaxIterCnt = 0, nTotalIterCnt = 0;
    srand(1000);
    t0 = GetTickCount();
    for (int i = 0; i < nAddCnt; i++)
    {
        int nData = (rand() << 15) | rand();
        int nIterCnt = 1;
        STreeLeaf sLeaf(nData);
        if (vecIdx1.empty())
        {
            vecIdx1.push_back(sLeaf);
        }
        else
        {
            size_t nCurIdx = 0;
            size_t nIdxMax = vecIdx1.size();
            for (;;)
            {
                STreeLeaf& sCurLeaf = vecIdx1[nCurIdx];
                if (sCurLeaf.m_nData == nData) break;
                size_t& nNextLeafIdx = nData < sCurLeaf.m_nData ? sCurLeaf.m_nLeftIdx : sCurLeaf.m_nRightIdx;
                if (!nNextLeafIdx) break;
                nCurIdx = nNextLeafIdx;
                nIterCnt++;
            }
        }
        if (!i || nMinIterCnt > nIterCnt) nMinIterCnt = nIterCnt;
        if (!i || nMaxIterCnt < nIterCnt) nMaxIterCnt = nIterCnt;
        nTotalIterCnt += nIterCnt;
    }
    t1 = GetTickCount();

    printf("B-Tree search. Values searched: %d, min iter cnt: %d, max iter cnt: %d, total iter cnt: %d, time estimated: %d ms\r\n", nAddCnt, nMinIterCnt, nMaxIterCnt, nTotalIterCnt, t1 - t0);

    nMinIterCnt = 0, nMaxIterCnt = 0, nTotalIterCnt = 0;
    srand(1000);
    t0 = GetTickCount();
    for (int i = 0; i < nAddCnt; i++)
    {
        int nData = (rand() << 15) | rand();

        int nIterCnt = 1;

        size_t nIdxMin = 0;
        size_t nIdxMax = vecIdx2.size() - 1;
        if (nData < vecIdx2[nIdxMin] || nData > vecIdx2[nIdxMax]) continue;

        while (nIdxMin != nIdxMax)
        {
            size_t nIdx = nIdxMin + (nIdxMax - nIdxMin) / 2;
            int nCurData = vecIdx2[nIdx];
            if (nCurData == nData) break;
            if (nIdx == nIdxMin)
            {
                nIdx++;
                // Или нашли или нет, в любом случае выход из поиска
                break;
            }
            if (nCurData > nData) nIdxMax = nIdx;
            else nIdxMin = nIdx;
            nIterCnt++;
        }
        if (!i || nMinIterCnt > nIterCnt) nMinIterCnt = nIterCnt;
        if (!i || nMaxIterCnt < nIterCnt) nMaxIterCnt = nIterCnt;
        nTotalIterCnt += nIterCnt;
    }
    t1 = GetTickCount();

    printf("Interval search. Values searched: %d, min iter cnt: %d, max iter cnt: %d, total iter cnt: %d, time estimated: %d ms\r\n", nAddCnt, nMinIterCnt, nMaxIterCnt, nTotalIterCnt, t1 - t0);

    nMinIterCnt = 0, nMaxIterCnt = 0, nTotalIterCnt = 0;
    srand(1000);
    t0 = GetTickCount();
    for (int i = 0; i < nAddCnt; i++)
    {
        int nData = (rand() << 15) | rand();
        auto ix = setIdx.find(nData);
        if (ix == setIdx.end()) continue;
        else continue; // vecIdx1[i] = *ix;
    }
    t1 = GetTickCount();

    printf("std::set search. Values searched: %d, time estimated: %d ms\r\n", nAddCnt, t1 - t0);

    getchar();

    return 0;
}
Отредактировано 27.09.2019 13:21 Maniacal . Предыдущая версия . Еще …
Отредактировано 27.09.2019 12:46 Maniacal . Предыдущая версия .
Отредактировано 27.09.2019 12:44 Maniacal . Предыдущая версия .
Re[2]: производительность MySql
От: Mr.Delphist  
Дата: 27.09.19 16:45
Оценка: -1 :)
Здравствуйте, vvv848165@ya.ru, Вы писали:


VYR>
VYR> static void AddToDB()
VYR>        {
VYR>            string constring = "server=localhost;user=root;pwd=1234;database=usersdb;";

VYR>            using (MySqlConnection conn = new MySqlConnection(constring))
VYR>            {
VYR>                conn.Open();

VYR>                for (int i = 0; i < 1000000; i++)
VYR>                {
VYR>                    MySqlCommand cmd = new MySqlCommand("INSERT INTO `usersdb`.`users` (`firstname`, `age`) VALUES(@fn, @ag);", conn);

VYR>                    cmd.Parameters.Add(new MySqlParameter("@fn", "Name" + new Random().Next(int.MaxValue).ToString()));
VYR>                    cmd.Parameters.Add(new MySqlParameter("@ag", new Random().Next(120)));

VYR>                    cmd.ExecuteNonQuery();
VYR>                    Console.WriteLine("{0}", i);//~ раз в секунду
VYR>                }

VYR>                conn.Clone();
VYR>            }
VYR>        }
VYR>


  1. Убрать вывод в консоль — он замедляет.
  2. Random() — аналогично, замедляет. Лучше сначала нагенерить массив случайных величин, и затем их юзать готовые.
  3. Параметризованный запрос — тоже небесплатен (в примере на PHP сразу собирается целевая SQL-строка).
Re[4]: производительность MySql
От: Sinclair Россия https://github.com/evilguest/
Дата: 29.09.19 15:01
Оценка: +1
Здравствуйте, Maniacal, Вы писали:

S>>Это что за индексы такие, в которых вставка в середину имеет асимптотику хуже O(logN)?


M>Это количество итераций O(logN). А вот если индекс фрагментирован (когда близкие по значению элементы хранятся в крайне отдалённых друг от друга местах памяти/диска), что с ним происходит при добавлении значений в середину, то скакание по страницам памяти, а тем более по кластерам на диске, значительно замедляет поиск.

ЧЕГО? Какое скакание? Какие итерации? Что значит "индекс фрагментирован"? Речь идёт о количесте операций IO. Я не знаю, что вы называете "итерациями".

M>Выход: перестройка индекса. Или после каждой INSERT/DELETE/UPDATE (как пишут на форумах, у MySQL она происходит после каждого commit'а (возможно, это не так)) или же периодическая перестройка индексов (вручную или автоматическая).

ЧЕГО? Перестройка после каждого коммита? Вы точно ничего не путаете?
M>Вот, проверил быстродействие алгоритмов дерева двоичного поиска и метода интервалов на непрерывном участке памяти (именно так индексы хранятся в файлах)
Да что вы говорите? Это какие такие индексы хранятся в файлах именно так? Я впервые слышу о столь смелых реализациях. MySQL, конечно, не самая гениальная СУБД в мире, но и её писали люди не без головы.

M>Понятно, что с массивом (он же перестроенный индекс) работать быстрее при поиске, но... Вставка, например, 10-ти значений в массив из 10 млн. записей с пересортировкой после каждой вставки — 4040 ms.

Ну, наверное именно поэтому хранение индексов в "отсортированном массиве" в СУБД почти не применяется. Слово "почти" здесь только потому, что есть реализации СУБД, которые специально избегают операций типа "вставка в середину", поэтому используют что-то вроде отсортированного массива (на самом деле и они — тоже нет, но в первом приближении сойдёт).


M>P.S. Помнится, работал в CBOSS, там была таблица, хранящая все звонки всех абонентов сотового оператора за последние несколько месяцев. При добавлении звонков, совершённых в роуминге, заполнялось индексированное поле, хранящее PK из таблицы то ли с роуимнговыми звонками, то ли с информацией о роуминговом файле или PK таблицы заданий на его загрузку. Что-то там было связанное то ли с повторной загрузкой роуминговых файлов, то ли с откатом ранее загруженных, в общем там неслабые велосипедные костыли на основе шаманского бубна применялись (не мной), чтобы не вешать оракловый сервер БД. Оттуда такие воспоминания.

Лучше всё же перечитать что-то из основ СУБД. Шаманские бубны в реальных приложениях, интенсивно работающих с данными, конечно же применяются. Но уж точно не для того, чтобы оптимизировать "вставку в середину".
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Re[5]: производительность MySql
От: Maniacal Россия  
Дата: 30.09.19 06:13
Оценка: -2 :))
Здравствуйте, Sinclair, Вы писали:

S>Лучше всё же перечитать что-то из основ СУБД. Шаманские бубны в реальных приложениях, интенсивно работающих с данными, конечно же применяются. Но уж точно не для того, чтобы оптимизировать "вставку в середину".


Я и говорю. UPDATE середины индекса никак без перестройки индекса не обойдётся.
Re[6]: производительность MySql
От: Sinclair Россия https://github.com/evilguest/
Дата: 30.09.19 06:27
Оценка: +1
Здравствуйте, Maniacal, Вы писали:
M>Я и говорю. UPDATE середины индекса никак без перестройки индекса не обойдётся.
Эмм. Смотря что называть перестройкой.
То, что называют перестройкой индекса во взрослых СУБД, никакого отношения к вставке в середину не имеет.

Самый популярный вид индексов в РСУБД — это B*Tree. В нём изменение любого ключа обходится без перестройки индекса, и задействует O(logN) страниц. При этом логарифм там по очень большим основаниям. То есть большинство операций задействуют две-три страницы. Рассуждения про "соседние ключи разбросаны по несоседним страницам" — вообще ни о чём. Так бывает, и очень часто, и никак не мешает быстродействию индексов.

Ваши примеры про бинарное дерево и интервальный массив не говорят ни о чём, кроме того, что бинарные деревья и отсортированные массивы в РСУБД использовать не стоит.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.