[GENERAL] dblink causing import errors
Our developers like the dblink modules, so I have installed it into the template1 database. They also like to import old database dumps after creating new databases with dbcreate. But then they get irritated by the error messages saying that the dblink functions already exist, because these functions are in the old dumps, but dbcreate already copied them from the template1 database. Also, some scripts which copy databases break or create unnecessary error messages because of that. Are there any best practices when using dblink to avoid these issues? -- Christoph -- 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] Shared memory usage in PostgreSQL 9.1
Am 04.12.2011 15:17, schrieb sfr...@snowman.net: Didn't see this get answered... The long-and-short of that there aren't any negative consequences of having it higher, as I understand it anyway, except the risk of greedy apps. In some cases, shared memory can't be swapped out, which makes it a bit more risky than 'regular' memory getting sucked up by some app. That's how I understand it as well. So the solution is to calculate an upper limit for the shared memory usage very generously, since it doesn't matter if the limit is set a couple of MBs too high. -- Christoph -- 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] Shared memory usage in PostgreSQL 9.1
Am 03.12.2011 18:02, schrieb Christoph Zwerschke: 400 MB = 419430400 Bytes but according to your log the used memory is: buffers = 424669472 Bytes This is a discrepancy of 1.25%. The difference could be explained by taking credit for the descriptors which may not be comprised in the shared_buffers setting, even if the shared_buffers value is set in memory units. But according to the docs, the descriptors should use 208/8192 = 2.5%. And on a 64bit machine, the descriptors should use even more memory, i.e. up to 5%. Just to clear up that last unexplained discrepancy, the problem is that I wrongly assumed the descriptors were the only overhead to the shared buffers. In reality it is more complex, e.g. additional memory for locks is reserved for each shared buffer. The 208 Bytes in the docs refer to the total overhead a shared buffer creates, while the value in Tomas' log contained only the overhead caused by the descriptors. Sorry for creating the noise and confusion. -- Christoph -- 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] Shared memory usage in PostgreSQL 9.1
Am 03.12.2011 20:31, schrieb Christoph Zwerschke: Then, the corrected sum is 449627320 Bytes, which is only about 2MB less than was requested. This remaining discrepancy can probably be explained by additional overhead for a PostgreSQL 9.1 64bit server vs. a PostgreSQL 8.3 32bit server for which the table was valid. And this additional overhead obviously is created per max_connections, not per shared_buffers. While the docs suggest there should be 19kB per connection, we measured about 45kB per connection. This explains the about 2MB difference when max_connections is 100. -- Christoph -- 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] Shared memory usage in PostgreSQL 9.1
Am 03.12.2011 13:39, schrieb Christoph Zwerschke: According to that table the usage would be: Connections: 1908000 Bytes Autovac workers: 57240 Bytes Prepared transactions: 0 Bytes Shared disk buffers: 400MB WAL buffers: 16MB Fixed space: 788480 Bytes Sum: 435145336 This is about 16MB less than what is really requested. Just so that this summation does not stay uncorrected: The major discrepancy accrued because my values for shared disk buffers and WAL buffers were wrong. They must be calculated as Shared disk buffers = (1 + 208/8192) * 400MB = 43008 Bytes WAL buffers = (1 + 8/8192) * 16MB = 16793600 Bytes Then, the corrected sum is 449627320 Bytes, which is only about 2MB less than was requested. This remaining discrepancy can probably be explained by additional overhead for a PostgreSQL 9.1 64bit server vs. a PostgreSQL 8.3 32bit server for which the table was valid. -- Christoph -- 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] Shared memory usage in PostgreSQL 9.1
Am 03.12.2011 18:39, schrieb Tom Lane: The long and the short of it is those numbers aren't meant to be exact. If they were, we'd have to complicate the table to distinguish 32 vs 64 bit and possibly other factors, and we'd have to remember to re-measure the values after any code change, neither of which seems worth the trouble. Please note that the table itself says that (a) the values are approximate, and (b) nobody has bothered to update the numbers since 8.3. Personally, I'm thrilled if you're seeing a discrepancy of only 1.25%. Understood. Btw, the 1.25% did not refer to the discrepancy between calculated and measured value, but to the memory overhead Tomas Vondra measured for the shared buffer pool, while I measured an overhead of about 2.5%, which should be also expected according to the docs. Another thing that's a bit confusing in Table 17.2 is that it is not immediately clear what size the shared disk buffers and wal buffers have when shared_buffers and wal_buffers are specified in memory units, not as integers as the table implies. The answer is, as I found out, in order to get the "real" values for shared_buffers and wal_buffers, the memory values must be divided by block_size resp. wal_block_size; the formula then stays the same. -- Christoph -- 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] Shared memory usage in PostgreSQL 9.1
Am 03.12.2011 18:02, schrieb Christoph Zwerschke: The difference could be explained by taking credit for the descriptors which may not be comprised in the shared_buffers setting, even if the shared_buffers value is set in memory units. Looked a bit more into this - the shared_buffers setting indeed only determines the memory for he actual shared buffer blocks, even if given in memory units. It does not include the descriptors and other freelist related stuff that is also needed to build the shared buffer pool. When I increased the shared_buffers by 1, the shared memory usage increased by 8372.4 Bytes, this is about 2.2% more than 1 blocks would use, close to the 2.5% which are documented. -- Christoph -- 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] Shared memory usage in PostgreSQL 9.1
Am 03.12.2011 15:34, schrieb Tomas Vondra: > Do you need to know an exact value or are you just interested why the > values in docs are not exact? Both. I'm writing an installation script that calculates the necessary IPC memory and increases the limit on the OS level (kernel.shmmax) if needed. I want to increase the limit only as much as really necessary, so I need to calculate the IPC usage as exactly as possible. (Btw, what negative consequences - if any - does it have if I set kernel.shmmax higher as necessary, like all available memory? Does this limit serve only as a protection against greedy applications?) > If you want to see what exactly needs how much memory, check the > src/backend/storage/ipc/ipci.c file in sources. I've added some log > messages, and this is the result on my 64bit machine (using the values > you've posted): Thanks a lot, that was helpful. So it seems the values in the docs are only correct for a 32 bit server. But I still don't understand this: In our example, we have set shared_buffers to: 400 MB = 419430400 Bytes but according to your log the used memory is: buffers = 424669472 Bytes This is a discrepancy of 1.25%. The difference could be explained by taking credit for the descriptors which may not be comprised in the shared_buffers setting, even if the shared_buffers value is set in memory units. But according to the docs, the descriptors should use 208/8192 = 2.5%. And on a 64bit machine, the descriptors should use even more memory, i.e. up to 5%. So I'm still a bit confused. -- Christoph -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Shared memory usage in PostgreSQL 9.1
For a PostgreSQL 9.1.1 instance, I have used the following postgresql.conf settings: max_connections = 100 shared_buffers = 400MB wal_buffers = 16MB All the other parameters have been left as default values. When I startup the instance, I get an error message saying that the shared memory does not suffice and 451837952 Bytes would be used. However, this is not what I expect when calculating the needs according to the documentation, Table 17-2 at http://www.postgresql.org/docs/current/static/kernel-resources.html According to that table the usage would be: Connections: 1908000 Bytes Autovac workers: 57240 Bytes Prepared transactions: 0 Bytes Shared disk buffers: 400MB WAL buffers: 16MB Fixed space: 788480 Bytes Sum: 435145336 This is about 16MB less than what is really requested. How can this substantial discrepancy be explained? For PostgreSQL 9.1, some important item must be missing in Table 17-2, or some values are wrong. -- Christoph -- 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] Finding rows with text columns beginning with other text columns
Am 10.05.2010 23:34 schrieb Alban Hertroys: > Thinking more on the issue, I don't see a way to prevent the nested > loop as there's no way to decide beforehand what part of the string to > index for b.txt. It depends on a.txt after all. Yes, that seems to be the gist of the matter. I just felt I might have missed something. Thanks for your answers. -- Christoph -- 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] Finding rows with text columns beginning with other text columns
Am 10.05.2010 11:50 schrieb Alban Hertroys: > On 10 May 2010, at 24:01, Christoph Zwerschke wrote: > >> select * from b join a on b.txt like a.txt||'%' >> >> I feel there should be a performat way to query these entries, >> but I can't come up with anything. Can anybody help me? > > Have you tried using substring instead of like? How exactly? I tried this: substr(b.txt, 1, length(a.txt)) = a.txt but it cannot be optimized and results in a nested loop, too. It only works with a fixed length: substr(b.txt, 1, 3) = a.txt So theoretically I could do something like select * from b join a on substr(b.txt, 1, 1) = a.txt and length(b.txt) = 1 union select * from b join a on substr(b.txt, 1, 2) = a.txt and length(b.txt) = 2 union select * from b join a on substr(b.txt, 1, 3) = a.txt and length(b.txt) = 3 union ... ... up to the maximum possible string length in a.txt. Not very elegant. If the question is not finding text cols in b starting with text cols in a, but text cols in b starting with text cols in a as their first word, then the following join condition works very well: split_part(b.txt, ' ', 1) = a.txt But I'm still looking for a simple solution to the original problem. -- Christoph -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Finding rows with text columns beginning with other text columns
Assume we have a table "a" with a text column "txt" and an index on that column. A query like the following will then be very perfomant since it can use the index: select * from a where txt like 'a%' (Assume also that the server is using the C locale or the index is set up with text_pattern_ops, so that this really works.) Now take a second, similar table "b" (can be the same table). We want to find all entries in b where txt begins with an existing txt entry in a: select * from b join a on b.txt like a.txt||'%' On the first glance you would expect that this is performant since it can use the index, but sadly it doesn't work. The problem seems to be that Postgres can not guarantee that column a.txt does not contain a '%', so it cannot optimize. I feel there should be a performat way to query these entries, but I can't come up with anything. Can anybody help me? Thanks, -- Christoph -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
