Old record migration to another table made the db slower
Hi all,
I have moved a lot of old records from one table say original_data_table to another table say old_data_table which is newly created for the old records. There were around 15 milliion old records to move. After this migration I have done standard vacuum on new old_data_table as well as on original_data_ table.
Now the problem is some queries are running very slow especially 'select max(date) from one of the table'. Also db seem to be a bit slower after all these works. Actual intention of the migration of old records was to speed up the original_data_table which is used frequently by users. How can I get this problem get this fixed? What are the steps to be followed for the same?
My postgresql version is 7.4.3. Thanks in Advance for your helps,Arun
Hi,
I am planing to backup archived WAL files from master to another machine, is there a way to check and make sure the backup archive file are all good and no any file missing or corrupted?
regards
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 5 Září 2013, 8:52, Arun P.L wrote:
Hi all,
I have moved a lot of old records from one table say original_data_table
to another table say old_data_table which is newly created for the old
records. There were around 15 milliion old records to move. After this
migration I have done standard vacuum on new old_data_table as well as on
original_data_ table.Now the problem is some queries are running very slow especially 'select
max(date) from one of the table'. Also db seem to be a bit slower after
all these works. Actual intention of the migration of old records was to
speed up the original_data_table which is used frequently by users. How
can I get this problem get this fixed? What are the steps to be followed
for the same?
My postgresql version is 7.4.3. Thanks in Advance for your helps,Arun
Hi,
First of all, 7.4 reached end-of-life ~3 years ago. And 7.4.3 is not even
the last release in this branch - there are ~30 updates. Go and upgrade to
7.4.30 now.
We'll need a bit more info about the tables / queries. What exactly have
you done? Show us the commands you've executed. Then show us EXPLAIN
ANALYZE for the queries.
Tomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ascot.moss@gmail.com wrote:
I am planing to backup archived WAL files from master to another machine, is there a way to check and
make sure the backup archive file are all good and no any file missing or corrupted?
The only 100% safe way would be to restore them, e.g. with
a standby database.
PostgreSQL 9.3 will also have "xlogdump" which you can run on the WAL file.
If that does not gag, the file is probably ok.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Sep 5, 2013 at 6:05 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
On 5 Září 2013, 8:52, Arun P.L wrote:
Actual intention of the migration of old records was to
speed up the original_data_table which is used frequently by users. How
can I get this problem get this fixed? What are the steps to be followed
for the same?We'll need a bit more info about the tables / queries. What exactly have
you done? Show us the commands you've executed. Then show us EXPLAIN
ANALYZE for the queries.
As you are seeing different plans for the same queries on both tables,
did you actually run ANALYZE on the new table to gather stats that
would allow the planner to build more consistent plans?
My postgresql version is 7.4.3. Thanks in Advance for your helps,Arun
First of all, 7.4 reached end-of-life ~3 years ago. And 7.4.3 is not even
the last release in this branch - there are ~30 updates. Go and upgrade to
7.4.30 now.
In your case it might be even worth directly to upgrade to 9.2.
pg_dump of 9.2 is supported down to 7.4 if I recall correctly.
--
Michael
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Sep 6, 2013 at 4:04 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
ascot.moss@gmail.com wrote:
I am planing to backup archived WAL files from master to another machine, is there a way to check and
make sure the backup archive file are all good and no any file missing or corrupted?The only 100% safe way would be to restore them, e.g. with
a standby database.
To check if any file is missing, you can check directly with the WAL
file names. Note that the format name of WAL files has changed a bit
in 9.3 as they can end with FF. In 9.2 and prior versions, it finished
at most with FE. This won't ensure that the content is safe though, so
use one of the methods mentioned previously in this thread.
--
Michael
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general