C#+MySQL как избежать блокировок таблиц?

Question

[iamserge]

Собственно использую MEMORY таблицы, одноврменно я пишу в нее (0.1 - 10/сек), обновляю в два потока отдельно разные колонки: до 10к сек - первый поток / 2-3к в секунду - второй. Но это в пиках, обычно меньше т.е. он все посчитать успевает и новых просто столько нет.

Eсли запись + один поток с UPDATE - у MySQL еще получается тянуть это, но стоит добавить второй поток и у меня очередь скапливается безнадежно большая т.к. MySQL валится с таймаутами, с ... В том числе делаю SELECT'ы пока не особо активно (т.е. не удается наладить работу) - но и их будет не так уж и мало - примерно в пределах 100к записей в мин.

Нашел тут и до сих пор не хочу в это верить:

MySQL использует табличные блокировки для подсистем хранения MyISAM, MEMORY, MERGE, но вы можете явно заблокировать таблицу в любой подсистеме хранения командой LOCK TABLES, о которой мы поговорим ниже.

1. Это просто ужас, как это обойти? Как перейти к строкам хотя бы? Лучше еще к ячейкам ...
2. Продумывал вариант через PRIMARY_KEY (он число всего-то) заменить обновление созданием новой таблицы, куда складывать пару значение PRIM_KEY + то что насчитал ... Но SELECT'ы будут медленнее в итоге, ... Да и в целом это первое что пришло в голову - не знаю как такое решение себя покажет, мало ли какие там еще встретятся проблемы ...

Таблицы в пределах 1,5 млн. записей и размер небольшой 200Мб. На диск не могу перейти, таблиц довольно много и SSD от этого чувствует себя ужасно и умирает быстро, потоков удается сделать совсем мало ... В общем совсем не вариант.

Вот собственно и проблема - сыпятся ошибки MySQL'я и иногда SELECT'ов приходится ждать очень долго (провисают на пол минуты, ну видимо из-за очереди забитой в пиках), хотя формально скорости памяти и ресурсов хватает гонять много потоков - по факту все на этом заступорилось ...

Comments

[Akina]

Всё же переходи на статические таблицы и InnoDB. Memory под капотом - та же MyISAM, и от табличной блокировки тут не убежать от слова "совсем".

С другой стороны, создаётся у меня впечатление, что ты блокируешь таблицу насмерть, пока программа там что-то считает, хотя надо минимизировать время работы с таблицей. Подключился-записал-отключился.

Какой у тебя получается поток запросов? среднее количество в штуках по каждому типу в секунду, плюс по ним же среднее количество записей...

[iamserge]

Akina, Ну UPDATE'ы я готовлю пачками по 3-20к и потом за раз отправляю, т.е. так (там один запрос вида WHERE id IN(...) на самом деле т.к. расчетные значения это int [-1;2] и можно их упаковками отправлять группируя). Второй поток наоборот, там double на выходе расчетов и там нужно каждое писать отдельно ... Но принцип тот же - приготовил строку - отправил в такую же конструкцию.

using (MySqlConnection UpgradeConnection = new MySqlConnection(SQLConnect)) {

                        MySqlCommand commTOTAL = UpgradeConnection.CreateCommand();
                        commTOTAL.CommandText = NO_pPOINTS_QUERY; // Тут уже вообще все готово как строка с кучей запросов - просто отправить ... 
                        commTOTAL.CommandTimeout = 120; // Уже докатился до 120 сек ...
                        UpgradeConnection.Open();

                        if (commTOTAL.CommandText.Length > 0)
                            commTOTAL.ExecuteNonQuery();

                    }

По количеству - запросто, но как это эффективно посмотреть? Т.е. у меня примерные цифры, по тому сколько данных есть ... Ну и да, это подождать надо, сейчас там досчиталось и пока все сносно работает, просто потому что все держится на 0.1 - 10/сек и новых нету ... Т.е. вместо 3-20к там столько, сколько записывается т.к. больше не для чего считать... Ну или я могу сделать рестарт и начать заново, т.к. это не проблема в принципе ...

Но что касается MyISAM это ужасно, просто по сравнению с SDD все просто летает =( А альтернатив нет? Ну другие free субд, я уже готов все просто переписать чтобы не отказаться от MEMORY этих ... Это как то глупо - мне дали супер скорость и тут же ее забрали, даже не дали попробовать как это работает =/

[Дмитрий]

А чего профайлинг говорит? Именно блокировка?

[iamserge]

Дмитрий, В самые лучшие времена т.е. сейчас когда я досчитал все по чуть-чуть (т.е. не даю ей иметь много не посчитанных данных) - примерно так, маленький SELECT отправил:

starting 0.000082
Executing hook on transaction 0.000005
starting 0.000008
checking permissions 0.000007
Opening tables 0.000032
init 0.000006
System lock 0.000007
Waiting for table level lock 0.037934
System lock 0.000022
optimizing 0.000013
executing 0.000011
end 0.000004
query end 0.000007
closing tables 0.000011
freeing items 0.000060
cleaning up 0.000015

[Дмитрий]

iamserge, а что за данные считаются?

[iamserge]

Дмитрий, Там расчет по формулам банальный в принципе, т.е. на выходе double как вполне себе число обычное. А где [-1;2] присваивается, это группировка т.е. запись оценивается относительно окружения и ей присваивается значение от [-1;2] в зависимости от того "какая она" - плохая или полезная. Ну то что с double пролетает на скорости света, c# вообще не замечает, что посчитал что-то. Второй расчет подольше, т.к. он даже не расчет - а скорее сравнение .... ну или расчет логических уравнений, можно и так сказать.

Это к тому чтобы вообще перейти на чистый SQL? Я о таком думал, но как мне это поможет непонятно, там ведь я буду как бы не дольше вешать таблицу т.к. буду все в запросе делать - выборку, расчеты, сравнения, а потом еще и обновления сразу - я так вообще могу ее не освободить то .... и судя по всему все это будет "висеть" все это время - пока эти дела делает шарп, закидывая в MySQL все готовеньнькое ...

[Дмитрий]

iamserge, вопрос при update сколько записей затрагивается, выбирается и какой explain этого всего.

[Дмитрий]

iamserge, вообще конечно есть твердолобые вариант - создать ramdisk и запихать туда mysql и использовать innodb. но такое себе. как бы тогда использование памяти будет весьма хреновым

[iamserge]

Дмитрий, То что касается update не пробовал, а при update я закидываю туда по 10 - 20к за раз, ну минимум 10 штук это сейчас ... Но у меня его нету, нужно судя по всему это все интегрировать как-то в c# и куда-нибудь ответ забрать ... - надо чуть доделать. Когда таблицы мрут (они же все таки memory) - я собираю в них данные из обычных (они как архивные стали) и начинаю все заново. Тогда там по очень много update'ов и получается чтобы догнать расчет до самых свежих - ну и потом опять по чуть чуть т.е. 10 шьук за раз в ногу с обновлениями свежих

как бы тогда использование памяти будет весьма хреновым

В каком смысле? Расходов больших объемов или скорость просядет?

[Дмитрий]

iamserge, ну вообщем как то тяжело рассуждать не видя запросов и их explain

ну просто mysql сам не дурак стараться запихать часто используемые данные в память. ему тяжело будет обьяснить что они уже в памяти. вообщем тут образуется широкий фронт для экспериментов и копания в настройках.

[iamserge]

Дмитрий, Да я заметил, погуглю про ramdisk - памяти много и ее я в жертву. Пока пытаюсь понять как в c# выполнить все это ... Ramdisk это такой интригующий, у меня 128Гб есть за между делом ...

set profiling = 1;
"Many UPDATE .... ";
show profiles;
show profile for query N;

[Дмитрий]

iamserge,
1. а как связан c# с мускулом в ramdisk? создаете монтируете - и говорите мускулу там хранить данные.
2. вот и интересно видеть что за many update у вас там и их explain

[iamserge]

Дмитрий, Про ramdisk я понял - про linux смотрел, но у меня Win и я надеюсь тут тоже все пройдет легко и безболезненно.

С c# связано получение детализации запроса: метод писал и добивался вывода explain, т.е. и чтобы работало и чтобы как-то выудить что-то интересное. Сейчас надо будет видимо убить все таблицы, чтобы нагрузка скаканула ого как, а пока все заработало и это UPDATE при малой нагрузке (а я пока уничтожу свои таблички):

continuing = 0.000023
starting = 0.000041
Executing hook on transaction = 0.000005
starting = 0.000006
checking permissions = 0.000005
Opening tables = 0.000035
init = 0.000009
System lock = 0.000006
Waiting for table level lock = 0.040106
System lock = 0.000058
updating = 0.000113
end = 0.000006
query end = 0.000006
closing tables = 0.000016
freeing items = 0.000069

[iamserge]

Дмитрий, Однако, запрос вида UPDATE table SET pp = 1 WHERE id IN(20k id); вешает все очень сильно
То есть получается он втискивается среди запросов из второго потока UPDATE table SET pp = 1 WHERE id = any int id;

Но как его выловить в этой куче удачно я не понимаю, т.е. выудить "тормоза" ... То есть выловить проблемный "UPDATE table SET pp = 1 WHERE id = any int"

Что кстати интересно, провисел довольно долго запрос в очереди ожидая длинного и без ошибок. Но это было всего два потока для одной таблицы. Ошибки же вовсю лезут стоит мне запустит скажем 20 потоков для 10 таблиц - тогда ничего не подвисает - просто вылеты ...

Хм, может он отдельно и правда может нормально разобраться с блокировками, а проблемы с множеством потоков которые ломятся подключаться к MySQL ...

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

[Дмитрий]

iamserge,
1. ну не мудрено что он будет вешать.
2. slow log включить
3. а какие еще запросы есть - если у вас работа только по primary key да и еще хотим все в памяти - может вам посмотреть на key -value. Или у вас там есть еще какие то select ы не по ключам?

[iamserge]

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

Я еще таймауты уже по 3 минуты поставил еще на всякий случай.

А что подразумевается под "key -value" - недопонял этот момент? Ну в целом у меня все делается по PRIMARY_KEY в WHERE, иначе никак т.к. много всякого и хочется быстрее - а это вроде бы как самый быстрый способ.

Если в смысле выборок то они вида "SELECT * FROM table WHERE id > X ORDER BY id DESC/или без LIMIT n,m" обычно, там еще все про event вытягивается, fulltext и таких ужасов нет, все скромненько совсем по минимуму. Сортировки как воздух нужны - иначе если будут не по порядку будет ничего невозможно сделать - просто куча мусора будет. Это самое основное что явно нужно, ну может мало ли еще что - но вряд ли ...

[Дмитрий]

iamserge, ну как бы если у нас вся работа взять данные по ключу, изменить положить обратно - то к чему тут реляционные базы? Покатит какой нибудь редис, тарантул или ещё чего под это заточенный.

[iamserge]

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

Хм, тарантул описывают так раз так, чтобы напомнить мне где у меня "болит":

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

Еще раз спасибо, кажется вот оно мое решение и может быть. Но думаю надо хотя бы на завтра отложить, у меня за день борьбы с MySQL уже просто голова устала, чтобы с разбегу на него запрыгнуть, на Тарантул этот или редис который я еще не гуглил даже

[Akina]

Вот всё одно не могу понять такого упорного цепляния за Memory engine.

Ну в качестве эксперимента всё же переведи таблицу в InnoDB и посмотри, как будет выглядеть процесс. Без всяких там рамдисков для начала, с таблицей на SSD. Думаю, что после тестов необходимость столь глубокой оптимизации, которой тут было запахло, тихо скончается.

Всё же MyISAM/Memory оптимизировано под высокое соотношение чтение/запись.

[iamserge]

Akina, Ну она зародилась когда я сравнил скорости RAM/SSD и их задержки. Оказалось что SSD довольно быстро с ростом числа потоков сдувается. После чего стал эксплуатировать RAM ...

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

[Everything_is_not_so_bad]

iamserge,

Ну UPDATE'ы я готовлю пачками по 3-20к и потом за раз отправляю, т.е. так (там один запрос вида WHERE id IN(...)

Монстр-запрос надо еще разобрать перед выполнением. Вообще, на такие запросы сразу внимание обращать надо.
И обновлять пачками по 1 K не пробовал, если на то пошло?

[iamserge]

Román Mirilaczvili, Нет, пока нет, а на сколько это может позволить выйграть в расходах? Просто если просто сократить время чтобы позволить другим пробиться - то кажется зачем, рано или поздно всем достанется ... А как спадет нагрузка, то там сократится это число сильно. Спадает она когда все старое насчитано и ничего кроме +0,5-10 запросов в таблицу каждую не пишется и собственно значит и считать больше не для кого. Я вообще мог бы их разделить на много маленьких - подумалось мне ... => Вопрос только где тут баланс между разером запроса и их количеством.

Что касается скорости на запись (ну просто INSERT'ы) + один поток (тот что считает double в рамках арифметики обычной + выдает кучу "UPDATE ... WHERE id = X") и все это для десяти таблиц. То есть на каждую табличку вышло по 2 потока - один пишет, один обновляет => Итого работало 20 потоков, ничего не менял кроме как убрал/вернул ENGINE = MEMORY:

1. Memory = 9 минут 3 секунды (Оперативка: 4 x 32Gb Hynix DDR4 REG ECC 2133 Mhz в четыре канала)
2. InnoDB = 1,5 часа+ - все еще идет, видимо часа два будет (GIGABYTE GP-GSM2NE3256GNTD: ничего кроме MySQL на нем нет, выделен для СУБД)

Сдается мне тут близко к тому, насколько железо отличается по скорости. С тем отличием, что для MEMORY скорость падает порядком т.к. они стоят в очереди периодически и просто не реализуют потенциал ...


Так что итого думаю:
1. Так что пока вариант с альтернативой СУБД'ам рассматривается в первую-вторую очередь, ну и приоритетно это RAM в качестве железа ... Был бы MySQL такой, ох кажется это было бы слишком хорошо ...

2.Хотя я и попробую сначала отладить это, хотя бы чтобы найти причины ошибок по логам и оценить потенциал MySQL лучше - ну и сравнить, что получится с ramdisk при "innoDB in the RAM"

[iamserge]

Все мои ошибки это:
Fatal error encountered during command execution.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (уже 240 сек. и кажется это трата времени просто ...)

[iamserge]

И дополнительно еще такая ошибка ...

'Timeout can be only be set to 'System.Threading.Timeout.Infinite' or a value > 0.
Parameter name: value'

Прямо тут на строке с ExecuteNonQuery для UPDATE, странно что же она не ловится ...

try
                            {

                                updateCommand.ExecuteNonQuery();

                            }
                            catch (MySqlException Ext)
                            {

                                checkpPointsLog.Invoke((MethodInvoker)delegate
                                {
                                    UpdateLog(Ext.Message);
                                });

                            }