Postgres as a hunch. We calculate the percentage of import substitution in Highload mode from 1C

Methods for investigating performance problems at the level of PostgreSQL DBMS operation (New section!) :: Problems and solutions :: Methodological support for developers and administrators of 1C:Enterprise 8 (1c.ru)

The Postgres architecture with the necessary views is well shown here

I initially adjusted the parameters in PostgresSQL.conf according to the 1C recommendations and the main ones were already set by the installer (full list using the Show all command) attachment

The distribution vendor immediately installs

# Add settings for extensions here

#Options for 1C:

escape_string_warning = off

standard_conforming_strings = off

shared_preload_libraries="online_analyze, plantuner, pg_stat_statements"

online_analyze.table_type="temporary"

online_analyze.verbose="off"

online_analyze.local_tracking = 'on'

plantuner.fix_empty_table="on" 

online_analyze.enable = on

lc_messages="en_US.utf-8"

I checked and corrected according to the article PostgreSQL settings for working with 1C:Enterprise. Part 2 :: PostgreSQL :: Methodological support for 1C:Enterprise 8 developers and administrators (1c.ru) only

Shared_buffers at 8 GB, and temp_bufffers at 32 MB which is enough for a load test. I left the rest as in the distribution kit, since it makes sense to do further fine tuning after the test on medium loads

Resource analysis using Windows showed that the SSD disk is loaded by a quarter of the possible IOPS, everything is OK with the network, but the processor load with 32 logical processors is under 100%! In windows, it’s good to watch it through Process explorer.

The picture shows how Postgres processes are arranged, they are created approximately equal to the number of testing threads, and if each one loads under 2%, the total load will be 100%. On the subd server 2 processors with 16 cores, hyper threading creates 32 logical processors i.e. loading of one logical processor can be no more than 3.1%

Analysis pg_stat_statements showed that SELECT FASTTRUNCATE and ANALYZE pg_temp.tt* take 76% of the time – is that really it? No, these are service procedures – it can’t be that bad.

Used query

--SELECT pg_stat_statements_reset(); --сброс счетчика
SELECT 
        pss.userid,
        pss.dbid,
        pd.datname as db_name,
        round((pss.total_exec_time + pss.total_plan_time)::numeric, 2) as total_time, 
        pss.calls, 
        round((pss.mean_exec_time+pss.mean_plan_time)::numeric, 2) as mean, 
        round((100 * (pss.total_exec_time + pss.total_plan_time) / sum((pss.total_exec_time + pss.total_plan_time)::numeric) OVER ())::numeric, 2) as cpu_portion_pctg,
        substr(pss.query, 1, 200) short_query
FROM pg_stat_statements pss, pg_database pd 
WHERE pd.oid=pss.dbid
ORDER BY (pss.total_exec_time + pss.total_plan_time)
DESC LIMIT 30;

Decided to switch to debugging and enable Postgres log level via log_min_messages = debug1 and then I noticed that he was standing log_min_messages = error, but the trace logs are huge. everything is written in them! It doesn’t match the documentation had to switch to panic.

Important !!! Make sure that the trace logs do not write anything extra, even if the parameters are set correctly, apparently I stumbled upon some kind of release errors.

Pyrrhic victory for Postgres?

Making sure that logging no longer writes too much. The test was run again. Results by numbers pleased ~ 1500 ORM records per second

For comparison, MS SQL on the same database with the same data 1400 . Difference 7% in favor of Postgres.

However, Postgres has the results of hardware measurements, and the time spent on operators did not please – 40% of the processor time.

Which generates postgresbackend

At the same time, Disk transfers (blue dotted line) do not reach 10 thousand Iops. Queues Current disk queue (brown) length can be said no.

The same base in the same conditions, but on average MS SQL does not reach 30% of the processor load

At the same time, from the side of MS SQL Disk transfers (dashed blue line) there are already almost 10 thousand iops. Current disk queues (brown) length happen. Of course, this does not mean the advantages of MS SQL, just a different architecture.

What does this mean?

If I increase the load from the 1C cluster with only two application servers, we will most likely get that MS SQL has a performance margin per CPU that is a third more than Postgres, which will simply run out of processor resources faster !!! Of course, asynchronous commit will be enabled for both databases to avoid waiting on the transaction log.

Or maybe the current 7%-10% of Postgres winnings will equalize the chances (more on that below)? Or MS SQL under heavy load will rest against Waits on SSD? Intrigue, right?

What did the Elephant do when He came to the Field? Analysis of a “successful” launch.

If you look at Trace using Postgres (see above for inclusion nuances)