[ADMIN] max_fsm_pages question
I got this log on 8.3.9: Jan 24 02:13:28 db23.zmi.at postgres[29696]: [3-1] DB= U= H= WARNING: relation pg_toast.pg_toast_1910021 contains more than max_fsm_pages pages with useful free space Jan 24 02:13:28 db23.zmi.at postgres[29696]: [3-2] DB= U= H= HINT: Consider using VACUUM FULL on this relation or increasing the configuration parameter max_fsm_pages. Jan 24 02:13:28 db23.zmi.at postgres[29696]: [4-1] DB= U= H= LOG: automatic vacuum of table dbmail.pg_toast.pg_toast_1910021: index scans: 1 Jan 24 02:13:28 db23.zmi.at postgres[29696]: [4-2] pages: 0 removed, 740218 remain Jan 24 02:13:28 db23.zmi.at postgres[29696]: [4-3] tuples: 601310 removed, 2397087 remain Jan 24 02:13:28 db23.zmi.at postgres[29696]: [4-4] system usage: CPU 5.73s/1.92u sec elapsed 835.67 sec and I'd like to know 1) which db uses pg_toast.pg_toast_1910021? (Later I found it:) That should be dbmail, as it writes dbmail.pg_toast.pg_toast_1910021 later on. But wouldn't it be good to log that directly? Making it easier for admins... 2) what table is using that toast? 3) why did postgres suddenly decide to remove the old cruft suddenly? Autovacuum is on, the nightly backups do an extra vacuum analyze, and once a week a CLUSTER is done for the big tables. Maybe I missed one? -- mit freundlichen Grüssen, Michael Monnerie, Ing. BSc it-management Internet Services http://it-management.at Tel: 0660 / 415 65 31 // Wir haben zwei Häuser zu verkaufen: // http://zmi.at/langegg/ // http://willhaben.at/iad/realestate/object?adId=15923011 signature.asc Description: This is a digitally signed message part.
[ADMIN] how to speed ilike
Hello
I have a task to make postgres find user records no matter if they are
spelled correctly. In particular I have to find names with Lithuanian
letters even if the user searches using latin letters. For example
search criteria 'kestas' should find 'Kęstas'. I've made a function that
converts lithuanian letters to latin and use it like fnk_latin(username)
ILIKE fnk_latin('kestas'). It works OK.
Now the problem is performance. On test data base I have 2 records
of users, and it takes 3 seconds to get result. On production database
there could be a lot more. How would you advice to improve performance?
Maybe some special index would help? As user names are update rarely
it's the read speed I'm interested in.
--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] how to speed ilike
Julius Tuskenis wrote:
I've made a function that converts lithuanian letters to latin and
use it like fnk_latin(username) ILIKE fnk_latin('kestas').
Now the problem is performance.
Maybe some special index would help?
create index tblname_username_latin on tblname
((fnk_latin(username)));
You might want to have that function force all letters to lowercase.
It might also help to specify varchar_pattern_ops.
-Kevin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] max_fsm_pages question
Michael Monnerie wrote: why did postgres suddenly decide to remove the old cruft suddenly? Have you read through this yet?: http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM Autovacuum is on, the nightly backups do an extra vacuum analyze, and once a week a CLUSTER is done for the big tables. You should probably make that a vacuum analyze verbose to get a good idea of where you should set max_fsm_pages, and to look for where you are accumumlating free space to track. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] max_fsm_pages question
On Montag, 25. Januar 2010 Kevin Grittner wrote: Michael Monnerie wrote: why did postgres suddenly decide to remove the old cruft suddenly? Have you read through this yet?: http://www.postgresql.org/docs/8.3/interactive/runtime-config-resourc e.html#RUNTIME-CONFIG-RESOURCE-FSM Yes I did. Autovacuum is on, the nightly backups do an extra vacuum analyze, and once a week a CLUSTER is done for the big tables. You should probably make that a vacuum analyze verbose to get a good idea of where you should set max_fsm_pages, and to look for where you are accumumlating free space to track. That's why I find it strange. I always log the VACUUM VERBOSE ANALYZE command, it is run nightly: INFO: free space map contains 21517 pages in 107 relations DETAIL: A total of 22912 page slots are in use (including overhead). 22912 page slots are required to track all free space. Current limits are: 15 page slots, 1000 relations, using 984 kB. So, as there was that one relation that was bloatet - how could it be? Autovaccuum, nightly vacuum analyze, weekly cluster - and still a heavy bloated toast* something. I must do something wrong. -- mit freundlichen Grüssen, Michael Monnerie, Ing. BSc it-management Internet Services http://it-management.at Tel: 0660 / 415 65 31 // Wir haben zwei Häuser zu verkaufen: // http://zmi.at/langegg/ // http://willhaben.at/iad/realestate/object?adId=15923011 signature.asc Description: This is a digitally signed message part.
Re: [ADMIN] max_fsm_pages question
Michael Monnerie michael.monne...@is.it-management.at wrote: So, as there was that one relation that was bloatet - how could it be? Autovaccuum, nightly vacuum analyze, weekly cluster - and still a heavy bloated toast* something. I must do something wrong. Any chance you had or have long-running transactions. We once had very low free space in a big database which suddenly ballooned. It turned out an application programmer had left a connection in idle in transaction state for a few days. Another possibility is that you had an update or delete which affected a lot of rows. Even if it rolls back, it can cause bloat. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] how to speed ilike
Thank you for your answer Kevin. create index tblname_username_latin on tblname ((fnk_latin(username))); Tried this, but with no changes You might want to have that function force all letters to lowercase. Tried that too, but seem to me that ILIKE doesn't use the index. I'm using username ilike '%blablabla%' , so maybe theres no way for ilike to benefit from an index. It might also help to specify varchar_pattern_ops. I added varchar_pattern_ops to index declaration, but this didn't help either. Do you have any other ideas? -- Julius Tuskenis Programavimo skyriaus vadovas UAB nSoft mob. +37068233050 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] max_fsm_pages question
On Montag, 25. Januar 2010 Kevin Grittner wrote: Any chance you had or have long-running transactions. We once had very low free space in a big database which suddenly ballooned. It turned out an application programmer had left a connection in idle in transaction state for a few days. Another possibility is that you had an update or delete which affected a lot of rows. Even if it rolls back, it can cause bloat. I don't believe that. This machine holds the dbmail Mail Database, locks and transactions are very small, just to insert a new mail. The system is very low loaded, so such a bloat can't happen in a day. Finding out which table the toast belongs to would be great, maybe that helps further investigation. -- mit freundlichen Grüssen, Michael Monnerie, Ing. BSc it-management Internet Services http://it-management.at Tel: 0660 / 415 65 31 // Wir haben zwei Häuser zu verkaufen: // http://zmi.at/langegg/ // http://willhaben.at/iad/realestate/object?adId=15923011 signature.asc Description: This is a digitally signed message part.
Re: [ADMIN] how to speed ilike
On Mon, Jan 25, 2010 at 05:33:10PM +0200, Julius Tuskenis wrote: Thank you for your answer Kevin. create index tblname_username_latin on tblname ((fnk_latin(username))); Tried this, but with no changes You might want to have that function force all letters to lowercase. Tried that too, but seem to me that ILIKE doesn't use the index. I'm using username ilike '%blablabla%' , so maybe theres no way for ilike to benefit from an index. You cannot use an index for this search. It will work for 'blah%' otherwise you need to use full-text indexes a la tsearch. Cheers, Ken -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] max_fsm_pages question
Michael Monnerie michael.monne...@is.it-management.at wrote: such a bloat can't happen in a day. That is evidence that you may have a problem with some long-running transaction which stays open for days, possibly idle in transaction. Bloat will accumulate, without any vacuum being able to prevent it or recover from it, until the transaction terminates. It will not show up as free space in your vacuum verbose output while the transaction remains open; although I believe it will show up in the dead row versions cannot be removed yet count. Finding out which table the toast belongs to would be great, maybe that helps further investigation. Have a look at reltoastrelid: http://www.postgresql.org/docs/8.3/interactive/catalog-pg-class.html -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] max_fsm_pages question
On Montag, 25. Januar 2010 Kevin Grittner wrote: Have a look at reltoastrelid: Hmm.. select * from pg_class; doesnt give anything containing 1910021 that I had in the log. So what? A temporary table? But that shouldn't be bloated nor auto-vacuumed, right? -- mit freundlichen Grüssen, Michael Monnerie, Ing. BSc it-management Internet Services http://it-management.at Tel: 0660 / 415 65 31 // Wir haben zwei Häuser zu verkaufen: // http://zmi.at/langegg/ // http://willhaben.at/iad/realestate/object?adId=15923011 signature.asc Description: This is a digitally signed message part.
[ADMIN] relation between records in main and toast tables
Hello, Let's say TableA has toastable column, the contents of this column is stored in let's say pg_toast_1234. Is there a query to find which records (chunk_id, chunk_seq) in pg_toast_1234 store data for specific record in TableA (i.e. with PK column value eq. '567')? Igor Neyman
