Re: [HACKERS] First steps with 8.3 and autovacuum launcher
On 9/19/07, Decibel! [EMAIL PROTECTED] wrote: Odd... I'd expect it to actually be beneficial to run analyze on a table at roughly the same time as PK building, because you'd make better use of cache. Sure if your database fits entirely in RAM (otherwise if two big tables are analyzed while we create the primary key for a third one, it won't help us at all). And even in this case, it's not sure the time lost by waiting the lock is worth it. It could for sure if the restore could create the other primary keys while waiting for the lock on the analyzed tables, which is obviously not the case. In my particular case, the restore stales a lot of times with status ALTER TABLE waiting. -- Guillaume ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Dynamically adding index types (was GIT indexes)
On Tue, 2007-08-07 at 17:03 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: How hard will it be to add the infrastructure to allow new index types to be added to the server dynamically? INSERT INTO pg_am VALUES (...); I don't really think we need more than that, at least not till non-core index AMs are a whole lot thicker on the ground than they are today. We're able to dynamically add AMs in the way you suggest, but there is no way to alter the RMgrTable to either add a new RM or re-assign one of the unused RMs. So we can add a new AM, but it can't write WAL in a different way to existing RMs. We could either: 1. Remove the Const in front of RmgrTable in rmgr.c. That would allow re-assignment of the two existing unused RMs. 2. Create a new catalog table pg_rm and some brief machinery to populate the RmgrTable from pg_rm. That would allow dynamically adding RMs. Seems like we could do (1) for 8.3 easily enough. Thoughts, please? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Open issues for HOT patch
On Tue, 2007-09-18 at 12:10 -0400, Tom Lane wrote: I wrote: * The patch makes undocumented changes that cause autovacuum's decisions to be driven by total estimated dead space rather than total number of dead tuples. Do we like this? No one seems to have picked up on this point, but after reflection I think there's actually a pretty big problem here. Per-page pruning is perfectly capable of keeping dead space in check. In a system with HOT running well, the reasons to vacuum a table will be: 1. Remove dead index entries. 2. Remove LP_DEAD line pointers. 3. Truncate off no-longer-used end pages. 4. Transfer knowledge about free space into FSM. Pruning cannot accomplish #1, #2, or #3, and without significant changes in the FSM infrastructure it has no hope about #4 either. What I'm afraid of is that steady page-level pruning will keep the amount of dead space low, causing autovacuum never to fire, causing the indexes to bloat indefinitely because of #1 and the table itself to bloat indefinitely because of #2 and #4. Thus, the proposed change in autovacuum seems badly misguided: instead of making autovacuum trigger on things that only it can fix, it makes autovacuum trigger on something that per-page pruning can deal with perfectly well. I'm inclined to think that we should continue to drive autovac off a count of dead rows, as this is directly related to points #1 and #2, and doesn't seem any worse for #3 and #4 than an estimate based on space would be. Possibly it would be sensible for per-page pruning to report a reduction in number of dead rows when it removes heap-only tuples, but I'm not entirely sure --- any thoughts? Some behavioural comments only: I was part of the earlier discussion about when-to-VACUUM and don't have any fixed view of how to do this. If HOT is running well, then there will be less need for #1, #3 and #4, as I understand it. Deletes will still cause the need for #1, #3, #4 as well as dead-space removal. Many tables have only Inserts and Deletes, so we need to take that into account. On large tables, VACUUM hurts very badly, so I would like to see it run significantly less often. In your last post you mentioned multiple UPDATEs. Pruning multiple times for successive UPDATEs isn't going to release more space, so why do it? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Open issues for HOT patch
Decibel! wrote: On Tue, Sep 18, 2007 at 11:32:52AM -0400, Tom Lane wrote: Another option would be to prune whenever the free space goes below table fillfactor and hope that users would set fillfactor so that atleast one updated tuple can fit in the block. I know its not best to rely on the users though. But it can be good hint. If default fillfactor weren't 100% then this might be good ;-). But Erik Jones and I were just talking about FILLFACTOR... Is the plan to keep it at 100% with HOT? ISTM that's not such a great idea, since it forces at least the first update (if not many more) to be COLD. I think we should still keep it at 100%. Most tables are not updated, and a non-100% fillfactor will be waste of space when the extra space is not needed. Even a table that is updated should reach a steady state after a few cold updates. Those cold updates will make room on the pages for future updates, now that we can prune them and leave only dead line pointers behind. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Open issues for HOT patch
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: We could lift the limitation that you can't defragment a page that's pinned, if we play some smoke and mirrors in the buffer manager. When you prune a page, make a *copy* of the page you're pruning, and keep both versions in the buffer cache. Old pointers keep pointing to the old version. Any new calls to ReadBuffer will return the new copy, and the old copy can be dropped when its pin count drops to zero. No, that's way too wacky. How do you prevent people from making further changes to the old version? For instance, marking a tuple deleted? To make any changes to the old version, you need to lock the page with LockBuffer. LockBuffer needs to return a buffer with the latest version of the page, and the caller has to use that version for any changes. Changing all callers of LockBuffer (that lock heap pages) to do that is the biggest change involved, AFAICS. Hint bit updates to the old version we could just forget about. The actual practical application we have, I think, would only require being able to defrag a page that our own backend has pins on, which is something that might be more workable --- but it still seems awfully fragile. It could maybe be made to work in the simplest case of a plain UPDATE, because in practice I think the executor will never reference the old tuple's contents after heap_update() returns. But this falls down in more complex situations involving joins --- we might continue to try to join the same old tuple to other rows, and then any pass-by-reference Datums we are using are corrupt if the tuple got moved. Ugh, yeah that's too fragile. Another wacky idea: Within our own backend, we could keep track of which tuples we've accessed, and defrag could move all other tuples as long as the ones that we might still have pointers to are not touched. The bookkeeping wouldn't have to be exact, as long as it's conservative. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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: [HACKERS] pg_ctl -w vs unix_socket_directory
On 19/09/2007, Tom Lane [EMAIL PROTECTED] wrote: Radoslaw Zielinski [EMAIL PROTECTED] writes: pg_ctl -w -D ... start doesn't work when unix_socket_directory is set to somewhere else than the compiled in default (/tmp). pg_ctl not working is going to be the very least of your worries; pretty much nothing else will either. If you want some other socket directory, I strongly recommend setting [...] I don't want any other socket directory. All I want is a way to create a working startup script: able to start/stop the server regardless of changes in postgresql.conf and report the success or failure. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Open issues for HOT patch
Heikki Linnakangas [EMAIL PROTECTED] writes: There is one wacky idea I haven't dared to propose yet: We could lift the limitation that you can't defragment a page that's pinned, if we play some smoke and mirrors in the buffer manager. When you prune a page, make a *copy* of the page you're pruning, and keep both versions in the buffer cache. Old pointers keep pointing to the old version. Any new calls to ReadBuffer will return the new copy, and the old copy can be dropped when its pin count drops to zero. Fwiw when Heikki first mentioned this idea I thought it was the craziest thing I ever heard. But the more I thought about it the more I liked it. I've come to the conclusion that while it's a wart, it's not much worse than the wart of the super-exclusive lock which it replaces. In fact it's arguably cleaner in some ways. As a result vacuum would never have to wait for arbitrarily long pins and there wouldn't be the concept of a vacuum waiting for a vacuum lock with strange lock queueing semantics. It also means we could move tuples around on the page more freely. The only places which would have to deal with a possible new buffer would be precisely those places that lock the page. If you aren't locking the page then you definitely aren't about to fiddle with any bits that matter since your writes could be lost. Certainly you're not about to set xmin or xmax or anything like that. You might set hint bits which would be lost but probably not often since you would have already checked the visibility of the tuples with the page locked. There may be one or two places where we fiddle bits for a tuple we've just inserted ourselves thinking nobody else can see it yet, but the current philosophy seems to be leaning towards treating such coding practices as unacceptably fragile anyways. The buffer manager doesn't really need to track multiple versions of pages. It would just mark the old version as an orphaned buffer which is automatically a victim for the clock sweep as soon as the pin count drops to 0. It will never need to return such a buffer. What we would need is enough information to reread the buffer if someone tries to lock it and to unpin it when someone unpins a newer version. At first I thought the cost of copying the page would be a downside but in fact Heikki pointed out that in defragmentation we're already copying the page. In fact copying it to new memory instead of memory which is almost certainly likely in processor caches which would need to be invalidated would actually be faster and avoiding the use of memmove could be faster too. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Open issues for HOT patch
Decibel! [EMAIL PROTECTED] writes: 3 isn't that important to me, but 4 is: 4. Doesn't hammer the database to measure And pgstattuple fails #4 miserably. Want to know the average dead space in a 500GB database? Yeah, right So we could put a vacuum_cost_delay() in it ... regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Dynamically adding index types (was GIT indexes)
Simon Riggs [EMAIL PROTECTED] writes: We're able to dynamically add AMs in the way you suggest, but there is no way to alter the RMgrTable to either add a new RM or re-assign one of the unused RMs. Hmmm... 1. Remove the Const in front of RmgrTable in rmgr.c. That would allow re-assignment of the two existing unused RMs. Useless, as there's no way for an add-on AM to cause the value to be changed before recovery begins. 2. Create a new catalog table pg_rm and some brief machinery to populate the RmgrTable from pg_rm. That would allow dynamically adding RMs. Also useless --- what if pg_rm is damaged or missing? Even if it's there, how would recovery be able to tell which rows are valid? We certainly don't want it trying to do pg_clog probes. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] curious regression failures (was Re: [PATCHES] PL/TCL Patch to prevent postgres from becoming multithreaded)
Stefan Kaltenbrunner [EMAIL PROTECTED] writes: Tom Lane wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: ! ERROR: could not read block 2 of relation 1663/16384/2606: read only 0 of 8192 bytes Is that repeatable? What sort of filesystem are you testing on? (soft-mounted NFS by any chance?) doesn't seem to be repeatable :-( Hmm ... http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=luna_mothdt=2007-09-19%2013:10:01 Exact same error --- is it at the same place in the tests where you saw it? Now that I think about it, there have been similar transient failures (read only 0 of 8192 bytes) in the buildfarm before. It would be helpful to collect a list of exactly which build reports contain that string, but AFAIK there's no very easy way to do that; Andrew, any suggestions? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] like/ilike improvements
Andrew, All, On 5/22/07, Andrew Dunstan [EMAIL PROTECTED] wrote: But before I commit this I'd appreciate seeing some more testing, both for correctness and performance. I finally found some time to test this patch on our data. As our production database is still using 8.1, I made my tests with 8.1.10 and 8.3devel. As I had very weird results, I tested also 8.2.5. The patch seems to work as expected in my locale. I didn't notice problems during the tests I made except for the performance problem I describe below. The box is a recent dual core box using CentOS 5. It's a test box installed specifically to test PostgreSQL 8.3. Every version is compiled with the same compiler. Locale is fr_FR.UTF-8 and database is UTF-8 too. The table used to make the tests fits entirely in RAM. I tested a simple ILIKE query on our data with 8.3devel and it was far slower than with 8.1.10 (2 times slower). It was obviously not the expected result as it should have been faster considering your work. So I decided to test also with 8.2.5 and it seems a performance regression was introduced in 8.2 (and not in 8.3 which is in fact a bit faster than 8.2). I saw this item in 8.2 release notes: Allow ILIKE to work for multi-byte encodings (Tom) Internally, ILIKE now calls lower() and then uses LIKE. Locale-specific regular expression patterns still do not work in these encodings. Could it be responsible of such a slow down? I attached the results of my tests. If anyone needs more information, I'll be glad to provide them. Regards, -- Guillaume ** Environment ** cityvox=# select version(); version - PostgreSQL 8.1.10 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-52) (1 row) cityvox=# show lc_collate; lc_collate - fr_FR.UTF-8 (1 row) cityvox=# show lc_ctype; lc_ctype - fr_FR.UTF-8 (1 row) cityvox=# \l List of databases Name| Owner | Encoding ---+--+-- cityvox | postgres | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 (4 rows) cityvox=# show shared_buffers; shared_buffers 16384 (1 row) cityvox=# show work_mem; work_mem -- 32768 (1 row) ** Seqscan on the table ** cityvox=# select count(*) from evenement; count 128780 (1 row) Time: 57.335 ms cityvox=# select count(*) from evenement; count 128780 (1 row) Time: 57.317 ms ** Query with LIKE ** cityvox=# SELECT e.numeve FROM evenement e WHERE e.libgeseve LIKE '%hocus pocus%' OR e.mots_cle LIKE '%hocus pocus%'; numeve (0 rows) Time: 188.312 ms cityvox=# SELECT e.numeve FROM evenement e WHERE e.libgeseve LIKE '%hocus pocus%' OR e.mots_cle LIKE '%hocus pocus%'; numeve (0 rows) Time: 188.235 ms ** Query with ILIKE ** cityvox=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%' OR e.mots_cle ILIKE '%hocus pocus%'; numeve --- 900024298 87578 161108 (3 rows) Time: 227.048 ms cityvox=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%' OR e.mots_cle ILIKE '%hocus pocus%'; numeve --- 900024298 87578 161108 (3 rows) Time: 226.586 ms cityvox=# EXPLAIN ANALYZE SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%' OR e.mots_cle ILIKE '%hocus pocus%'; QUERY PLAN --- Seq Scan on evenement e (cost=0.00..6743.01 rows=1 width=4) (actual time=45.907..226.702 rows=3 loops=1) Filter: (((libgeseve)::text ~~* '%hocus pocus%'::text) OR ((mots_cle)::text ~~* '%hocus pocus%'::text)) Total runtime: 226.736 ms (3 rows) Time: 227.216 ms ** Query with only one condition with ILIKE ** cityvox=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%'; numeve --- 900024298 87578 (2 rows) Time: 177.318 ms ** Environment ** cityvox=# select version(); version PostgreSQL 8.2.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-52) (1 row) cityvox=# show lc_collate; lc_collate - fr_FR.UTF-8 (1 row) cityvox=# show lc_ctype; lc_ctype - fr_FR.UTF-8 (1 row) cityvox=# \l List of databases Name| Owner | Encoding ---+--+-- cityvox | postgres | UTF8 postgres | postgres | UTF8 template0 | postgres |
[HACKERS] Re: curious regression failures (was Re: [PATCHES] PL/TCL Patch to prevent postgres from becoming multithreaded)
Tom Lane wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: Tom Lane wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: ! ERROR: could not read block 2 of relation 1663/16384/2606: read only 0 of 8192 bytes Is that repeatable? What sort of filesystem are you testing on? (soft-mounted NFS by any chance?) doesn't seem to be repeatable :-( Hmm ... http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=luna_mothdt=2007-09-19%2013:10:01 Exact same error --- is it at the same place in the tests where you saw it? looks like it is in a similiar place: http://www.kaltenbrunner.cc/files/regression.diffs (I don't have more than this on that failure any more) Stefan ---(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: [HACKERS] Open issues for HOT patch
On Sep 19, 2007, at 8:08 AM, Tom Lane wrote: Decibel! [EMAIL PROTECTED] writes: 3 isn't that important to me, but 4 is: 4. Doesn't hammer the database to measure And pgstattuple fails #4 miserably. Want to know the average dead space in a 500GB database? Yeah, right So we could put a vacuum_cost_delay() in it ... pg_stat_delay()? ;) That's better than what we have now, without a doubt. But I'd still prefer to have a table I can just read on the fly. We do have a TODO to replace the current stats infrastructure with something that has less overhead, right? :) -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
On Sep 19, 2007, at 2:08 AM, Guillaume Smet wrote: On 9/19/07, Decibel! [EMAIL PROTECTED] wrote: Odd... I'd expect it to actually be beneficial to run analyze on a table at roughly the same time as PK building, because you'd make better use of cache. Sure if your database fits entirely in RAM (otherwise if two big tables are analyzed while we create the primary key for a third one, You missed my point... what we'd want to happen is for the analyze to take place while that table had a good chance of still being in memory. it won't help us at all). And even in this case, it's not sure the time lost by waiting the lock is worth it. It could for sure if the restore could create the other primary keys while waiting for the lock on the analyzed tables, which is obviously not the case. In my particular case, the restore stales a lot of times with status ALTER TABLE waiting. It might be worth looking into creating a different lock for ALTERs that actually change database page layout vs ALTERs that don't, since there's no reason you couldn't run ANALYZE while adding a PK (for example). -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Re: curious regression failures (was Re: [PATCHES] PL/TCL Patch to prevent postgres from becoming multithreaded)
Andrew Dunstan wrote: Tom Lane wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: Tom Lane wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: ! ERROR: could not read block 2 of relation 1663/16384/2606: read only 0 of 8192 bytes Is that repeatable? What sort of filesystem are you testing on? (soft-mounted NFS by any chance?) doesn't seem to be repeatable :-( Hmm ... http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=luna_mothdt=2007-09-19%2013:10:01 Exact same error --- is it at the same place in the tests where you saw it? Now that I think about it, there have been similar transient failures (read only 0 of 8192 bytes) in the buildfarm before. It would be helpful to collect a list of exactly which build reports contain that string, but AFAIK there's no very easy way to do that; Andrew, any suggestions? pgbfprod=# select sysname, stage, snapshot from build_status where log ~ $$read only \d+ of \d+ bytes$$; sysname |stage | snapshot ---+--+- zebra | InstallCheck | 2007-09-11 10:25:03 wildebeest | InstallCheck | 2007-09-11 22:00:11 baiji | InstallCheck | 2007-09-12 22:39:24 luna_moth | InstallCheck | 2007-09-19 13:10:01 hmm all of those seem to fail the foreign key checks in a very similiar way and that are vastly different platforms (windows,solaris,openbsd and linux). Stefan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Re: curious regression failures (was Re: [PATCHES] PL/TCL Patch to prevent postgres from becoming multithreaded)
Tom Lane wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: Tom Lane wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: ! ERROR: could not read block 2 of relation 1663/16384/2606: read only 0 of 8192 bytes Is that repeatable? What sort of filesystem are you testing on? (soft-mounted NFS by any chance?) doesn't seem to be repeatable :-( Hmm ... http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=luna_mothdt=2007-09-19%2013:10:01 Exact same error --- is it at the same place in the tests where you saw it? Now that I think about it, there have been similar transient failures (read only 0 of 8192 bytes) in the buildfarm before. It would be helpful to collect a list of exactly which build reports contain that string, but AFAIK there's no very easy way to do that; Andrew, any suggestions? pgbfprod=# select sysname, stage, snapshot from build_status where log ~ $$read only \d+ of \d+ bytes$$; sysname |stage | snapshot ---+--+- zebra | InstallCheck | 2007-09-11 10:25:03 wildebeest | InstallCheck | 2007-09-11 22:00:11 baiji | InstallCheck | 2007-09-12 22:39:24 luna_moth | InstallCheck | 2007-09-19 13:10:01 (4 rows) cheers andrew ---(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: [HACKERS] Timezones change - never ending story
Zdenek Kotala [EMAIL PROTECTED] writes: Just for information. Venezuela is going to have new timezone change (30minutes shift) on this weekend. This change is not yet integrated in the last version in Olson database. (Original announcement said it happens on 1.1.2008) Is there still time to slip this into the upcoming 8.2.5? I haven't been able to find anything which specifies precisely when it'll happen though. Just knowing the week or even day isn't enough. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] curious regression failures (was Re: [PATCHES] PL/TCL Patch to prevent postgres from becoming multithreaded)
Andrew Dunstan [EMAIL PROTECTED] writes: pgbfprod=# select sysname, stage, snapshot from build_status where log ~ $$read only \d+ of \d+ bytes$$; sysname |stage | snapshot ---+--+- zebra | InstallCheck | 2007-09-11 10:25:03 wildebeest | InstallCheck | 2007-09-11 22:00:11 baiji | InstallCheck | 2007-09-12 22:39:24 luna_moth | InstallCheck | 2007-09-19 13:10:01 (4 rows) Fascinating. So I would venture that (1) it's definitely our bug, not something we could blame on NFS or whatever, and (2) we introduced it fairly recently. That specific error message wording exists only in HEAD, but it's been there since 2007-01-03, so if there were a pre-existing problem you'd think there would be some more matches. The patterns I notice here are (1) they're all InstallCheck not Check failures; (2) though not all at the same place in the tests, it's a fairly short range; (3) it's all references to system catalogs, though not all the same one. My gut feeling is that we're seeing autovacuum truncate off an empty end block and then a backend tries to reference that block again. But there should be enough interlocks in place to prevent such references. Any ideas out there? regards, tom lane ---(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: [HACKERS] curious regression failures
Stefan Kaltenbrunner [EMAIL PROTECTED] writes: Andrew Dunstan wrote: pgbfprod=# select sysname, stage, snapshot from build_status where log ~ $$read only \d+ of \d+ bytes$$; sysname |stage | snapshot ---+--+- zebra | InstallCheck | 2007-09-11 10:25:03 wildebeest | InstallCheck | 2007-09-11 22:00:11 baiji | InstallCheck | 2007-09-12 22:39:24 luna_moth | InstallCheck | 2007-09-19 13:10:01 hmm all of those seem to fail the foreign key checks in a very similiar way and that are vastly different platforms (windows,solaris,openbsd and linux). Is this exhaustive? That is, are we sure this never happened before Sept 11th? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] curious regression failures
Gregory Stark wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: Andrew Dunstan wrote: pgbfprod=# select sysname, stage, snapshot from build_status where log ~ $$read only \d+ of \d+ bytes$$; sysname |stage | snapshot ---+--+- zebra | InstallCheck | 2007-09-11 10:25:03 wildebeest | InstallCheck | 2007-09-11 22:00:11 baiji | InstallCheck | 2007-09-12 22:39:24 luna_moth | InstallCheck | 2007-09-19 13:10:01 hmm all of those seem to fail the foreign key checks in a very similiar way and that are vastly different platforms (windows,solaris,openbsd and linux). Is this exhaustive? That is, are we sure this never happened before Sept 11th? Yes, we have never thrown away any buildfarm history, and we have build logs going back several years now. Being able to run queries like this makes it all worth while :-) (Thanks Joshua for the disk space - I know it annoys you.) cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timezones change - never ending story
Gregory Stark wrote: Is there still time to slip this into the upcoming 8.2.5? 8.2.5 is no longer upcoming. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] like/ilike improvements
Guillaume Smet wrote: Andrew, All, On 5/22/07, Andrew Dunstan [EMAIL PROTECTED] wrote: But before I commit this I'd appreciate seeing some more testing, both for correctness and performance. I finally found some time to test this patch on our data. As our production database is still using 8.1, I made my tests with 8.1.10 and 8.3devel. As I had very weird results, I tested also 8.2.5. The patch seems to work as expected in my locale. I didn't notice problems during the tests I made except for the performance problem I describe below. The box is a recent dual core box using CentOS 5. It's a test box installed specifically to test PostgreSQL 8.3. Every version is compiled with the same compiler. Locale is fr_FR.UTF-8 and database is UTF-8 too. The table used to make the tests fits entirely in RAM. I tested a simple ILIKE query on our data with 8.3devel and it was far slower than with 8.1.10 (2 times slower). It was obviously not the expected result as it should have been faster considering your work. So I decided to test also with 8.2.5 and it seems a performance regression was introduced in 8.2 (and not in 8.3 which is in fact a bit faster than 8.2). I saw this item in 8.2 release notes: Allow ILIKE to work for multi-byte encodings (Tom) Internally, ILIKE now calls lower() and then uses LIKE. Locale-specific regular expression patterns still do not work in these encodings. Could it be responsible of such a slow down? I attached the results of my tests. If anyone needs more information, I'll be glad to provide them. Ugh. It's at least good to see that the LIKE case has some useful speedup in 8.3. Can you run the same set of tests in a single byte encoding like latin1? We might have to look at doing on-demand lowering, but in a case like yours it looks like we'd still end up lowering almost every character anyway, so I'm not quite sure what to do. Note that the 8.2 change was a bug fix, so we can't just revert it. Maybe we need to look closely at the efficiency of lower(). cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] Debugger
Hello, I am with a problem. When I am using debugger, breakpoints that they are inside of main function (stop the execution in the marked point) but when breakpoint is marked in another archive (index.c for example) breakpoint does not function (the execution in the point does not stop), because this happens? Thanks, Pedro Belmino.
Re: [HACKERS] pg_ctl -w vs unix_socket_directory
On Tue, 2007-09-18 at 19:13 -0400, Tom Lane wrote: Radoslaw Zielinski [EMAIL PROTECTED] writes: pg_ctl -w -D ... start doesn't work when unix_socket_directory is set to somewhere else than the compiled in default (/tmp). pg_ctl not working is going to be the very least of your worries; pretty much nothing else will either. If you mean client applications won't work, that would be expected from such a change to the server configuration. If you want some other socket directory, I strongly recommend setting the path to it at compile time so that it's properly wired into libpq. AFAICS the only value in specifying unix_socket_directory at server start is if you actually *want* a stealth server that won't be found by clients without manual intervention. Those arguments apply almost as well to the server port. The server port is read from the postgresql.conf from pg_ctl, but not the socket directory. It's an annoyance: if you change the default socket directory, you're probably going to break your init script (on FreeBSD you will, because it uses -w). I don't think that's the expected result, and it's not intuitive to find the cause of the problem. I think the inconsistency between server port number and socket directory is less than ideal. However, I also don't feel very strongly about it. It's rare, and a there are plenty of workarounds. Regards, Jeff Davis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] curious regression failures
Looking back, by far the largest change in the period Sep 1 - Sep 11 was the lazy xid calculation and read-only transactions. That seems like the most likely culprit. But given Tom's comments this commit stands out too: ---BeginMessage--- Log Message: --- Release the exclusive lock on the table early after truncating it in lazy vacuum, instead of waiting till commit. Modified Files: -- pgsql/src/backend/commands: vacuumlazy.c (r1.92 - r1.93) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/vacuumlazy.c?r1=1.92r2=1.93) ---(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 ---End Message--- -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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: [HACKERS] Debugger
Pedro Belmino wrote: I am with a problem. When I am using debugger, breakpoints that they are inside of main function (stop the execution in the marked point) but when breakpoint is marked in another archive (index.c for example) breakpoint does not function (the execution in the point does not stop), because this happens? There is a fork() happen between the main function and what you are probably looking for break at. You need to attach to the session process after it forks, not to the main postmaster process. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] like/ilike improvements
On 9/19/07, Andrew Dunstan [EMAIL PROTECTED] wrote: It's at least good to see that the LIKE case has some useful speedup in 8.3. It can be due to your patch or to the varlena header patch. Seqscan is a bit faster too. Can you run the same set of tests in a single byte encoding like latin1? As discussed on IRC, I'm loading the data in a LATIN1 database for 8.1, 8.2 and 8.3. I'll let you know when I have the results. We might have to look at doing on-demand lowering, but in a case like yours it looks like we'd still end up lowering almost every character anyway, so I'm not quite sure what to do. Note that the 8.2 change was a bug fix, so we can't just revert it. Maybe we need to look closely at the efficiency of lower(). Yes, I know it's a bug fix but the performance decrease is far from being negligible in our case. -- Guillaume ---(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: [HACKERS] curious regression failures
Gregory Stark [EMAIL PROTECTED] writes: But given Tom's comments this commit stands out too: From: Alvaro Herrera [EMAIL PROTECTED] Log Message: --- Release the exclusive lock on the table early after truncating it in lazy vacuum, instead of waiting till commit. I had thought about that one and not seen a problem with it --- but sometimes when the light goes on, it's just blinding :-(. This change is undoubtedly what's breaking it. The failures in question are coming from commands that try to insert new entries into various system tables. Now normally, the first place a backend will try to insert a brand-new tuple in a table is the rd_targblock block that is remembered in relcache as being where we last successfully inserted. The failures must be happening because autovacuum has just truncated away where rd_targblock points. There is a mechanism to reset everyone's rd_targblock after a truncation: it's done by broadcasting a shared-invalidation relcache inval message for that relation. Which happens at commit, before releasing locks, which is the correct time for the typical application of this mechanism, namely to make sure people see system-catalog updates on time. Releasing the exclusive lock early allows backends to try to access the relation again before they've heard about the truncation. There might be another way to manage this, but we're not inventing a new invalidation mechanism for 8.3. This patch will have to be reverted for the time being :-( regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] like/ilike improvements
On 9/19/07, Andrew Dunstan [EMAIL PROTECTED] wrote: Can you run the same set of tests in a single byte encoding like latin1? Here are the results (each query was executed several times before this result): ** 8.1 ** cityvox_latin1=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%'; numeve --- 900024298 87578 (2 rows) Time: 135.877 ms ** 8.2 ** cityvox_latin1=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%'; numeve --- 900024298 87578 (2 rows) Time: 111.595 ms ** 8.3 ** cityvox_latin1=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%'; numeve --- 900024298 87578 (2 rows) Time: 160.582 ms Results are quite surprising but there's no error, I checked them several times... If someone can point me to how I can profile query execution, I can provide more information. -- Guillaume ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] like/ilike improvements
Guillaume Smet wrote: On 9/19/07, Andrew Dunstan [EMAIL PROTECTED] wrote: Can you run the same set of tests in a single byte encoding like latin1? Here are the results (each query was executed several times before this result): ** 8.1 ** cityvox_latin1=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%'; numeve --- 900024298 87578 (2 rows) Time: 135.877 ms ** 8.2 ** cityvox_latin1=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%'; numeve --- 900024298 87578 (2 rows) Time: 111.595 ms ** 8.3 ** cityvox_latin1=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%'; numeve --- 900024298 87578 (2 rows) Time: 160.582 ms Results are quite surprising but there's no error, I checked them several times... No, what this suggests to me is that it might have been a mistake to make the single byte case work like the multi-byte case, by pre-lowering the string, as we did back in May. It confirms my suspicion that the lower() code is the culprit. It should really be lightning fast. Can you retry both sets of tests but this time in C locale? The lower() code works differently in C locale, and it might be that we need to look at tweaking just one case. cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
