Thread: >24 hour restore
I have, what appears to be a big problem.
Machine specs
AMD 2100+,
1 GIG SDRam,
3 WD HD's
1 - 20 Gig -15 Gig system and 5 Gig Swap
mounted as /
2 - 80 Gig (8 M Cache) in Redhat software RAID 1 (mirror) using Adaptec
1200 as an IDE Controller
mounted as /usr/local/pgsql
Redhat 8 w/ latest kernel and all updates.
I have a much slower machine that has been running my database. We are
trying to upgrade to the above machine to make things a bit faster.
I followed "Tips for upgrading PostgreSQL from 6.5.3 to 7.0.3" by Mark
Stosberg with only a few changes
[postgres@sqlsrv root]# pg_dump -cs mydbtable >sqlschema.sql
[postgres@sqlsrv root]# pg_dump -a mydbtable > sqldump.sql
sqlschema.sql = 900K
sqldump.sql = 2.4G
[sftp files to aforementioned machine]
[postgres@newsqlsrv root]# psql -e mydbtable <sqlschema.sql 2>&1 | tee
schema-full-results.txt; grep ERROR schema-full-results.txt
>schema-err-results.txt
All this works perfectly, quite fast but when I ran....
[postgres@newsqlsrv root]# psql -e <sqldump.sql 2>&1 | tee
inserts-full-results.txt; grep ERROR inserts-full-results.txt
>inserts-err-results.txt
It started off quick, but it got to the first table w/ any real data in it
(only about 30k records) and acted like it was frozen. I left it running
all night, it finished that table and started on others but it hasnt even
gotten to the big tables (2 @ about 9 million records). At this pace it
will take several days to finish the restore.
I hope this is something easy/stupid that I have missed. I know that w/
mirroring my write times are not improved, but they are DEFINATLY not this
bad.
I hope that I havent missed any information.
Thank you in advance for any direction.
Chad
Have a look through the log files for both postgresql and the kernel. You could be having issues like SCSI time outs, or a failed disk in a RAID, or there could be some hints in the postgresql logs about what's happening. What does top show? high CPU load, low? iostat ? vmstat ? On Wed, 28 May 2003, Chad Thompson wrote: > I have, what appears to be a big problem. > > Machine specs > AMD 2100+, > 1 GIG SDRam, > 3 WD HD's > 1 - 20 Gig -15 Gig system and 5 Gig Swap > mounted as / > 2 - 80 Gig (8 M Cache) in Redhat software RAID 1 (mirror) using Adaptec > 1200 as an IDE Controller > mounted as /usr/local/pgsql > Redhat 8 w/ latest kernel and all updates. > > I have a much slower machine that has been running my database. We are > trying to upgrade to the above machine to make things a bit faster. > > I followed "Tips for upgrading PostgreSQL from 6.5.3 to 7.0.3" by Mark > Stosberg with only a few changes > > [postgres@sqlsrv root]# pg_dump -cs mydbtable >sqlschema.sql > [postgres@sqlsrv root]# pg_dump -a mydbtable > sqldump.sql > > sqlschema.sql = 900K > sqldump.sql = 2.4G > > [sftp files to aforementioned machine] > > [postgres@newsqlsrv root]# psql -e mydbtable <sqlschema.sql 2>&1 | tee > schema-full-results.txt; grep ERROR schema-full-results.txt > >schema-err-results.txt > > All this works perfectly, quite fast but when I ran.... > > [postgres@newsqlsrv root]# psql -e <sqldump.sql 2>&1 | tee > inserts-full-results.txt; grep ERROR inserts-full-results.txt > >inserts-err-results.txt > > It started off quick, but it got to the first table w/ any real data in it > (only about 30k records) and acted like it was frozen. I left it running > all night, it finished that table and started on others but it hasnt even > gotten to the big tables (2 @ about 9 million records). At this pace it > will take several days to finish the restore. > > I hope this is something easy/stupid that I have missed. I know that w/ > mirroring my write times are not improved, but they are DEFINATLY not this > bad. > > I hope that I havent missed any information. > Thank you in advance for any direction. > > Chad > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
On Wed, May 28, 2003 at 09:12:23AM -0600, Chad Thompson wrote:
>
> It started off quick, but it got to the first table w/ any real data in it
> (only about 30k records) and acted like it was frozen. I left it running
> all night, it finished that table and started on others but it hasnt even
> gotten to the big tables (2 @ about 9 million records). At this pace it
> will take several days to finish the restore.
This makes me think you have a trigger problem. You don't say what
version you're running, but my guess is that you need to disable all
your triggers, and remove all your indices, before you start loading
the data. Re-enable them afterwards.
By building the schema first, then loading the data, you're spending
cycles running triggers &c.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
> On Wed, May 28, 2003 at 09:12:23AM -0600, Chad Thompson wrote: > > > > It started off quick, but it got to the first table w/ any real data in it > > (only about 30k records) and acted like it was frozen. I left it running > > all night, it finished that table and started on others but it hasnt even > > gotten to the big tables (2 @ about 9 million records). At this pace it > > will take several days to finish the restore. > > This makes me think you have a trigger problem. You don't say what > version you're running, but my guess is that you need to disable all > your triggers, and remove all your indices, before you start loading > the data. Re-enable them afterwards. > > By building the schema first, then loading the data, you're spending > cycles running triggers &c. > This was my first thought. After about an hour of running, I stopped the process, edited the schema file to remove all the foreign keys and triggers. I then started it again. So there SHOULD be no triggers right now. UPDATE: I stopped the restore, before it was stopped, top showed postmaster using 17% CPU. After stopping I noticed that it DID fill my largest table (1.16 M tuples) over night. So I am editing the dump file to continue where it left off. ( vi is the only thing that is not choking on the 2.4 gig file) That is good news because that means it wont take 7-10 days to import, just 1-2. As for version (oops) my old version was 7.3.1 and I am moving to 7.3.2 Any other ideas? TIA Chad Oh, a bit off topic... I remember that I wanted to move the WAL files off of the raid but forgot to do it on start up. Can I do that now that the system is setup? Where would I find docs to tell me about that?
On Wed, May 28, 2003 at 11:59:49AM -0600, Chad Thompson wrote:
> This was my first thought. After about an hour of running, I stopped the
> process, edited the schema file to remove all the foreign keys and triggers.
> I then started it again. So there SHOULD be no triggers right now.
Hmm.
> UPDATE: I stopped the restore, before it was stopped, top showed postmaster
> using 17% CPU. After stopping I noticed that it DID fill my largest table
> (1.16 M tuples) over night. So I am editing the dump file to continue where
> it left off. ( vi is the only thing that is not choking on the 2.4 gig file)
> That is good news because that means it wont take 7-10 days to import, just
> 1-2.
Sounds like you have an I/O problem.
> As for version (oops) my old version was 7.3.1 and I am moving to 7.3.2
Why don't you just shut down your 7.3.1 postmaster and start 7.3.2?
This requires no initdb. If you're changing machines (ISTR you are),
then copy the tree, assuming the same OS.
> Oh, a bit off topic... I remember that I wanted to move the WAL files off of
> the raid but forgot to do it on start up. Can I do that now that the system
> is setup? Where would I find docs to tell me about that?
Sure. Stop the postmaster, copy the pg_xlog directory to the target
location, then make a soft link. (I usually use cp and move the old
dir out of the way temporarily to start with, just in case.)
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
* Chad Thompson <chad@weblinkservices.com> [28.05.2003 19:08]: > I hope this is something easy/stupid that I have missed. I know that w/ > mirroring my write times are not improved, but they are DEFINATLY not this > bad. Well, I have had something similar to your case, except for size - it's was about 1 Gb. I've dropped all foreign keys, triggers and, also, all indexes. As I've found, each index takes additional time for inserts/updates/deletes, so it's recommended to create indexes after data manipulations. If this will not help, I don't know. May be hardware problems... -- Victor Yegorov