Обсуждение: Performance
Hi,We want to work with PostgreSQL in our new project. I need your opinion on the best way to create a database.
Description of our Project:It will be in Client/Server Architecture. Windows Application users will access the server as clients and they are all in different locations. There will be a simple ERP system that will perform CRUD transactions and report them.
We are considering connecting to the Embarcadero Firedac dataset. We can also connect clients with PosgreRestAPI.Our number of clients can be between 5k-20K.We have a maximum of 200 tables consisting of invoice, order, customer, bank and stock information. I can create a second Postgre SQL for reporting if necessary.
Question 1 :Should we install PostgreSQL on Windows server operating system or Linux operating system?
2:Is it correct to open a field named client_id for each table, for example the customer table, and use this field in CRUD operations to host the same single customer table for all users?
3:Create a separate table for each User? (result: 5000 users x 200 Tables = 1,000,000 tables)4:Create a database per user? (result: 5000 databases)5:Is each user a separate schema? (result: 5000 schemas)Can you share your ideas with me?Thank you.
Hi,
I have run a test with pgbench against two cloud vendors (settings, parameters almost the same).
Both Postgres (or whatever they do internally when they call it as Postgres offering, NOT Aurora or so :-) )
I have got a strange result that cloud vendor 1 is performing almost everywhere better in matter of
read and write but except in the init phase of pgbench it took almost double the time.
pgbench -i -IdtGvp -s 3000 "${PG_DATABASE}"
pgbench -c 50 -j 10 -P 60 -r -T 3600 "${PG_DATABASE}"
| Metric | cloud vendor 1 (small) | cloud vendor 1 (large) | cloud vendor 2 (small) | cloud vendor 2 (large) | |----------------------------------|------------------------|------------------------|-------------------|-------------------| | **Initialization Time** | 60m52.932s | 3h0m8.97s | 32m7.154s | 5h14m16s | | **Benchmark Duration** | 3600s (1 hour) | 3600s (1 hour) | 3600s (1 hour) | 3600s (1 hour) | | **Transactions per Second** | 399.460720 | 9833.737455 | 326.551036 | 3314.363264 | | **Latency Average (ms)** | 125.124 | 6.507 | 153.106 | 19.309 | | **Latency StdDev (ms)** | 154.483 | 44.403 | 59.522 | 4.015 | | **Initial Connection Time (ms)** | 557.696 | 174.318 | 1688.474 | 651.087 | | **Transactions Processed** | 1,438,437 | 35,400,215 | 1,175,081 | 11,929,631 | | Statement (ms) | cloud vendor 1 (small) | cloud vendor 1 (large) | cloud vendor 2 (small) | cloud vendor 2 (large) | |-----------------------------|------------------------|------------------------|-------------------|-------------------| | BEGIN | 8.599 | 0.545 | 9.008 | 1.398 | | UPDATE pgbench_accounts | 38.648 | 2.031 | 27.124 | 4.722 | | SELECT pgbench_accounts | 12.332 | 0.676 | 17.922 | 1.798 | | UPDATE pgbench_tellers | 17.275 | 0.853 | 20.843 | 1.831 | | UPDATE pgbench_branches | 18.478 | 0.862 | 21.941 | 1.743 | | INSERT INTO pgbench_history | 16.613 | 0.827 | 18.710 | 1.501 | | END | 13.177 | 0.708 | 37.553 | 6.317 |
Of course no one knows the magig underneath what some cloud vendors are doing underneath but does anyone have some
ideas what the reason could be or how I could do better testing to find this out?
Cheers
Dirk
On Thu, 2024-02-01 at 10:23 +0100, Dirk Krautschick wrote: > I have run a test with pgbench against two cloud vendors (settings, parameters almost the same). > Both Postgres (or whatever they do internally when they call it as Postgres offering, NOT Aurora or so :-) ) > > I have got a strange result that cloud vendor 1 is performing almost everywhere better in matter of > read and write but except in the init phase of pgbench it took almost double the time. Nobody except those vendors could tell you for certain, but perhaps on the one system the initial data load is fast, because you have not yet exceeded your I/O quota, and then I/O is throttled. Yours, Laurenz Albe