Thread: Performance problems
Hello all, I've a little performance problem with psql 7.2.3. I have a little database with approx. eight tables. They have two or six columns and less than 2000 rows. Three of them are regularly updated by a small C++-tool. It inserts or updates entries of these three tables. All entries of one of these tables are deleted with "Delete from..." everytime the tools runs (e.g. every 20 minutes). I'm using indices on all tables. Now I can observe the following phenomenon: The total runtime of the tool is increasing up to a certain peak and then it drops to the half of the peak. The time interval from one peak to the next is also increasing as well as the total value of the peak. But the total time for database access is not only increasing for write access but also for read access. The time it takes to Select things from the database is increasing too. I unfortunately don't understand the whole thing totally, but if I dump the database (with pg_dump), delete it and restore it, the time values for reading and writing have decreased to a normal level and begin to increase again. It seems to me, that I do sth. fundamentally wrong :( But even after searching google and the postgresql archives I don't see the light at all... Please help me out here. Thanks and best regards Marco Wedekind
Hello,
VACCUM it's your friend, look at the docs or search the list.
Regards.
El vie, 25 de 04 de 2003 a las 16:53, marco escribió:
> Hello all,
>
> I've a little performance problem with psql 7.2.3.
>
> I have a little database with approx. eight tables. They have two or six
> columns and less than 2000 rows.
>
> Three of them are regularly updated by a small C++-tool. It inserts or
> updates entries of these three tables. All entries of one of these
> tables are deleted with "Delete from..." everytime the tools runs (e.g.
> every 20 minutes).
>
> I'm using indices on all tables.
>
> Now I can observe the following phenomenon:
>
> The total runtime of the tool is increasing up to a certain peak and
> then it drops to the half of the peak.
> The time interval from one peak to the next is also increasing as well
> as the total value of the peak.
>
> But the total time for database access is not only increasing for write
> access but also for read access.
>
> The time it takes to Select things from the database is increasing too.
>
> I unfortunately don't understand the whole thing totally, but if I dump
> the database (with pg_dump), delete it and restore it, the time values
> for reading and writing have decreased to a normal level and begin to
> increase again.
>
> It seems to me, that I do sth. fundamentally wrong :( But even after
> searching google and the postgresql archives I don't see the light at
> all...
>
> Please help me out here.
>
> Thanks and best regards
>
> Marco Wedekind
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Manuel Durán Aguete <mdaguete@alsernet.es>
On Friday 25 April 2003 20:23, marco wrote: > I unfortunately don't understand the whole thing totally, but if I dump > the database (with pg_dump), delete it and restore it, the time values > for reading and writing have decreased to a normal level and begin to > increase again. > > It seems to me, that I do sth. fundamentally wrong :( But even after > searching google and the postgresql archives I don't see the light at > all... You need to vacuum full everytime you delete large amount of rows and vacuum analyze every time you insert/update large amount of rows.. I would say large amount==50K row is a good start.. So after 20 runs of tool, run vacuum once.. Try it and let us know.. Shridhar
you need to do vacuum full on a regular basis, like at about the time that the select/update times increase by 10-25%. Youcould put that on a chron job. marco wrote: > Hello all, > > I've a little performance problem with psql 7.2.3. > > I have a little database with approx. eight tables. They have two or six > columns and less than 2000 rows. > > Three of them are regularly updated by a small C++-tool. It inserts or > updates entries of these three tables. All entries of one of these > tables are deleted with "Delete from..." everytime the tools runs (e.g. > every 20 minutes). > > I'm using indices on all tables. > > Now I can observe the following phenomenon: > > The total runtime of the tool is increasing up to a certain peak and > then it drops to the half of the peak. > The time interval from one peak to the next is also increasing as well > as the total value of the peak. > > But the total time for database access is not only increasing for write > access but also for read access. > > The time it takes to Select things from the database is increasing too. > > I unfortunately don't understand the whole thing totally, but if I dump > the database (with pg_dump), delete it and restore it, the time values > for reading and writing have decreased to a normal level and begin to > increase again. > > It seems to me, that I do sth. fundamentally wrong :( But even after > searching google and the postgresql archives I don't see the light at > all... > > Please help me out here. > > Thanks and best regards > > Marco Wedekind > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
marco <wedekind@mpi-halle.mpg.de> writes:
> It seems to me, that I do sth. fundamentally wrong
You aren't vacuuming often enough ...
regards, tom lane
You know,
It'd be nice if there was a system table, or command that showed the number and/or percentage of tuples needing to
bevacuumed. If table are only affected by tuples formerly in them, then the table/function could show the value per
table.If any discarded tuples affect all tables, then a global table/function would be warranted.
A minimally compute intensive chron job or ON DELETE trigger could then call vacuum full at a certain percentage.
Also,what kind of memory management ( in the postgres application ) could be written that pushes deleted/unused tuples
outof any caches or scopes to at least lengthen the time between vacuums?
Shridhar Daithankar wrote:
> On Friday 25 April 2003 20:23, marco wrote:
>
>>I unfortunately don't understand the whole thing totally, but if I dump
>>the database (with pg_dump), delete it and restore it, the time values
>>for reading and writing have decreased to a normal level and begin to
>>increase again.
>>
>>It seems to me, that I do sth. fundamentally wrong :( But even after
>>searching google and the postgresql archives I don't see the light at
>>all...
>
>
> You need to vacuum full everytime you delete large amount of rows and vacuum
> analyze every time you insert/update large amount of rows..
>
> I would say large amount==50K row is a good start.. So after 20 runs of tool,
> run vacuum once.. Try it and let us know..
>
> Shridhar
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
On Friday 25 April 2003 21:13, Dennis Gearon wrote: > You know, > It'd be nice if there was a system table, or command that showed the > number and/or percentage of tuples needing to be vacuumed. If table are > only affected by tuples formerly in them, then the table/function could > show the value per table. If any discarded tuples affect all tables, then a > global table/function would be warranted. > > A minimally compute intensive chron job or ON DELETE trigger could then > call vacuum full at a certain percentage. Also, what kind of memory > management ( in the postgres application ) could be written that pushes > deleted/unused tuples out of any caches or scopes to at least lengthen the > time between vacuums? Why don't you take a look at pgavd? It will periodically connect to database and check if it needs vacuum. just that it won't issue vacuum full because it locks table and IMO only administrator should do it. http://gborg.postgresql.org/project/pgavd/projdisplay.php Shridhar
On Fri, 2003-04-25 at 17:05, Manuel Durán Aguete wrote: > Hello, > VACCUM it's your friend, look at the docs or search the list. > > Regards. > Well, you are right. I Vacuum this db once every day with a cronjob like this: > crontab -l ... 0 0 * * 0 psql -U <user_name> <db_name> -c vacuum This should work... But even after running vacuum like that I have the same situation as before. It takes more time to read or write the database than it would normally take... But as all the others have written in their answers to my question I should do vacuum full and vacuum analyze on a somewhat regular basis. Thanks for the hints! I will test them on Monday. Thanks to all and have a nice weekend, Marco
On 25 Apr 2003, marco wrote: > On Fri, 2003-04-25 at 17:05, Manuel Durán Aguete wrote: > > Hello, > > VACCUM it's your friend, look at the docs or search the list. > > > > Regards. > > > Well, you are right. I Vacuum this db once every day with a cronjob like > this: > > > crontab -l > > ... > > 0 0 * * 0 psql -U <user_name> <db_name> -c vacuum > > This should work... > > But even after running vacuum like that I have the same situation as before. It > takes more time to read or write the database than it would normally > take... > > But as all the others have written in their answers to my question I should do > vacuum full and vacuum analyze on a somewhat regular basis. Thanks for the hints! > I will test them on Monday. > > Thanks to all and have a nice weekend, You may be suffering from the heartbreak of index bloat. If your updates are such that they index key is always adding to one end, and removing from the other, then the index will grow very wide, with a lot of unpopulated leaves on the left. Try reindex index index_name on the indexes for the updated table and see if that helps.
On Fri, 2003-04-25 at 11:46, Shridhar Daithankar wrote: > Why don't you take a look at pgavd? It will periodically connect to database > and check if it needs vacuum. just that it won't issue vacuum full because it > locks table and IMO only administrator should do it. > > http://gborg.postgresql.org/project/pgavd/projdisplay.php Or, pg_autovacuum which is now included in 7.4 contrib. It should compile against 7.3.2 without any trouble. I would be curious to see how it goes (always looking for real world feedback). If you need any help let me know.
On Fri, 2003-04-25 at 17:13, Dennis Gearon wrote: > you need to do vacuum full on a regular basis, like at about the time that > the select/update times increase by 10-25%. You could put that on > chron job. Ok, 'vacuum full' has done the job. It took about an hour to finish, but in contrast to 'vacuum' access times finally dropped back to a normal level. I guess it'll take me some time to find out the optimal "vacuum rate", but at least it works now :) Thanks Marco
You might want to look at pgavd (http://gborg.postgresql.org/project/pgavd/projdisplay.php). I haven't played with it yet, so no idea how well it works. :) On Mon, Apr 28, 2003 at 04:23:17PM +0200, marco wrote: > I guess it'll take me some time to find out the optimal "vacuum rate", > but at least it works now :) -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
OK, I'm stupid, pgavd was mentioned earlier in the thread. Nevermind. :/ On Tue, Apr 29, 2003 at 03:57:23PM -0500, Jim C. Nasby wrote: > You might want to look at pgavd > (http://gborg.postgresql.org/project/pgavd/projdisplay.php). I haven't > played with it yet, so no idea how well it works. :) > > On Mon, Apr 28, 2003 at 04:23:17PM +0200, marco wrote: > > I guess it'll take me some time to find out the optimal "vacuum rate", > > but at least it works now :)
On Wednesday 30 April 2003 02:27, Jim C. Nasby wrote: > You might want to look at pgavd > (http://gborg.postgresql.org/project/pgavd/projdisplay.php). I haven't > played with it yet, so no idea how well it works. :) A C equivalent of same is present in postgresql CVS tip in contrib modules. Shridhar