Continuing the topic of recording large streams of data raised , in this one we will consider methods by which you can reduce the 'physical' size of stored data in PostgreSQL, and their impact on server performance.
We will talk about TOAST settings and data alignment.. On average, these methods will save not too many resources, but without any modification of the application code.
However, our experience turned out to be quite productive in this regard, as the storage for almost any monitoring is, by its nature, mostly append-only in terms of the data being written. And if you're curious about how to make the database write to disk instead of 200MB/s half that — please read on.
Little secrets of big data
According to the profile of our service, And since the IBS complex, whose databases we monitor, is a multi-component product with complex data structures, the queries.
to achieve maximum performance like 'multi-volume' with complex algorithmic logic. Thus, the size of each individual query instance or the resulting execution plan in the incoming log turns out to be, on average, quite large. Let's take a look at the structure of one of the tables where we write 'raw' data — that is, straight from the original log entry: A typical such table (already partitioned, of course, so this is a section template), where the most important part is the text. Sometimes it's quite large.
Remember that the 'physical' size of a single record in PG cannot exceed one page of data, but the 'logical' size is a completely different matter. To write a large value (varchar/text/bytea) into a field, the
TOAST technology is used.A typical table like this (already partitioned, of course, so this is a section template), where the most important part is the text. Sometimes, it's quite extensive.
Recall that the 'physical' size of a single entry in PG cannot exceed one data page, but the 'logical' size is a different matter. To write a large value in a field (varchar/text/bytea), the :
PostgreSQL uses a fixed page size (usually 8 KB) and does not allow tuples to occupy multiple pages. Therefore, it is not possible to directly store very large field values. To overcome this limitation, large field values are compressed and/or split into several physical rows. This occurs transparently to the user and has minimal impact on most of the server code. This method is known as TOAST …
In fact, for each table with 'potentially large' fields, a paired table with 'slices' is automatically TOAST( chunk_id integer , chunk_seq integer , chunk_data bytea , PRIMARY KEY(chunk_id, chunk_seq) );
That is, if we need to write a row with a 'large' value, the actual record will occur not only in the main table and its PK but also in TOAST and its PK. dataReducing TOAST impact However, most records are not that large,.
and should fit within 8KB.
— How can we save on this?.. Here, the column attribute STORAGE
comes to our aid: allows both compression and separate storage. This
- is the standard option for most data types compatible with TOAST. First, an attempt is made to compress, then — to store outside the table if the row is still too large. MAIN allows compression but not separate storage. (In fact, separate storage will still be performed for such columns, but only
- as a last resort , when there is no other way to reduce the row to fit on the page.) In fact, this is exactly what we need for text —to maximize compression, and if it still doesn't fit — to move it to TOAST.
This can be done right 'on the fly' with a single command: ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;How to assess the effect
Since the data flow changes daily, we cannot compare absolute figures, but relatively, the smaller the proportionwe recorded in TOAST — the better. However, there is a danger — the larger the 'physical' volume of each individual record, the 'wider' the index becomes, as it has to cover a greater number of data pages.
Before changes heap = 37GB (39%) TOAST = 54GB (57%) PK = 4GB ( 4%) After changes
Section heap = 37GB (67%) TOAST = 16GB (29%) PK = 2GB ( 4%):
In fact, we
Section have started writing to TOAST twice as infrequently.:
heap = 37GB (67%)
TOAST = 16GB (29%)
PK = 2GB ( 4%)In fact, we have started writing to TOAST half as often, which relieved not only the disk but also the CPU:
I would note that we have also started to «read» the disk less, not just «write» — since when inserting a record into a table, we also have to «read» part of the tree for each of the indexes to determine its future position in them.
Who lives well on PostgreSQL 11
After upgrading to PG11, we decided to continue the «tuning» of TOAST and noted that starting from this version, the parameter became available for configuration :
The TOAST processing code is triggered only when the value of the row that needs to be stored in the table exceeds the size of TOAST_TUPLE_THRESHOLD bytes (usually 2 KB). The TOAST code will compress and/or move field values outside the table until the row value is less than TOAST_TUPLE_TARGET bytes (a variable size, usually also 2 KB) or further reduction is no longer possible.
We decided that our data are usually either "quite short" or "very long", so we decided to limit ourselves to the minimally possible value:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);Let's see how the new settings affected disk load after readjustment:
Not bad! The average disk queue has decreased by about 1.5 times, and the disk's occupancy — by 20%! But maybe this somehow affected the CPU?
At least, it definitely did not get worse. Although, it's hard to judge if even such volumes still cannot raise the average CPU load above 5%.
The sum changes when the terms are rearranged!
As is known, a penny saves a ruble, and with our storage volumes of about 10TB/month even a slight optimization can provide a good profit. Therefore, we paid attention to the physical structure of our data — specifically how fields are "arranged" within each record of the tables.
Because due to it directly :
Many architectures provide for data alignment on machine word boundaries. For example, on a 32-bit x86 system, integers (type integer, occupying 4 bytes) will be aligned on a 4-byte word boundary, as will double-precision floating point numbers (type double precision, 8 bytes). On a 64-bit system, double values will be aligned on an 8-byte word boundary. This is yet another reason for incompatibility.
Due to alignment, the size of a table row depends on the order of field placement. This effect is usually not very noticeable, but in some cases, it can lead to a significant increase in size. For example, if you mix fields of types char(1) and integer, there will typically be 3 unused bytes between them.
Let's start with synthetic models:
SELECT pg_column_size(ROW(
'0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 bytes
SELECT pg_column_size(ROW(
'2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 bytesWhere did the extra bytes come from in the first case? It's simple — the 2-byte smallint is aligned to a 4-byte boundary before the next field, and when it stands last, there is nothing to align and no need to do so.
In theory, everything is fine, and you can rearrange fields however you like. Let's check this with real data from one of the tables, whose daily section occupies about 10-15GB.
Original structure:
CREATE TABLE public.plan_20190220
(
-- Inherited from table plan: pack uuid NOT NULL,
-- Inherited from table plan: recno smallint NOT NULL,
-- Inherited from table plan: host uuid,
-- Inherited from table plan: ts timestamp with time zone,
-- Inherited from table plan: exectime numeric(32,3),
-- Inherited from table plan: duration numeric(32,3),
-- Inherited from table plan: bufint bigint,
-- Inherited from table plan: bufmem bigint,
-- Inherited from table plan: bufdsk bigint,
-- Inherited from table plan: apn uuid,
-- Inherited from table plan: ptr uuid,
-- Inherited from table plan: dt date,
CONSTRAINT plan_20190220_pkey PRIMARY KEY (pack, recno),
CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
CONSTRAINT plan_20190220_dt_check CHECK (dt = '2019-02-20'::date)
)
INHERITS (public.plan)The section after changing the order of columns has exactly the same fields, just in a different order:
CREATE TABLE public.plan_20190221
(
-- Inherited from table plan: dt date NOT NULL,
-- Inherited from table plan: ts timestamp with time zone,
-- Inherited from table plan: pack uuid NOT NULL,
-- Inherited from table plan: recno smallint NOT NULL,
-- Inherited from table plan: host uuid,
-- Inherited from table plan: apn uuid,
-- Inherited from table plan: ptr uuid,
-- Inherited from table plan: bufint bigint,
-- Inherited from table plan: bufmem bigint,
-- Inherited from table plan: bufdsk bigint,
-- Inherited from table plan: exectime numeric(32,3),
-- Inherited from table plan: duration numeric(32,3),
CONSTRAINT plan_20190221_pkey PRIMARY KEY (pack, recno),
CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
CONSTRAINT plan_20190221_dt_check CHECK (dt = '2019-02-21'::date)
)
INHERITS (public.plan) The total section size is determined by the number of 'facts' and depends only on external processes, so we will divide the heap size (pg_relation_size) on the number of records in it — so we will obtain the average size of an actual stored record:
Minus 6% of the volume, great!
But of course, it’s not all that rosy — because we can't change the order of fields in the indexes, and therefore 'overall' (pg_total_relation_size)…
… still here saved 1.5%, without changing a single line of code. Indeed!
I should note that the above arrangement of fields is not necessarily the most optimal. Because some blocks of fields we don't want to 'split' for aesthetic reasons — for instance, a pair (pack, recno), which serves as the PK for this table.
Overall, defining the 'minimum' arrangement of fields is a relatively simple 'brute force' task. Therefore, you may achieve results even better than ours with your own data — give it a try!
Source: habr.com
