How to effectively store canvas per-pixel in the database with subsequent display?

Question

PHP

Stack: PHP (Laravel), JS, MySQL, Nginx, Websockets.



There is a canvas with a size of 2000x1000 (~2 million pixels in total).
On this canvas, N connected clients are drawn in real time via web sockets.
The pixel data is stored in MySQL.

Table

Schema::create('pixels', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('user_id')->nullable();
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    $table->smallInteger('x');
    $table->smallInteger('y');
    $table->string('color', 18);
    $table->timestamps();
    $table->index('id');
});

With each click on the client side, information is written to a table and distributed to connected clients.

Question: when opening a site, how to display the canvas with all the pixels from the database faster and more efficiently?

How is it implemented now

Загрузка по чанкам: при открытии сайта делает N ajax запросов к апи, который возвращает чанк с пикселями. Использовал вариант с offset-limit, с where id > N and where id < N, эффект одинаковый.
Еще проблема в том, что запросы занимают большое кол-во времени, при этом блокируя другие открытые вкладки которые ждут пока завершатся предыдущие запросы с других вкладок (nginx php-fpm, очевидно завершать запросы, чтобы принять следующие плохая идея, т. к. нагрузка на сервер в эти моменты и так растет, а ожидаемое кол-во одновременно подключенных клиентов десятки тысяч (*в моих влажных мечтах)).

In general, I don’t like the idea with such an approach at all, and I understand that this is wrong.
Do you have any ideas on how this could be done? And is MySQL suitable for such purposes?

Answer 1

Well, at least for $table->string('color', 18); it is necessary to immediately tear off the hands
That is, they saved on the coordinates, 4 bytes in total, and then the hoba - 4 times more for the unfortunate color. despite the fact that there are clearly no more than a dozen flowers. And that's not to mention normalization .
According to the mind, you need to come up with a simple binary protocol that receives a stream of bytes in aligned pieces
of 2 bytes x
2 bytes y
1 byte color
4 bytes user (and there is nothing to be greedy, no BigInteger. half of the world's population is enough)
that is, only 9 bytes.
and not under a hundred, as it is now - also wrapped in jason brackets / quotes
If you think about it, then you can not immediately transfer the user. No one will watch all users, and when hovering, you can send a separate request. Then you can leave BigInteger as well.
It will turn out only 5 bytes per pixel, that is, 10 meters for the entire map. Dofiga, but uplifting.
In terms of pagination, this is some kind of nonsense. Why "offset limits" if there is already a clear breakdown.
Who hinders to request stupidly line by line? Say, 100 lines of a picture? 10 requests per megabyte.
id in this table, in fact, is not needed at all, only if Lara cannot do without it. But in my mind the primary key is xy.
From a DB to receive 2 lemons of lines certainly too not sugar
But it is possible to increase lines, at least virtually.
View or a procedure that combines, say, one hundred lines into one
I'm not sure about other repositories. In the same place, a selection of updates will probably be needed, according to the timestamp.
But in general, you can experiment with Radish.

Answer 2

I would do it a little differently, save the following in the database:
1. who, when and how drew
userID - user ID
timeStamp - timestamp
point - mouse coordinate
2. information about changing the drawing tool (brush) and tool settings
userID - user ID
timeStamp - timestamp toolType
- type of tool (brush)
toolSettings - JSON string with tool parameters (color, size, etc.)
we will get such records in 2 tables:
- in most cases, a much smaller amount of information stored
- the ability to undo both for your own actions and for the actions of other users
- the ability to subsequently display not only the result of joint creativity, but also the process itself
- the ability to turn off on the canvas with the result both the art of individual users and the result of using individual tools
PS: from myself I will add that for this task in the form as I described it, noSQL databases are more suitable, in which it will be possible to get by with one collection and several types of records:
1. tool change record

{"action": "changeTool",  userID: "xxxxxxx",  timeStamp: 0000000000000, toolType:"pen"}

2. record of changing tool parameters

{"action": "changeToolSettings",  userID: "xxxxxxx",  timeStamp: 0000000000000, setings: {
    тут у каждого инструмента что-то свое, общими наверно будут только цвет и размер
}}

3. record about the path of smearing with the tool

{"action": "path",  userID: "xxxxxxx", path:[
    {timeStamp: 0000000000000, "x": 0, "y": 0},
    {timeStamp: 0000000000000, "x": 0, "y": 0},
    {timeStamp: 0000000000000, "x": 0, "y": 0},
    {timeStamp: 0000000000000, "x": 0, "y": 0},
    ....
]}