On Saturday, 20 July, 2024 at 10:55:30 pm IST, Francisco Olarte <folarte@xxxxxxxxxxxxxx> wrote:
Hi:
Please, avoid top posting, specially when replying to long mail with
various points,m it makes it nearly impossible to track what you are
replying to.
OK
Please, avoid top posting, specially when replying to long mail with
various points,m it makes it nearly impossible to track what you are
replying to.
OK
> Executed
> VACUUM FULL VERBOSE
> followed by
> REINDEX DATABASE dbname;
As it has been already said, vacuum full implies reindex ( it
basically copies old table to a new one, including indexes, swaps
them, deletes old one ).
> It didn't increase the performance, still time out happened. VACUUM didn't find any dead rows in that particular table.
The no dead rows is the interesting part.
> followed by
> REINDEX DATABASE dbname;
As it has been already said, vacuum full implies reindex ( it
basically copies old table to a new one, including indexes, swaps
them, deletes old one ).
> It didn't increase the performance, still time out happened. VACUUM didn't find any dead rows in that particular table.
The no dead rows is the interesting part.
Yes no dead rows.
> Yes, the actual query and conditions were not given in my first comment. Actually where condition is not on the date field alone and the query with current date is only a sample.
Then they are worthless and harmful. Query time problems is normally
data and statistics dependent and always query dependent.
The query you posted has only two ways to be done, and few ways to be
improved. Suggestions for it will probably be harmful for other
queries.
Actual Query:
select source_node_id, create_time from sym_data where table_name = 'tx_combined_sales_header' and ((event_type = 'I' and row_data like '"F92DD7AA237A45D99CA5741DF73EA3D1"%') or (event_type in ('U', 'D') and pk_data like '"F92DD7AA237A45D99CA5741DF73EA3D1"')) and create_time >= '2024-07-18 01:43:32.981' order by create_time desc
> What I did,
> 1. Took backup (pg_dump) of the database from the server it's running. [ Server config. Xeon Silver 4208, Windows Server 2019 Standard ].
> 2. Restored in another desktop system, installing PG 11 afresh.
> 3. Performance was excellent. Within milliseconds I got the result. Application was run from the desktop.
> 4. Restored the database in the same server, as another database. Improved performance but doesn't match the performance of the desktop. Application run from the server itself.
What you did not:
- Show your tables and indexes.
- Show your real queries.
- Tell us what "the application is" ( i.e., "psql", "a java app using
JDBC", ... )
> Now server got two databases with exactly the same data. Old one takes more than 15 minutes; newer one takes few seconds. Application run from the server and also from clients. In both conditions, the result is same.
After what has been happening, I have to ask. Do you mean ONE server
with two databases, or TWO servers with one database each? Also, what
are the especs of the server and the desktops, and the postgres
configuration on each? A misconfigured server can easily send query
time through the roof ( i.e., DB servers want real RAM, if you
configure postgres with too much mem and it swaps you can make a query
really slow )
I thought I'm clear. My bad.
2 computers were involved in total. One Xeon Server with Windows 2019 Standard and other one is Intel i5 based Desktop with Windows 10.
I took backup (pg_dump) from windows server machine.
And restored in the same server as another database. Now we have 2 databases with identical data in Windows Server. The actual query (given above) is taking more than 15 min in the original database and takes a second in the restored database.
I took backup (pg_dump) from windows server machine.
And restored in the same server as another database. Now we have 2 databases with identical data in Windows Server. The actual query (given above) is taking more than 15 min in the original database and takes a second in the restored database.
Also I restored the database in Desktop machine also, which takes ms only.
All PG settings are set at installation, and nothing changed by us.
> What else I need to do to correct this issue?
No clue.
I have done Vacuum, Re-Index in the original database. No improvement. Anything else that I can do to make the original database to perform just like the restored database?
> I can easily replace the old database with the backup. Is that only option?
Ah, one clue. From the info I have in this and previous mails, that is
the only option for me. Having more info someone may have ideas, but
so far the only thing I have concluded is three databases, fast in
server, slow in server and desktop, test only. So my only options are
fast server and slow server. So my solution would be "use fast
server". As I said, maybe having more data we could suggest "analyze
that table with these parameters", or "make this index" or "rewrite
this condition in this way", but this is impossible to do with the
data you provided.
What else ?
Regards.
Francisco Olarte.
