Обсуждение: Old record migration to another table made the db slower

Hi,

I am planing to backup archived WAL files from master to another machine, is there a way to check and make sure the
backuparchive file are all good and no any file missing or corrupted?  

regards

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

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

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

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