Re: [ADMIN] WAL configuration and REINDEX
I apologise if I am uninformed, but it is my understanding that REINDEX is still required even with the most regular of vacuums? On 9/22/06, Jim Nasby <[EMAIL PROTECTED]> wrote: On Sep 21, 2006, at 6:40 AM, Jose Manuel Garci a Valladolid wrote:> I have a PostgreSQL 8.1.4 server under Linux Red Hat with several > databases. The server is configured with WAL archiving turned on.> As a maintenance process, every night one cron job launches a> backup process to the server with> pg_dump, then the server shuts down and starts up and do a REINDEX > DATABASE to all databases to keep all tables reindexed.>> After 30 days of no activity to the server (this is a DBA test> server) the amount of WAL segments is increasing at very high> speed. Every REINDEX process generates between 25 and 30 WAL > segments. With this behavior (and no activity!!) I can not maintain> this amount of data to performs future WAL backups and recovers.>> Any idea to decrease the number of WAL segments generated? > Is possible to know when a table would be reindexed and to avoid> dayly REINDEX process?I think you're running off some pretty old information.First, you should only enable WAL archiving if you intend to use PITR. pg_dump and PITR are completely un-related. In fact, if you'venever taken a base filesystem backup, all those old WAL files arecompletely useless.Second, as long as you are vacuuming frequently enough, there's no reason you should ever have to REINDEX. You certainly shouldn't bedoing it on a regular basis.--Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)--Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)---(end of broadcast)---TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] WAL configuration and REINDEX
adey <[EMAIL PROTECTED]> writes: > I apologise if I am uninformed, but it is my understanding that REINDEX is > still required even with the most regular of vacuums? REINDEX shouldn't be required at all under normal circumstances. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] WAL configuration and REINDEX
Thanks Tom. How does Postgres handle page splits and redirections in indexes then please? I had an unsuccessful look at :- http://www.postgresql.org/docs/8.1/interactive/index-functions.html and http://www.postgresql.org/docs/8.1/interactive/indexes-types.html On 9/25/06, Tom Lane <[EMAIL PROTECTED]> wrote: adey <[EMAIL PROTECTED]> writes:> I apologise if I am uninformed, but it is my understanding that REINDEX is > still required even with the most regular of vacuums?REINDEX shouldn't be required at all under normal circumstances. regards, tom lane
Re: [ADMIN] best OS and HW for postgreSQL
On Sep 22, 2006, at 6:12 PM, Raul Retamozo wrote: Hi everyone on the list. I want to know what is the reccommended OS to work with PostgreSQL , on specific with PostGIS: One more question is about what HW (server) offers the best performance for a Web Map Server bases on PostGIS and mapserver. In general, you're probably best off running whatever OS you're most comfortable with. As for hardware, until recently, AMD was the un-disputed king when it came to running PostgreSQL (and databases in general). But the newer Intel CPUs seem to have surpassed the Opteron. I believe there's a tweakers.net article floating around that did some performance testing with the new CPUs. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] best OS and HW for postgreSQL
Hi. On 9/25/06, Jim Nasby <[EMAIL PROTECTED]> wrote: As for hardware, until recently, AMD was the un-disputed king when it came to running PostgreSQL (and databases in general). But the newer Intel CPUs seem to have surpassed the Opteron. I believe there's a tweakers.net article floating around that did some performance testing with the new CPUs. http://tweakers.net/reviews/646/13 cug -- PostgreSQL Bootcamp, Big Nerd Ranch Europe, Nov 2006 http://www.bignerdranch.com/news/2006-08-21.shtml ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
