Recently, I discussed how to use standard recipes from a PostgreSQL database. Today, we will talk about how to make data writes to the database more efficient without using any 'knobs' in the config — simply by properly organizing data streams.
#1. Секционирование
This article discusses how and why it is worth organizing has already been covered, here we will discuss the practical application of some approaches within our .
‘Days long past…’
Initially, like any MVP, our project started under quite a small load — monitoring was only conducted for a handful of the most critical servers, all tables were relatively compact… But as time went on, the number of monitored hosts increased significantly, and when we tried once more to do something with one of the tables sized at 1.5TB, we realized that continuing like this was possible but rather inconvenient.
The times were almost legendary, various versions of PostgreSQL 9.x were relevant, so all partitioning had to be done 'manually' — through table inheritance and triggers with dynamic EXECUTE.
The resulting solution turned out to be sufficiently universal that it could be translated across all tables:
- An empty 'header' parent table was declared, where all the necessary indexes and triggers were described.
- Client-side writes were made to the 'root' table, and internally, using the routing trigger
BEFORE INSERTthe record was 'physically' inserted into the required section. If such a section did not yet exist — we caught the exception and … - … using a section was created based on the parent table's template with a restriction on the required date, so that during data retrieval, reading would only occur within that section.
PG10: the first attempt
However, partitioning through inheritance was historically not very well suited for working with an active write stream or a large number of descendant sections. For example, it’s worth recalling that the algorithm for choosing the appropriate section had quadratic complexity, which, with 100+ sections, you can imagine how it works…
In PG10, this situation was significantly optimized by implementing support for . Therefore, we immediately tried to apply it right after migrating the storage, but…
As it turned out after sifting through the manual, a natively partitioned table in this version:
- does not support index descriptions
- does not support triggers on it
- cannot be a 'descendant' of anything
- does not support
INSERT ... ON CONFLICT - cannot generate partitions automatically
After getting hit by the rake, we realized that there was no way to avoid modifying the application, and we postponed further research for six months.
PG10: a second chance
So, we began to tackle the arising issues one by one:
- Since triggers and
ON CONFLICTturned out to be necessary in some cases, we created an intermediate proxy table. - We eliminated 'routing' in the triggers — that is, from
EXECUTE. - We separated out a template table with all the indexes, so that they would not even be present in the proxy table.
Finally, after all this, we natively partitioned the main table. Creating a new partition still remained the application's responsibility.
We are 'developing' dictionaries
As in any analytical system, we also had 'facts' and 'dimensions' (dictionaries). In our case, these included, for example, of uniform slow queries or the text of the query itself.
'Facts' had been partitioned by days for a long time, so we could safely delete outdated partitions, and they did not bother us (after all, logs!). However, we ran into trouble with the dictionaries...
It wouldn't be correct to say that there were very many, but for about 100TB of 'facts', there was a dictionary of 2.5TB. You can't easily delete or compress anything from such a table in a reasonable time, and writing to it gradually became slower.
It seems like a dictionary… each entry should be represented exactly once… and that's correct, but!.. No one prevents us from having a separate dictionary for each day! Yes, this brings some redundancy, but it allows us to:
- write/read faster due to the smaller size of the partition
- consume less memory by working with more compact indexes
- store less data thanks to the ability to quickly delete outdated
As a result of the entire set of measures CPU load decreased by ~30%, and disk load by ~50%:
We continued to write to the database the same way, just with less load.
#2. Эволюция и рефакторинг БД
So, we concluded that we have a section for each day of data. In fact, CHECK (dt = '2018-10-12'::date) is the partitioning key and the condition for a record to fall into a specific section.
Since all reports in our service are built based on a specific date, the indexes from the "non-partitioned times" were all of the type (Server, Date, Plan Template), (Server, Date, Plan Node), (Date, Error Class, Server),…
But now each section hosts its instances of each such index… And within each section the date is a constant… It turns out that now we simply write the constant as one of the fields in each such index, which increases both its size and the search time for it, but brings no result. We left ourselves a headache, oops… The direction for optimization is clear — just
remove the date field from all indexes on partitioned tables. With our volumes, the gain is about 1TB/week And now let’s notice that this terabyte still had to be somehow written down. That is, we now also need to load the!
disk less ! This picture clearly shows the effect obtained from the cleanup we dedicated a week to:One of the major problems of overloaded systems is
#3. «Размазываем» пиковую нагрузку
excessive synchronization of unnecessary operations. Sometimes "because we didn’t notice", sometimes "it was easier that way", but sooner or later we have to get rid of it. Zooming in on the previous picture - and we see that our disk
"loads" with double amplitude between adjacent readings, which definitely should not happen "statistically" with that many operations: Achieving this is fairly simple. We had nearly
1000 servers , each processed by a separate logical thread, and each thread dumps the accumulated information to send to the database at certain intervals, like this:setInterval(sendToDB, interval)
The problem lies exactly in the fact thatall threads start at about the same time , so their sending moments almost always coincide "down to the point". Oops №2…Fortunately, this can be fixed quite easily,
by adding a "random" offset. by adding a "random" offset by time:
setInterval(sendToDB, interval * (1 + 0.1 * (Math.random() - 0.5)))#4. Кэшируем, что нужно можно
The third traditional highload problem is lack of cache where it could be.
For example, we made it possible to analyze by the nodes of the plan (all these Seq Scan on users), but to assume right away that they are all the same in bulk is a mistake.
No, of course, nothing is written to the database again; the trigger prevents that with INSERT ... ON CONFLICT DO NOTHING. But still, this data reaches the database, and unnecessary reads for conflict checking have to be performed. Oops #3…
The difference in the number of records sent to the database before/after enabling caching is obvious:
And this is the accompanying drop in the load on the storage:
Total
"Terabytes-per-day" sounds scary. If you do everything correctly, it's just 2^40 bytes / 86400 seconds = ~12.5MB/s, which was manageable even for desktop IDE drives. 🙂
And seriously, even with a tenfold overload throughout the day, you can easily stay within the capabilities of modern SSDs.
Source: habr.com
