[GENERAL] Tsearch2 and aspell
Regarding, http://archives.postgresql.org/pgsql-general/2003-10/msg00956.php Has there been any progress integrating aspell for tsearch2 over ispell? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] tsearch2: index in different table?
Is it possible to store an index in a different table? Instead of
keeping the index in a separate column, I have a separate table for
storing all indexes. I am trying to set up something like this:
UPDATE indexTable SET index1=to_tsvector('default',coalesce(Table1.Data,''));
This is currently not working, but something in this line. Is this
possible? Does tsearch2 only restrict to 1 table?
TIA
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Isolate Logs
Is it possible to isolate logging for each individual database? For example, lets say I have 3 databases: db1, db2, db3. And I want to log db activity (statements, login/logout, etc..) db1, db2, db3 like $PGDATA/db1, $PGDATA/db2, $PGDATA/db3, respectively. Currently everything is logged in $PGDATA/pg_log TIA ---(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
[GENERAL] tsearch2 best practices
Hi All, Planning to implement tsearch2 for my websitem and dbschema. I wanted to know if there is a "Best practices" guide I should be following. While reading about it, I noticed there were lot of 'gotchas' with this, such as back-up/restore, Slony 1 replication issues, etc.. What do most people recommend for backup/restore solution: 1) Install tsearch 2)Alter tables for tsearch2, update trigger, 3) do work .. 4) uninstall tsearch2, 5)backup? Is that the right approach? Or something else? Also, when will tsearch2 part of core, instead of contrib? TIA
[GENERAL] database slowdown
I am running Redhat 5.2 Linux with Postgresql 8.4.4; When my disk space is 90% free the database performance is very good. However, when it reaches close to 20% free the database performance is bad. I know its bad because I see a lot of 'D' next to 'postgresql' process when running top and I see a lot 'WAITING' in the 'ps' tree. I was wondering if there are any filesystem tweaks I can do to make postgresql run smoother. I am currently using ext3. Should I consider using a different file system for my database? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] database slowdown
Hi Andy, No, I don't shrink the database. I simply purge the whole thing and then let it populate again. The data isn't too critical. The disks I have are internal SAS disks. I get around 150MB/sec write and 250MB/sec read. Its a RAID1 . ps does show idle in transactions. I've never checked pg_locks. How would I do that ? On Sun, Jan 16, 2011 at 12:28 PM, Andy Colson wrote: > On 01/16/2011 10:44 AM, Mag Gam wrote: >> >> I am running Redhat 5.2 Linux with Postgresql 8.4.4; >> >> When my disk space is 90% free the database performance is very good. >> However, when it reaches close to 20% free the database performance is >> bad. I know its bad because I see a lot of 'D' next to 'postgresql' >> process when running top and I see a lot 'WAITING' in the 'ps' tree. I >> was wondering if there are any filesystem tweaks I can do to make >> postgresql run smoother. I am currently using ext3. Should I consider >> using a different file system for my database? >> > > I doubt its a disk space problem. Sounds like a cache/disk io problem. > When you are using 80% space, do you do something to get it back to 90% > free (ie shrink the db)? > > When the db is small, it fits in ram, and read's will be very quick. Once > your db gets bigger than cache it'll have to start using a lot more disk io. > > What kind of disk IO throughput do yo have? What kind of disk system is > this? (raid, scsi, etc) > Does ps ever show 'idle in transaction'? > Have you checked pg_locks to see what you are waiting on? > Have you watched vmstat while its at 80% full vs when its at 80% free? > (does the iowait go up?) > > > -Andy > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] database slowdown
Purge meaning, stop postgresql, rm -rf $PGDATA, recreate the environment, and start up postgresql again. On Tue, Jan 18, 2011 at 8:32 AM, Bill Moran wrote: > In response to Mag Gam : > >> Hi Andy, >> >> No, I don't shrink the database. I simply purge the whole thing and >> then let it populate again. The data isn't too critical. > > What does "purge" mean? Are you doing an SQL DELETE, or a TRUNCATE, > or dropping the DB and recreating? > > Each of these functions differently behind the scenes, in particular > DELETE will not actually free up the space used by the records you > deleted right away, and depending on many other factors, may be the > cause of your problems. > >> >> The disks I have are internal SAS disks. I get around 150MB/sec write >> and 250MB/sec read. Its a RAID1 . >> >> ps does show idle in transactions. I've never checked pg_locks. How >> would I do that ? >> >> >> >> On Sun, Jan 16, 2011 at 12:28 PM, Andy Colson wrote: >> > On 01/16/2011 10:44 AM, Mag Gam wrote: >> >> >> >> I am running Redhat 5.2 Linux with Postgresql 8.4.4; >> >> >> >> When my disk space is 90% free the database performance is very good. >> >> However, when it reaches close to 20% free the database performance is >> >> bad. I know its bad because I see a lot of 'D' next to 'postgresql' >> >> process when running top and I see a lot 'WAITING' in the 'ps' tree. I >> >> was wondering if there are any filesystem tweaks I can do to make >> >> postgresql run smoother. I am currently using ext3. Should I consider >> >> using a different file system for my database? >> >> >> > >> > I doubt its a disk space problem. Sounds like a cache/disk io problem. >> > When you are using 80% space, do you do something to get it back to 90% >> > free (ie shrink the db)? >> > >> > When the db is small, it fits in ram, and read's will be very quick. Once >> > your db gets bigger than cache it'll have to start using a lot more disk >> > io. >> > >> > What kind of disk IO throughput do yo have? What kind of disk system is >> > this? (raid, scsi, etc) >> > Does ps ever show 'idle in transaction'? >> > Have you checked pg_locks to see what you are waiting on? >> > Have you watched vmstat while its at 80% full vs when its at 80% free? >> > (does the iowait go up?) >> > >> > >> > -Andy >> > >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Column oriented pgsql
Is it possible to tweak (easily) Postgresql so the storage is column oriented versus row-oriented? We would like to increase read optimization on our data which is about 2TB. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Column oriented pgsql
Got it thanks! On Fri, May 8, 2009 at 2:57 PM, Christophe wrote: > > On May 8, 2009, at 11:25 AM, John R Pierce wrote: >> >> you read your tables by column, rather than by row?? >> SQL queries are inherently row oriented, the fundamental unit of storage >> is a 'tuple', which is a representation of a row of a table. > > I believe what is referring to is the disk storage organization, clustering > a single column from multiple rows together onto a page. For example, if > your typical use of a table is to read one particular column from a large > number of rows, this could (in theory) improve performance. > > AFAIK, PostgreSQL doesn't support this. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
