Выполнение запросов на чтение занимает наносекунды,
а вот добавление новой строки в таблицу занимает примерно 1-0.5 сек (винт при этом загружен полностью)...
Почему так падает производительность ??? Или это нормально?
пробыал под C# и под C++ аналогично...
Здравствуйте, Qt-Coder, Вы писали:
QC>Здравствуйте, vvv848165@ya.ru, Вы писали:
QC>Дык у тебя транзакция каждый раз открывается. Ставь одну на все.
Типо совет ... можно вообще всё на asm-e без БД, тогда быстрее будет (мне важна скорость на запрос)
Поидее не должно так много занимать времени даже поодиночке ...
на PHP видел тесты — там всё гораздо быстрее
Здравствуйте, vvv848165@ya.ru, Вы писали:
VYR>Выполнение запросов на чтение занимает наносекунды, VYR>а вот добавление новой строки в таблицу занимает примерно 1-0.5 сек (винт при этом загружен полностью)... VYR>Почему так падает производительность ??? Или это нормально? VYR>пробыал под C# и под C++ аналогично...
С>>>У вас в параметре соединения стоит autocommit?
VYR>что-то не догнал как установить...
Вот его как раз ставить и не надо. И вставлять надо не по одной записи, а по несколько. Начало и завершение транзакции имеют свои накладные расходы T, и если сама вставка занимает R времени, то при вставке 100_000 строк по транзакции на строку у вас будут затраты 100_000 * (T + R), а при вставке по 1000 строк на транзакцию получится 1000 * T + 100_000 * R
И ещё я подозреваю, что драйвер mysql для php зверски оптимизирован. Основная база для языка, всё же.
Здравствуйте, vvv848165@ya.ru, Вы писали:
VYR>Выполнение запросов на чтение занимает наносекунды, VYR>а вот добавление новой строки в таблицу занимает примерно 1-0.5 сек (винт при этом загружен полностью)... VYR>Почему так падает производительность ??? Или это нормально? VYR>пробыал под C# и под C++ аналогично...
Если в таблице овердофига записей, есть индексы и вставка строки происходит не с увеличением значения индекса (для ASCENDING-индексов), а в середину куда-то, то при коммите происходит перестройка всего индекса, а это очень накладная операция.
Я не знаю как это реализовано в C#, но скорее всего вот это:
MySqlCommand cmd = new MySqlCommand("INSERT INTO `usersdb`.`users` (`firstname`, `age`) VALUES(@fn, @ag);", conn);
надо вынести за цикл, т.к. оно скорее всего на каждой итерации создает prepared statement и не освобождает его за собой пока не придет gc (IDisposable вроде явно намекает на желательность ручного освобождения).
Далее, ты не показал схему таблицы. Если у тебя там PK, а ты вставляешь в середину, то будет тормозить на перестройке индекса.
Установил всё дома (там дешевый ssd винт) — 100 записей в секунду (загружено одно ядро на максимум а винт на 50%)
откуда такая оптимизация (ведь всё тоже самое)???
Здравствуйте, 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 вроде явно намекает на желательность ручного освобождения).
Здравствуйте, Maniacal, Вы писали:M>Если в таблице овердофига записей, есть индексы и вставка строки происходит не с увеличением значения индекса (для ASCENDING-индексов), а в середину куда-то, то при коммите происходит перестройка всего индекса, а это очень накладная операция.
Это что за индексы такие, в которых вставка в середину имеет асимптотику хуже O(logN)?
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, 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;
}
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>
Убрать вывод в консоль — он замедляет.
Random() — аналогично, замедляет. Лучше сначала нагенерить массив случайных величин, и затем их юзать готовые.
Параметризованный запрос — тоже небесплатен (в примере на PHP сразу собирается целевая SQL-строка).
Здравствуйте, 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 таблицы заданий на его загрузку. Что-то там было связанное то ли с повторной загрузкой роуминговых файлов, то ли с откатом ранее загруженных, в общем там неслабые велосипедные костыли на основе шаманского бубна применялись (не мной), чтобы не вешать оракловый сервер БД. Оттуда такие воспоминания.
Лучше всё же перечитать что-то из основ СУБД. Шаманские бубны в реальных приложениях, интенсивно работающих с данными, конечно же применяются. Но уж точно не для того, чтобы оптимизировать "вставку в середину".
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, Sinclair, Вы писали:
S>Лучше всё же перечитать что-то из основ СУБД. Шаманские бубны в реальных приложениях, интенсивно работающих с данными, конечно же применяются. Но уж точно не для того, чтобы оптимизировать "вставку в середину".
Я и говорю. UPDATE середины индекса никак без перестройки индекса не обойдётся.
Здравствуйте, Maniacal, Вы писали: M>Я и говорю. UPDATE середины индекса никак без перестройки индекса не обойдётся.
Эмм. Смотря что называть перестройкой.
То, что называют перестройкой индекса во взрослых СУБД, никакого отношения к вставке в середину не имеет.
Самый популярный вид индексов в РСУБД — это B*Tree. В нём изменение любого ключа обходится без перестройки индекса, и задействует O(logN) страниц. При этом логарифм там по очень большим основаниям. То есть большинство операций задействуют две-три страницы. Рассуждения про "соседние ключи разбросаны по несоседним страницам" — вообще ни о чём. Так бывает, и очень часто, и никак не мешает быстродействию индексов.
Ваши примеры про бинарное дерево и интервальный массив не говорят ни о чём, кроме того, что бинарные деревья и отсортированные массивы в РСУБД использовать не стоит.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.