|
List Info
Thread: Restore DB & get better performance
|
|
| Restore DB & get better performance |
|
2007-09-13 18:14:34 |
|
I finally upgraded from postgres 7.4 to 8.2 and mail inbox loading went from about 7-10s to 2-3s, so much better, but still not at the same level of 2.0.10 with its <1s loads. I have a feeling that my db structure/schema messed up.
I ran vacuum full analyze; on all the databases, is that all I should do? Should I reindex or anything, anything to get performance going better.
I would like to make a new structure and then import my data into it. Does anybody have a good method for doing this. ; I was thinking of just using phppgadmin to export data only. ; and then import it but then the sequences would be off. does anybody have a good procedure for doing this.
Thanks,
Eric
Gear up for Halo® 3 and get a $25 Best Buy gift card. It’s our way of saying thanks for using Windows Live™. Get it now! |
| Re: Restore DB & get better
performance |
 
United States |
2007-09-13 19:20:17 |
>
> I finally upgraded from postgres 7.4 to 8.2 and mail
inbox loading went
> from about 7-10s to 2-3s, so much better, but still not
at the same level
> of 2.0.10 with its <1s loads. I have a feeling that
my db
> structure/schema messed up.
>
> I ran vacuum full analyze; on all the databases, is
that all I should do?
> Should I reindex or anything, anything to get
performance going better.
>
Try tunnig your os and postgresql :
http://www.postgresql.org/docs/8.2/interacti
ve/kernel-resources.html
leonel
_______________________________________________
DBmail mailing list
DBmail dbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
|
|
| Re: Restore DB & get better
performance |
 
Austria |
2007-09-13 21:14:29 |
On Freitag, 14. September 2007 01:14 Eric Hiller wrote:
> I ran vacuum full analyze; on all the databases, is
that all I should
> do?
I've increased default_statistics_target = 1000 (was 10
before) and
after vacuum full analyze, it's a huge benefit. See my mail
from
27.Aug. to see the details.
mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0676/846 914 666
.network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg
--import"
// Fingerprint: EA39 8918 EDFF 0A68 ACFB 11B7 BA2D 060F
1C6F E6B0
// Keyserver: www.keyserver.net Key-ID:
1C6FE6B0
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
|
|
| RE: Restore DB & get better
performance |
|
2007-09-13 23:05:23 |
|
I am unable to find the post you mentioned. I am looking for a way to renew the structure of my database. and clean up existing data.
----------------------------------------> From: michael.monnerieit-management.at> To: dbmaildbmail.org> Subject: Re: [Dbmail] Restore DB & get better performance> Date: Fri, 14 Sep 2007 04:14:29 +0200>> On Freitag, 14. September 2007 01:14 Eric Hiller wrote:>> I ran vacuum full analyze; on all the databases, is that all I should>> do?>> I've increased default_statistics_target = 1000 (was 10 before) and> after vacuum full analyze, it's a huge benefit. See my mail from> 27.Aug. to see the details.>> mfg zmi> --> // Michael Monnerie, Ing.BSc ----- http://it-management.at> // Tel: 0676/846 914 666 .network.your.ideas.> // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"> // Fingerprint: EA39 8918 EDFF 0A68 ACFB 11B7 BA2D 060F 1C6F E6B0> // Keyserver: www.keyserver.net Key-ID: 1C6FE6B0
More photos; more messages; more whatever – Get MORE with Windows Live™ Hotmail®. NOW with 5GB storage. Get more! |
| RE: Restore DB & get better
performance |
|
2007-09-16 16:20:05 |
|
Well I went ahead and ran the same query and this is my result:
EXPLAIN ANALYZE SELECT p.id from dbmail_physmessage p where p.id NOT IN
(select physmessage_id from dbmail_headervalue);
; ; ; ; ; ; QUERY PLAN ;
--------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on dbmail_physmessage p (cost=29372.58..510886373.58 rows=32249 width=8) (actual time=42115240.380..42115240.380 rows=0 loops=1)
Filter: (NOT (subplan))
SubPlan
-> ; Materialize (cost=29372.58..42869.56 rows=937598 width=8) (actual time=0.027..358.673 rows=478536 loops=65186)
; -> ; Seq Scan on dbmail_headervalue (cost=0.00..24313.98 rows=937598 width=8) (actual time=0.026..940.275 rows=947228 loops=1)
Total runtime: 42115290.134 ms
(6 rows)
Thats right, nearly 12 hours!
Should I go through the database and just delete all the indexes and constraints and then re add them?
Since it is 8.2 autovacuum does not appear in the log? How can I even tell if the autovacuum is running correctly?
Thanks,
Eric
> From: michael.monnerieit-management.at
> To: dbmaildbmail.org
> Subject: Re: [Dbmail] Restore DB & get better performance
> Date: Fri, 14 Sep 2007 13:09:24 +0200
>
> On Freitag, 14. September 2007 06:05 Eric Hiller wrote:
>; > I am unable to find the post you mentioned. I am looking for a way
> > to renew the structure of my database. and clean up existing data.
>
> Sorry, see thread "massive runtime improvement for dbmail-util", my 2nd
> mail.
>
> http://www.nabble.com/massive-runtime-improvement-for-dbmail-util---tf4332845.html#a12340159
>
> mfg zmi
> --
> // Michael Monnerie, Ing.BSc ----- http://it-management.at
> // Tel: 0676/846 914 666 .network.your.ideas.
> // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
> // Fingerprint: EA39 8918 EDFF 0A68 ACFB 11B7 BA2D 060F 1C6F E6B0
> // Keyserver: www.keyserver.net Key-ID: 1C6FE6B0
Can you find the hidden words? Take a break and play Seekadoo! Play now! |
| Re: Restore DB & get better
performance |
Austria |
2007-09-16 18:09:41 |
On Sonntag, 16. September 2007 23:20 Eric Hiller wrote:
> QUERY PLAN
> Total runtime: 42115290.134 ms
> Thats right, nearly 12 hours!
It could be that your values for the ANALYZE commands in the
config are
not correct, or it really takes so much time because you
NEVER executed
that command before. How many message are there overall? So
you could
see about how many messages would be deleted. If it's more
than 20%,
you should really execute it (maybe on a weekend), to get
the index
smaller. Make a VACUUM FULL VERBOSE afterwards, to see the
improvement.
> Should I go through the database and just delete all
the indexes and
> constraints and then re add them?
I've never done that. There's the REINDEX command, so you
don't have to
delete & add. I'd try this.
> Since it is 8.2 autovacuum does not appear in the log?
How can I even
> tell if the autovacuum is running correctly?
Sorry I have 8.1, and see it in the logs. I'd suggest:
- delete unused messages (either with the command you
ANALYZEd, or by
upgrading dbmail to 2.2.6 and running dbmail-util -ay)
- VACUUM FULL, if a lot of deletes happened
- REINDEX, if you feel the need
I'm no DB expert, that's just what I would do.
mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0676/846 914 666
.network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg
--import"
// Fingerprint: EA39 8918 EDFF 0A68 ACFB 11B7 BA2D 060F
1C6F E6B0
// Keyserver: www.keyserver.net Key-ID:
1C6FE6B0
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
|
|
| RE: Restore DB & get better
performance |
|
2007-09-16 18:37:36 |
|
What would selecting those rows even do, wouldnt I want to delete those rows?
VACUUM FULL VERBOSE would freeze the database correct?
I have gone through and added alot of the new additions to the schema to see if that helps speed it up. I know how to reindex one index, is there a way to reindex all indexes?
> From: michael.monnerieit-management.at
> To: dbmaildbmail.org
> Subject: Re: [Dbmail] Restore DB & get better performance
> Date: Mon, 17 Sep 2007 01:09:41 +0200
>
> On Sonntag, 16. September 2007 23:20 Eric Hiller wrote:
>; > QUERY PLAN
>; > Total runtime: 42115290.134 ms
> > Thats right, nearly 12 hours!
>;
> It could be that your values for the ANALYZE commands in the config are
> not correct, or it really takes so much time because you NEVER executed
> that command before. How many message are there overall? So you could
> see about how many messages would be deleted. If it's more than 20%,
> you should really execute it (maybe on a weekend), to get the index
> smaller. Make a VACUUM FULL VERBOSE afterwards, to see the improvement.
>
> > Should I go through the database and just delete all the indexes and
> > constraints and then re add them?
>
> I've never done that. There's the REINDEX command, so you don't have to
> delete & add. I'd try this.
>
> > Since it is 8.2 autovacuum does not appear in the log? How can I even
> > tell if the autovacuum is running correctly?
>
> Sorry I have 8.1, and see it in the logs. I'd suggest:
>
> - delete unused messages (either with the command you ANALYZEd, or by
> upgrading dbmail to 2.2.6 and running dbmail-util -ay)
> - VACUUM FULL, if a lot of deletes happened
> - REINDEX, if you feel the need
>
> I'm no DB expert, that's just what I would do.
>
> mfg zmi
> --
> // Michael Monnerie, Ing.BSc ----- http://it-management.at
> // Tel: 0676/846 914 666 .network.your.ideas.
> // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
> // Fingerprint: EA39 8918 EDFF 0A68 ACFB 11B7 BA2D 060F 1C6F E6B0
> // Keyserver: www.keyserver.net Key-ID: 1C6FE6B0
Gear up for Halo® 3 and get a $25 Best Buy gift card. It’s our way of saying thanks for using Windows Live™. Get it now! |
| Re: Restore DB & get better
performance |
Austria |
2007-09-17 13:46:58 |
On Montag, 17. September 2007 01:37 Eric Hiller wrote:
> What would selecting those rows even do, wouldnt I want
to delete
> those rows?
The SELECT is only to see how many un(wanted,needed,used)
messages there
are, the DELETE does the work.
> VACUUM FULL VERBOSE would freeze the database
correct?
I'd guess, but the postgresql docs should tell.
> I have gone through and added alot of the new additions
to the schema
> to see if that helps speed it up.
The more indices, the slower inserts/deletes/updates are. An
index is
for SELECT only.
> I know how to reindex one index,
> is there a way to reindex all indexes?
I don't know, but the postgresql docs should tell.
Sorry, too lazy now to read docs. 
mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0676/846 914 666
.network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg
--import"
// Fingerprint: EA39 8918 EDFF 0A68 ACFB 11B7 BA2D 060F
1C6F E6B0
// Keyserver: www.keyserver.net Key-ID:
1C6FE6B0
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
|
|
[1-8]
|
|