Обсуждение: Tuning
I have some questions about tuning. The PostgreSQL documentation says that you don't need to worry about index maintenance and tuning with PostgreSQL. I'm used to systems that work a lot better if they are periodically re-orged or re-indexed. Is it true that one need not be concerned with this? I'm certain that the databases must require some human intervention. What kind of tuning or other intervention are you doing. What kind of tools are available and are being used. Carol Walter
On Mon, Apr 7, 2008 at 9:33 AM, Carol Walter <walterc@indiana.edu> wrote: > I have some questions about tuning. The PostgreSQL documentation says that > you don't need to worry about index maintenance and tuning with PostgreSQL. > I'm used to systems that work a lot better if they are periodically re-orged > or re-indexed. Is it true that one need not be concerned with this? I'm > certain that the databases must require some human intervention. What kind > of tuning or other intervention are you doing. What kind of tools are > available and are being used. Not sure where in the docs it exactly says to never worry about your indexes. While most access patterns can indeed be handled fine by autovacuum, you can use the reindex command in pgsql to optimize your indexes if they're chock full of empty space that can't be reclaimed by vacuum. You can monitor things like index and table bloat with the pg_stat_* series of views.
walterc@indiana.edu (Carol Walter) writes: > I have some questions about tuning. The PostgreSQL documentation > says that you don't need to worry about index maintenance and tuning > with PostgreSQL. I'm used to systems that work a lot better if they > are periodically re-orged or re-indexed. Is it true that one need > not be concerned with this? I'm certain that the databases must > require some human intervention. What kind of tuning or other > intervention are you doing. What kind of tools are available and are > being used. Well, there is one side to things where yes, indeed, "maintenance is quite necessary," and that being in the area of "vacuuming." <http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html> The need to 'reindex' or 'reorg' tables is not non-existent, however it is needed *way* less frequently than was the case in much older versions of PostgreSQL. e.g. - with v7.2, there were patterns of updates that would leave portions of indexes not usable, but the issue was rectified in ~7.4, and people have not been observing problems relating to this former scenario. Back when we had systems on v7.2, we had to shut down every few months and reindex some tables in order to keep performance OK. That's no longer the case with systems running on v8.1, and as we bring 8.3 into production, I expect even less need for manual interventions. If you are running VACUUM and ANALYZE often enough, and autovacuum pretty much does so, now, then there shouldn't be much need to do "re-orging" of the system. -- let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];; http://linuxfinances.info/info/nonrdbms.html In the name of the Lord-High mutant, we sacrifice this suburban girl -- `Future Schlock'
On Mon, Apr 07, 2008 at 12:45:38PM -0400, Chris Browne wrote: > versions of PostgreSQL. e.g. - with v7.2, there were patterns of > updates that would leave portions of indexes not usable, but the issue > was rectified in ~7.4, and people have not been observing problems > relating to this former scenario. There remain use patterns that will leave the indexes in pretty bad shape. This is an inherent limiation with btrees, though -- if you just unbalanced the tree with a large number of deletes, there's nothing you can do except REINDEX. A
On Montag, 7. April 2008 Scott Marlowe wrote: > You can monitor things like index and table bloat with the pg_stat_* > series of views. Are there scripts to automate this look at stats? Or could one draw graphs from that values, to visualize how well the db is? E.g., if you could calculate a % value, you could make RRD stats to see it's change over time. Is there any project on this? 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: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: www.keyserver.net Key-ID: 1C1209B4
Вложения
Michael Monnerie wrote: > On Montag, 7. April 2008 Scott Marlowe wrote: > >> You can monitor things like index and table bloat with the pg_stat_* >> series of views. >> > > Are there scripts to automate this look at stats? > Or could one draw graphs from that values, to visualize how well the db > is? E.g., if you could calculate a % value, you could make RRD stats to > see it's change over time. Is there any project on this? > > mfg zmi > I don't see a direct way to monitor bloat from pg_stat*. If I'm wrong, please set me straight. For example, monitoring index bloat would involve deciding how many pages an index would ideally consume, based on either sampling the table yourself or raiding stats, and making assumptions. Then you'd compare to actual (or approximated actual) and decide if you were within whatever threshold you think is OK. Paul
> Are there scripts to automate this look at stats? > Or could one draw graphs from that values, to visualize how well the db > is? E.g., if you could calculate a % value, you could make RRD stats to > see it's change over time. Is there any project on this? I don't know about rrd graphing it, but there are some great nagios plugins for this at http://bucardo.org/nagios You could have a look at the queries used in those and roll your own mrtg plugins. If you do, please share.
On Dienstag, 8. April 2008 paul rivers wrote: > I don't see a direct way to monitor bloat from pg_stat*. If I'm > wrong, please set me straight. > > For example, monitoring index bloat would involve deciding how many > pages an index would ideally consume, based on either sampling the > table yourself or raiding stats, and making assumptions. Then you'd > compare to actual (or approximated actual) and decide if you were > within whatever threshold you think is OK. If there's no rule of thumb, how could you manually decide on actions, other than by reading from a crystal ball? Also, if there are no tools, almost no admin can/will do anything. Except for the specialist having too much free time to dig into the db just for fun and reading tons of pg_stat* tables and values *g* As most won't have that funny time, tools which show possible problems or performance losses would greatly help. On Dienstag, 8. April 2008 Jeff Frost wrote: > I don't know about rrd graphing it, but there are some great nagios > plugins for this at http://bucardo.org/nagios > > You could have a look at the queries used in those and roll your own > mrtg plugins. If you do, please share. Thank you. I'll have a look at that nagios plugins. 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: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: www.keyserver.net Key-ID: 1C1209B4