Re: [HACKERS] Spinlock performance improvement proposal
> But note that Myron did a number of things that are (IMHO) orthogonal yes, I did :) > to process-to-thread conversion, such as adding prepared statements, > a separate thread/process/whateveryoucallit for buffer writing, ditto > for vacuuming, etc. I think his results cannot be taken as indicative > of the benefits of threads per se --- these other things could be > implemented in a pure process model too, and we have no data with which > to estimate which change bought how much. > If you are comparing just process vs. thread, I really don't think I gained much for performance and ended up with some pretty unmanageable code. The one thing that led to most of the gains was scheduling all the writes to one thread which, as noted by Tom, you could do on the process model. Besides, Most of the advantage in doing this was taken away with the addition of WAL in 7.1. The other real gain that I saw with threading was limiting the number of open files but that led me to alter much of the file manager in order to synchronize access to the files which probably slowed things a bit. To be honest, I don't think I, personally, would try this again. I went pretty far off the beaten path with this thing. It works well for what I am doing ( a limited number of SQL statements run many times over ) but there probably was a better way. I'm thinking now that I should have tried to add a CORBA interface for connections. I would have been able to accomplish my original goals without creating a deadend for myself. Thanks all for a great project, Myron [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] multibyte performance
I did some benchmarking with/without multibyte support using current. (1) regression test With multibyte support: 9.52user 3.38system 0:59.27elapsed 21%CPU (0avgtext+0avgdata 0maxresident)k Without multibyte support: 8.97user 4.84system 1:00.85elapsed 22%CPU (0avgtext+0avgdata 0maxresident)k (2) pgbench With multibyte support(first column is the concurrent user, second is the TPS): 1 46.004932 2 70.848123 4 88.147471 8 90.472970 16 96.620166 32 95.947363 64 92.718780 128 61.725883 Witout multibyte support: 1 52.668169 2 68.132654 4 79.956663 8 81.133516 16 96.618124 32 92.283645 64 86.936559 128 87.584099 for your convenience, a graph is attached(bench.png). (3) testing environment Linux kernel 2.2.17 PIII 750MHz, 256MB RAM, IDE disk configure option: configure --enable-multibyte=EUC_JP or configure postgresql.conf settings(other than default): max_connections = 128 shared_buffers = 1024 wal_sync_method = open_sync deadlock_timeout = 10 pgbench options: -s 2 (initialization) -t 10 (benchmarking) -- Tatsuo Ishii bench.png ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Spinlock performance improvement proposal
"D. Hageman" <[EMAIL PROTECTED]> writes: > If you look at Myron Scott's post today you will see that it had other > advantages going for it (like auto-vacuum!) and disadvantages ... rogue > thread corruption (already debated today). But note that Myron did a number of things that are (IMHO) orthogonal to process-to-thread conversion, such as adding prepared statements, a separate thread/process/whateveryoucallit for buffer writing, ditto for vacuuming, etc. I think his results cannot be taken as indicative of the benefits of threads per se --- these other things could be implemented in a pure process model too, and we have no data with which to estimate which change bought how much. Threading certainly should reduce the context switch time, but this comes at the price of increased overhead within each context (since access to thread-local variables is not free). It's by no means obvious that there's a net win there. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Spinlock performance improvement proposal
On Wed, 26 Sep 2001, D. Hageman wrote: > Oh, man ... am I reading stuff into what you are writing or are you > reading stuff into what I am writing? Maybe a little bit of both? My > original contention is that I think that the best way to get the full > potential out of SMP machines is to use a threads model. I didn't say the > present way wasn't fast. Or alternatively, that the current inter-process locking is a bit inefficient. Its possible to have inter-process locks that are as fast as inter-thread locks. > > Actually, if I remember, there was someone who ported postgresql (I think > > it was 6.5) to be multithreaded with major pain, because the requirement > > was to integrate with CORBA. I believe that person posted some benchmarks > > which were essentially identical to non-threaded postgres... > > Actually, it was 7.0.2 and the performance gain was interesting. The > posting can be found at: > > 7.0.2About10:52 average completion > multi-threaded2:42 average completion > 7.1beta3 1:13 average completion > > If the multi-threaded version was 7.0.2 and threads increased performance > that much - I would have to say that was a bonus. However, the > performance increases that the PostgreSQL team implemented later ... > pushed the regular version ahead again. That kinda says to me that > potential is there. Alternatively, you could read that 7.1 took the wind out of threaded sails. :) But I guess we won't know until the current version is ported to threads... -alex ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Spinlock performance improvement proposal
On Wed, 26 Sep 2001, Alex Pilosov wrote: > On Wed, 26 Sep 2001, D. Hageman wrote: > > > When you need data that is specific to a thread you use a TSD (Thread > > Specific Data). > Which Linux does not support with a vengeance, to my knowledge. I am not sure what that means. If it works it works. > As a matter of fact, quote from Linus on the matter was something like > "Solution to slow process switching is fast process switching, not another > kernel abstraction [referring to threads and TSD]". TSDs make > implementation of thread switching complex, and fork() complex. Linus does have some interesting ideas. I always like to hear his perspective on matters, but just like the government - I don't always agree with him. I don't see why TSDs would make the implementation of thread switching complex - seems to me that would be something that is implemented in the userland side part of the pthreads implemenation and not the kernel side. I don't really like to talk specifics, but both the lightweight process and the system call fork() are implemented using the __clone kernel function with the parameters slightly different (This is in the Linux kernel, btw since you wanted to use that as an example). The speed improvements the kernel has given the fork() command (like copy on write) only lasts until the process writes to memmory. The next time it comes around - it is for all intents and purposes a full context switch again. With threads ... the cost is relatively consistant. > The question about threads boils down to: Is there far more data that is > shared than unshared? If yes, threads are better, if not, you'll be > abusing TSD and slowing things down. I think the question about threads boils down to if the core members of the PostgreSQL team want to try it or not. At this time, I would have to say they pretty much agree they like things the way they are now, which is completely fine. They are the ones that spend most of the time on it and want to support it. > I believe right now, postgresql' model of sharing only things that need to > be shared is pretty damn good. The only slight problem is overhead of > forking another backend, but its still _fast_. Oh, man ... am I reading stuff into what you are writing or are you reading stuff into what I am writing? Maybe a little bit of both? My original contention is that I think that the best way to get the full potential out of SMP machines is to use a threads model. I didn't say the present way wasn't fast. > Actually, if I remember, there was someone who ported postgresql (I think > it was 6.5) to be multithreaded with major pain, because the requirement > was to integrate with CORBA. I believe that person posted some benchmarks > which were essentially identical to non-threaded postgres... Actually, it was 7.0.2 and the performance gain was interesting. The posting can be found at: http://candle.pha.pa.us/mhonarc/todo.detail/thread/msg7.html The results are: 20 clients, 900 inserts per client, 1 insert per transaction, 4 different tables. 7.0.2About10:52 average completion multi-threaded2:42 average completion 7.1beta3 1:13 average completion If the multi-threaded version was 7.0.2 and threads increased performance that much - I would have to say that was a bonus. However, the performance increases that the PostgreSQL team implemented later ... pushed the regular version ahead again. That kinda says to me that potential is there. If you look at Myron Scott's post today you will see that it had other advantages going for it (like auto-vacuum!) and disadvantages ... rogue thread corruption (already debated today). -- //\\ || D. Hageman<[EMAIL PROTECTED]> || \\// ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Spinlock performance improvement proposal
On Wed, 26 Sep 2001, D. Hageman wrote: > > > Save for the fact that the kernel can switch between threads faster then > > > it can switch processes considering threads share the same address space, > > > stack, code, etc. If need be sharing the data between threads is much > > > easier then sharing between processes. > > > > When using a kernel threading model, it's not obvious to me that the > > kernel will switch between threads much faster than it will switch > > between processes. As far as I can see, the only potential savings is > > not reloading the pointers to the page tables. That is not nothing, > > but it is also > > > I can't comment on the "isolate data" line. I am still trying to figure > > > that one out. > > > > Sometimes you need data which is specific to a particular thread. > > When you need data that is specific to a thread you use a TSD (Thread > Specific Data). Which Linux does not support with a vengeance, to my knowledge. As a matter of fact, quote from Linus on the matter was something like "Solution to slow process switching is fast process switching, not another kernel abstraction [referring to threads and TSD]". TSDs make implementation of thread switching complex, and fork() complex. The question about threads boils down to: Is there far more data that is shared than unshared? If yes, threads are better, if not, you'll be abusing TSD and slowing things down. I believe right now, postgresql' model of sharing only things that need to be shared is pretty damn good. The only slight problem is overhead of forking another backend, but its still _fast_. IMHO, threads would not bring large improvement to postgresql. Actually, if I remember, there was someone who ported postgresql (I think it was 6.5) to be multithreaded with major pain, because the requirement was to integrate with CORBA. I believe that person posted some benchmarks which were essentially identical to non-threaded postgres... -alex ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Spinlock performance improvement proposal
> ... Thomas still has his date/time stuff > to finish off, now that CVSup is fixed ... I'm now getting clean runs through the regression tests on a freshly merged cvs tree. I'd like to look at it a little more to adjust pg_proc.h attributes before I commit the changes. There was a bit of a hiccup when merging since there was some bytea stuff added to the catalogs over the last couple of weeks. Could folks hold off on claiming new OIDs until I get this stuff committed? TIA I expect to be able to merge this stuff by Friday at the latest, more likely tomorrow. - Thomas ---(end of broadcast)--- TIP 3: 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] [BUGS] PostgreSQL / PHP Overrun Error
There is a problem in PHP-4.0.6. Please use PHP4.0.7 or 4.0.8 and the problem will be solved. This can be obtained from CVS -- Mike - Original Message - From: "Christopher Kings-Lynne" <[EMAIL PROTECTED]> To: "Mike Rogers" <[EMAIL PROTECTED]>; "mlw" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, September 26, 2001 10:31 PM Subject: RE: [HACKERS] [BUGS] PostgreSQL / PHP Overrun Error > Have you recompiled PHP to link against the new postgres libraries? > > Chris > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED]]On Behalf Of Mike Rogers > > Sent: Thursday, 27 September 2001 1:07 AM > > To: mlw > > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; > > [EMAIL PROTECTED] > > Subject: Re: [HACKERS] [BUGS] PostgreSQL / PHP Overrun Error > > > > > > Sorry: > > PHP 4.0.6 (with memory leak patch [download listed right below > > php-4.0.6.tar.gz download- It was a problem]) > > PostgreSQL 7.1.3 > > Apache 1.3.20 (with mod_ssl- but it does the same thing > > without mod_ssl) > > -- > > Mike > > > > - Original Message - > > From: "mlw" <[EMAIL PROTECTED]> > > To: "Mike Rogers" <[EMAIL PROTECTED]> > > Cc: "Tom Lane" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; > > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Wednesday, September 26, 2001 1:55 PM > > Subject: Re: [BUGS] PostgreSQL / PHP Overrun Error > > > > > > > Mike Rogers wrote: > > > > > > > Well it really isn't your code (true), but the only thing that is > > changed is > > > > the 7.0-7.1- Was a data length changed on the return or > > something that > > > > could affect this? > > > > > > What version of PHP are you using? > > > > > > > > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [BUGS] PostgreSQL / PHP Overrun Error
Have you recompiled PHP to link against the new postgres libraries? Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Mike Rogers > Sent: Thursday, 27 September 2001 1:07 AM > To: mlw > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; > [EMAIL PROTECTED] > Subject: Re: [HACKERS] [BUGS] PostgreSQL / PHP Overrun Error > > > Sorry: > PHP 4.0.6 (with memory leak patch [download listed right below > php-4.0.6.tar.gz download- It was a problem]) > PostgreSQL 7.1.3 > Apache 1.3.20 (with mod_ssl- but it does the same thing > without mod_ssl) > -- > Mike > > - Original Message - > From: "mlw" <[EMAIL PROTECTED]> > To: "Mike Rogers" <[EMAIL PROTECTED]> > Cc: "Tom Lane" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Wednesday, September 26, 2001 1:55 PM > Subject: Re: [BUGS] PostgreSQL / PHP Overrun Error > > > > Mike Rogers wrote: > > > > > Well it really isn't your code (true), but the only thing that is > changed is > > > the 7.0-7.1- Was a data length changed on the return or > something that > > > could affect this? > > > > What version of PHP are you using? > > > > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] casting for dates
On Wed, 26 Sep 2001, Mitch Vincent wrote: > Will > > SELECT now() - 'nummonths months'::interval ; > > work? Unfortunately no. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Spinlock performance improvement proposal
Ian Lance Taylor <[EMAIL PROTECTED]> writes: > (Actually, though, Postgres is already vulnerable to erratic behaviour > because any backend process can corrupt the shared buffer pool.) Not to mention the other parts of shared memory. Nonetheless, our experience has been that cross-backend failures due to memory clobbers in shared memory are very infrequent --- certainly far less often than we see localized-to-a-backend crashes. Probably this is because the shared memory is (a) small compared to the rest of the address space and (b) only accessed by certain specific modules within Postgres. I'm convinced that switching to a thread model would result in a significant degradation in our ability to recover from coredump-type failures, even given the (implausible) assumption that we introduce no new bugs during the conversion. I'm also *un*convinced that such a conversion will yield significant performance benefits, unless we introduce additional cross-thread dependencies (and more fragility and lock contention) by tactics such as sharing catalog caches across threads. regards, tom lane ---(end of broadcast)--- TIP 3: 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_dump bug
=?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= <[EMAIL PROTECTED]> writes: > PostgreSQL version: 7.1.3 > The dump puts first the permissions and after that the view creation, Are you certain you are using the 7.1.3 version of pg_dump, and not something older? This was fixed in 7.1.3 according to the CVS logs... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Spinlock performance improvement proposal
On 26 Sep 2001, Doug McNaught wrote: > This depends on your system. Solaris has a huge difference between > thread and process context switch times, whereas Linux has very little > difference (and in fact a Linux process context switch is about as > fast as a Solaris thread switch on the same hardware--Solaris is just > a pig when it comes to process context switching). Yeah, I kinda commented on this in another e-mail. Linux has some nice tweaks for software using the forking model, but I am sure a couple of Solaris admins out there like to run PostgreSQL. ;-) You are right in that it is very system dependent. I should have prefaced it with "In general ..." > > I can't comment on the "isolate data" line. I am still trying to figure > > that one out. > > I think his point is one of clarity and maintainability. When a > task's data is explicitly shared (via shared memory of some sort) it's > fairly clear when you're accessing shared data and need to worry about > locking. Whereas when all data is shared by default (as with threads) > it's very easy to miss places where threads can step on each other. Well, I understand what you are saying and you are correct. The situation is that when you implement anything using pthreads you lock your variables (which is where the major performance penalty comes into play with threads). Now, the kicker is how you lock them. Depending on how you do it (as per discussion earlier on this list concerning threads) it can be faster or slower. It all depends on what model you use. Data is not explicitely shared between threads unless you make it so. The threads just share the same stack and all of that, but you can't (shouldn't is probably a better word) really access anything you don't have an address for. Threads just makes it easier to share if you want to. Also, see my other e-mail to the list concerning TSDs. -- //\\ || D. Hageman<[EMAIL PROTECTED]> || \\// ---(end of broadcast)--- TIP 3: 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] Spinlock performance improvement proposal
On 26 Sep 2001, Ian Lance Taylor wrote: > > > Save for the fact that the kernel can switch between threads faster then > > it can switch processes considering threads share the same address space, > > stack, code, etc. If need be sharing the data between threads is much > > easier then sharing between processes. > > When using a kernel threading model, it's not obvious to me that the > kernel will switch between threads much faster than it will switch > between processes. As far as I can see, the only potential savings is > not reloading the pointers to the page tables. That is not nothing, > but it is also not a lot. It is my understanding that avoiding a full context switch of the processor can be of a significant advantage. This is especially important on processor architectures that can be kinda slow at doing it (x86). I will admit that most modern kernels have features that assist software packages utilizing the forking model (copy on write for instance). It is also my impression that these do a good job. I am the kind of guy that looks towards the future (as in a year, year and half or so) and say that processors will hopefully get faster at context switching and more and more kernels will implement these algorithms to speed up the forking model. At the same time, I see more and more processors being shoved into a single box and it appears that the threads model works better on these type of systems. > > I can't comment on the "isolate data" line. I am still trying to figure > > that one out. > > Sometimes you need data which is specific to a particular thread. When you need data that is specific to a thread you use a TSD (Thread Specific Data). > Basically, you have to look at every global variable in the Postgres > backend, and determine whether to share it among all threads or to > make it thread-specific. Yes, if one was to implement threads into PostgreSQL I would think that some re-writing would be in order of several areas. Like I said before, give a person a chance to restructure things so future TODO items wouldn't be so hard to implement. Personally, I like to stay away from global variables as much as possible. They just get you into trouble. > > That last line is a troll if I every saw it ;-) I will agree that threads > > isn't for everything and that it has costs just like everything else. Let > > me stress that last part - like everything else. Certain costs exist in > > the present model, nothing is - how should we say ... perfect. > > When writing in C, threading inevitably loses robustness. Erratic > behaviour by one thread, perhaps in a user defined function, can > subtly corrupt the entire system, rather than just that thread. Part > of defensive programming is building barriers between different parts > of a system. Process boundaries are a powerful barrier. I agree with everything you wrote above except for the first line. My only comment is that process boundaries are only *truely* a powerful barrier if the processes are different pieces of code and are not dependent on each other in crippling ways. Forking the same code with the bug in it - and only 1 in 5 die - is still 4 copies of buggy code running on your system ;-) > (Actually, though, Postgres is already vulnerable to erratic behaviour > because any backend process can corrupt the shared buffer pool.) I appreciate your total honest view of the situation. -- //\\ || D. Hageman<[EMAIL PROTECTED]> || \\// ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Spinlock performance improvement proposal
"D. Hageman" <[EMAIL PROTECTED]> writes: > Save for the fact that the kernel can switch between threads faster then > it can switch processes considering threads share the same address space, > stack, code, etc. If need be sharing the data between threads is much > easier then sharing between processes. This depends on your system. Solaris has a huge difference between thread and process context switch times, whereas Linux has very little difference (and in fact a Linux process context switch is about as fast as a Solaris thread switch on the same hardware--Solaris is just a pig when it comes to process context switching). > I can't comment on the "isolate data" line. I am still trying to figure > that one out. I think his point is one of clarity and maintainability. When a task's data is explicitly shared (via shared memory of some sort) it's fairly clear when you're accessing shared data and need to worry about locking. Whereas when all data is shared by default (as with threads) it's very easy to miss places where threads can step on each other. -Doug -- In a world of steel-eyed death, and men who are fighting to be warm, Come in, she said, I'll give you shelter from the storm.-Dylan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] pg_dump bug
Short! :-) PostgreSQL version: 7.1.3 I do I dump of a database which has some views, rules, and different permissions on each view. The dump puts first the permissions and after that the view creation, so when I import the dump back to the server (or another server) I get lts of errors, and have to change the permission by hand. Has this already been reported? saludos... :-) -- Porqué usar una base de datos relacional cualquiera, si podés usar PostgreSQL? - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telematica Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Spinlock performance improvement proposal
On Wed, 26 Sep 2001, mlw wrote: > I can only think of two objectives for threading. (1) running the various > connections in their own thread instead of their own process. (2) running > complex queries across multiple threads. > I did a multi-threaded version of 7.0.2 using Solaris threads about a year ago in order to try and get multiple backend connections working under one java process using jni. I used the thread per connection model. I eventually got it working, but it was/is very messy ( there were global variables everywhere! ). Anyway, I was able to get a pretty good speed up on inserts by scheduling buffer writes from multiple connections on one common writing thread. I also got some other features that were important to me at the time. 1. True prepared statements under java with bound input and output variables 2. Better system utilization a. fewer Solaris lightweight processes mapped to threads. b. Fewer open files per postgres installation 3. Automatic vacuums when system activity is low by a daemon thread. but there were some drawbacks... One rogue thread or bad user function could take down all connections for that process. This was and seems to still be the major drawback to using threads. Myron Scott [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Spinlock performance improvement proposal
"D. Hageman" <[EMAIL PROTECTED]> writes: > > you have a newer kernel scheduled implementation, then you will have the same > > scheduling as separate processes. The only thing you will need to do is > > switch your brain from figuring out how to share data, to trying to figure > > out how to isolate data. A multithreaded implementation lacks many of the > > benefits and robustness of a multiprocess implementation. > > Save for the fact that the kernel can switch between threads faster then > it can switch processes considering threads share the same address space, > stack, code, etc. If need be sharing the data between threads is much > easier then sharing between processes. When using a kernel threading model, it's not obvious to me that the kernel will switch between threads much faster than it will switch between processes. As far as I can see, the only potential savings is not reloading the pointers to the page tables. That is not nothing, but it is also not a lot. > I can't comment on the "isolate data" line. I am still trying to figure > that one out. Sometimes you need data which is specific to a particular thread. Basically, you have to look at every global variable in the Postgres backend, and determine whether to share it among all threads or to make it thread-specific. In other words, you have to take extra steps to isolate the data within the thread. This is the reverse of the current situation, in which you have to take extra steps to share data among all backend processes. > That last line is a troll if I every saw it ;-) I will agree that threads > isn't for everything and that it has costs just like everything else. Let > me stress that last part - like everything else. Certain costs exist in > the present model, nothing is - how should we say ... perfect. When writing in C, threading inevitably loses robustness. Erratic behaviour by one thread, perhaps in a user defined function, can subtly corrupt the entire system, rather than just that thread. Part of defensive programming is building barriers between different parts of a system. Process boundaries are a powerful barrier. (Actually, though, Postgres is already vulnerable to erratic behaviour because any backend process can corrupt the shared buffer pool.) Ian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] casting for dates
On 27 Sep 2001, Andrew McMillan wrote: > On Thu, 2001-09-27 at 08:30, Vince Vielhaber wrote: > > > > I'm trying to use an integer from a table to add/subtract time in months. > > IOW: > > > > create table foo(nummonths int); > > > > select now() - nummonths months; > > newsroom=# select now() - interval( text(3) || ' months'); > ?column? > > 2001-06-27 08:56:27+12 > (1 row) > > > Crude, but hey: it works :-) It certainly does! Thanks! Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 3: 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] Spinlock performance improvement proposal
On Wed, 26 Sep 2001, mlw wrote: > > I can only think of two objectives for threading. (1) running the various > connections in their own thread instead of their own process. (2) running > complex queries across multiple threads. > > For item (1) I see no value to this. It is a lot of work with no tangible > benefit. If you have an old fashion pthreads implementation, it will hurt > performance because are scheduled within the single process's time slice.. Old fashion ... as in a userland library that implements POSIX threads? Well, I would agree. However, most *modern* implementations are done in the kernel or kernel and userland coop model and don't have this limitation (as you mention later in your e-mail). You have kinda hit on one of my gripes about computers in general. At what point in time does one say something is obsolete or too old to support anymore - that it hinders progress instead of adding a "feature"? > you have a newer kernel scheduled implementation, then you will have the same > scheduling as separate processes. The only thing you will need to do is > switch your brain from figuring out how to share data, to trying to figure > out how to isolate data. A multithreaded implementation lacks many of the > benefits and robustness of a multiprocess implementation. Save for the fact that the kernel can switch between threads faster then it can switch processes considering threads share the same address space, stack, code, etc. If need be sharing the data between threads is much easier then sharing between processes. I can't comment on the "isolate data" line. I am still trying to figure that one out. That last line is a troll if I every saw it ;-) I will agree that threads isn't for everything and that it has costs just like everything else. Let me stress that last part - like everything else. Certain costs exist in the present model, nothing is - how should we say ... perfect. > For item (2) I can see how that could speed up queries in a low utilization > system, and that would be cool, but in a server that is under load, threading > the queries probably be less efficient. Well, I don't follow your logic and you didn't give any substance to back up your claim. I am willing to listen. Another thought ... Oracle uses threads doesn't it or at least it has a single processor and multi-processor version last time I knew ... which do they claim is better? (Not saying that Oracle's proclimation of what is good and what is not matters, but it is good for another view point). -- //\\ || D. Hageman<[EMAIL PROTECTED]> || \\// ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Spinlock performance improvement proposal
Neil Padgett <[EMAIL PROTECTED]> writes: > Well. Currently the runs are the typical pg_bench runs. With what parameters? If you don't initialize the pg_bench database with "scale" proportional to the number of clients you intend to use, then you'll naturally get huge lock contention. For example, if you use scale=1, there's only one "branch" in the database. Since every transaction wants to update the branch's balance, every transaction has to write-lock that single row, and so everybody serializes on that one lock. Under these conditions it's not surprising to see lots of lock waits and lots of useless runs of the deadlock detector ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] casting for dates
Haven't tried yet, but perhaps casting nummonths to an interval datatype would do the trick. -r At 04:30 PM 9/26/01 -0400, Vince Vielhaber wrote: >I'm trying to use an integer from a table to add/subtract time in months. >IOW: > >create table foo(nummonths int); > >select now() - nummonths months; > >So far nothing I've tried will work - short of a function. Is there a >way to do this? > >Vince. >-- >== >Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net > 56K Nationwide Dialup from $16.00/mo at Pop4 Networking > Online Campground Directoryhttp://www.camping-usa.com >Online Giftshop Superstorehttp://www.cloudninegifts.com >== > > > > >---(end of broadcast)--- >TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > >--- >Incoming mail is certified Virus Free. >Checked by AVG anti-virus system (http://www.grisoft.com). >Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Spinlock performance improvement proposal
"D. Hageman" wrote: > The plan for the new spinlocks does look like it has some potential. My > only comment in regards to permformance when we start looking at SMP > machines is ... it is my belief that getting a true threaded backend may > be the only way to get the full potential out of SMP machines. I see that > is one of the things to experiment with on the TODO list and I have seen > some people have messed around already with this using Solaris threads. > It should probably be attempted with pthreads if PostgreSQL is going to > keep some resemblance of cross-platform compatibility. At that time, it > would probably be easier to go in and clean up some stuff for the > implementation of other TODO items (put in the base framework for more > complex future items) as threading the backend would take a little bit of > ideology shift. I can only think of two objectives for threading. (1) running the various connections in their own thread instead of their own process. (2) running complex queries across multiple threads. For item (1) I see no value to this. It is a lot of work with no tangible benefit. If you have an old fashion pthreads implementation, it will hurt performance because are scheduled within the single process's time slice.. If you have a newer kernel scheduled implementation, then you will have the same scheduling as separate processes. The only thing you will need to do is switch your brain from figuring out how to share data, to trying to figure out how to isolate data. A multithreaded implementation lacks many of the benefits and robustness of a multiprocess implementation. For item (2) I can see how that could speed up queries in a low utilization system, and that would be cool, but in a server that is under load, threading the queries probably be less efficient. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] casting for dates
Will SELECT now() - 'nummonths months'::interval ; work? - Original Message - From: "Vince Vielhaber" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, September 26, 2001 4:30 PM Subject: [HACKERS] casting for dates > > I'm trying to use an integer from a table to add/subtract time in months. > IOW: > > create table foo(nummonths int); > > select now() - nummonths months; > > So far nothing I've tried will work - short of a function. Is there a > way to do this? > > Vince. > -- > == > Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net > 56K Nationwide Dialup from $16.00/mo at Pop4 Networking > Online Campground Directoryhttp://www.camping-usa.com >Online Giftshop Superstorehttp://www.cloudninegifts.com > == > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Spinlock performance improvement proposal
Tom Lane wrote: > > Neil Padgett <[EMAIL PROTECTED]> writes: > > Initial results (top five -- if you would like a complete profile, let > > me know): > > Each sample counts as 1 samples. > > % cumulative self self total > > time samples samplescalls T1/call T1/call name > > 26.57 42255.02 42255.02 FindLockCycleRecurse > > Yipes. It would be interesting to know more about the locking pattern > of your benchmark --- are there long waits-for chains, or not? The > present deadlock detector was certainly written with an eye to "get it > right" rather than "make it fast", but I wonder whether this shows a > performance problem in the detector, or just too many executions because > you're waiting too long to get locks. > > > However, this seems to be a red herring. Removing the deadlock detector > > had no effect. In fact, benchmarking showed removing it yielded no > > improvement in transaction processing rate on uniprocessor or SMP > > systems. Instead, it seems that the deadlock detector simply amounts to > > "something to do" for the blocked backend while it waits for lock > > acquisition. > > Do you have any idea about the typical lock-acquisition delay in this > benchmark? Our docs advise trying to set DEADLOCK_TIMEOUT higher than > the typical acquisition delay, so that the deadlock detector does not > run unnecessarily. Well. Currently the runs are the typical pg_bench runs. This was useful since it was a handy benchmark that was already done, and I was hoping it might be useful for comparison since it seems to be popular. More benchmarks of different types would of course be useful though. I think the large time consumed by the deadlock detector in the profile is simply due to too many executions while waiting to acquire to contended locks. But, I agree that it seems DEADLOCK_TIMEOUT was set too low, since it appears from the profile output that the deadlock detector was running unnecessarily. But the deadlock detector isn't causing the SMP performance hit right now, since the throughput is the same with it in place or with it removed completely. I therefore didn't make any attempt to tune DEADLOCK_TIMEOUT. As I mentioned before, it apparently just gives the backend "something" to do while it waits for a lock. I'm thinking that the deadlock detector unnecessarily has no effect on performance since the shared memory is causing some level of serialization. So, one CPU (or two, or three, but not all) is doing useful work, while the others are idle (that is to say, doing no useful work). If they are idle spinning, or idle running the deadlock detector the net throughput is still the same. (This might also indicate that improving the lock design won't help here.) Of course, another possibility is that you spend so long spinning simply because you do spin (rather than sleep), and this is wasting much CPU time so the useful work backends take longer to get things done. Either is just speculation right now without any data to back things up. > > > For example, there has been some suggestion > > that perhaps some component of the database is causing large lock > > contention. > > My thought as well. I would certainly recommend that you use more than > one test case while looking at these things. Yes. That is another suggestion for a next step. Several cases might serve to better expose the path causing the slowdown. I think that several test cases of varying usage patterns, coupled with hold time instrumentation (which can tell what routine acquired the lock and how long it held it, and yield wait-for data in the analysis), are the right way to go about attacking SMP performance. Any other thoughts? Neil -- Neil Padgett Red Hat Canada Ltd. E-Mail: [EMAIL PROTECTED] 2323 Yonge Street, Suite #300, Toronto, ON M4P 2C9 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] casting for dates
On Thu, 2001-09-27 at 08:30, Vince Vielhaber wrote: > > I'm trying to use an integer from a table to add/subtract time in months. > IOW: > > create table foo(nummonths int); > > select now() - nummonths months; newsroom=# select now() - interval( text(3) || ' months'); ?column? 2001-06-27 08:56:27+12 (1 row) Crude, but hey: it works :-) Cheers, SAndrew. -- Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7217MOB: +64(21)635-694OFFICE: +64(4)499-2267 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] casting for dates
I'm trying to use an integer from a table to add/subtract time in months. IOW: create table foo(nummonths int); select now() - nummonths months; So far nothing I've tried will work - short of a function. Is there a way to do this? Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Spinlock performance improvement proposal
On 26 Sep 2001, Doug McNaught wrote: > "D. Hageman" <[EMAIL PROTECTED]> writes: > > > The plan for the new spinlocks does look like it has some potential. My > > only comment in regards to permformance when we start looking at SMP > > machines is ... it is my belief that getting a true threaded backend may > > be the only way to get the full potential out of SMP machines. > > Depends on what you mean. For scaling well with many connections and > simultaneous queries, there's no reason IMHO that the current > process-per-backend model won't do, assuming the locking issues are > addressed. Well, I know the current process-per-backend model does quite well. My argument is not that it fails to do as intended. My original argument is that it is belief (at the momment with the knowledge I have) to get the full potential out of SMP machines - threads might be the way to go. The data from RedHat is quite interesting, so my feelings on this might change or could be re-inforced. I watch anxiously ;-) > If you're talking about making a single query use multiple CPUs, then > yes, we're probably talking about a fundamental rewrite to use threads > or some other mechanism. Well, we have several thread model ideologies that we could chose from. Only experimentation would let us determine the proper path to follow and then it wouldn't be ideal for everyone. You kinda just have to take the best scenerio and run with it. My first inclination would be something like a thread per connection (to reduce connection overhead), but then we could run into limits on different platforms (threads per process). I kinda like the idea of using a thread for replication purposes ... lots of interesting possibilities exist and I will be first to admit that I don't have all the answers. -- //\\ || D. Hageman<[EMAIL PROTECTED]> || \\// ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] LOCAL_CREDS -> SCM_CREDS in src/backend/libpq/auth.c:535
> For OpenBSD to work, we need a change from LOCAL_CREDS to SCM_CREDS.
> Bruce, I think you are familure with this one. Care to make the change?
> (I have no idea where to make it!).
OK, I have applied the following patch that fixes the problem on
OpenBSD. In my reading of the OpenBSD kernel, it has 'struct sockcred'
but has no code in the kernel to deal with SCM_CREDS or LOCAL_CREDS.
The patch tests for both HAVE_STRUCT_SOCKCRED and LOCAL_CREDS before it
will try local socket credential authentication. This means we have
local creds on Linux, NetBSD, FreeBSD, and BSD/OS. I will document this
in pg_hba.conf.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Index: src/backend/libpq/auth.c
===
RCS file: /cvsroot/pgsql/src/backend/libpq/auth.c,v
retrieving revision 1.67
diff -c -r1.67 auth.c
*** src/backend/libpq/auth.c2001/09/21 20:31:45 1.67
--- src/backend/libpq/auth.c2001/09/26 19:30:30
***
*** 520,526
break;
case uaIdent:
! #if !defined(SO_PEERCRED) && (defined(HAVE_STRUCT_CMSGCRED) ||
defined(HAVE_STRUCT_FCRED) || defined(HAVE_STRUCT_SOCKCRED))
/*
* If we are doing ident on unix-domain sockets,
* use SCM_CREDS only if it is defined and SO_PEERCRED
isn't.
--- 520,526
break;
case uaIdent:
! #if !defined(SO_PEERCRED) && (defined(HAVE_STRUCT_CMSGCRED) ||
defined(HAVE_STRUCT_FCRED) || (defined(HAVE_STRUCT_SOCKCRED) && defined(LOCAL_CREDS)))
/*
* If we are doing ident on unix-domain sockets,
* use SCM_CREDS only if it is defined and SO_PEERCRED
isn't.
Index: src/backend/libpq/hba.c
===
RCS file: /cvsroot/pgsql/src/backend/libpq/hba.c,v
retrieving revision 1.72
diff -c -r1.72 hba.c
*** src/backend/libpq/hba.c 2001/09/21 20:31:46 1.72
--- src/backend/libpq/hba.c 2001/09/26 19:30:30
***
*** 904,910
return true;
! #elif defined(HAVE_STRUCT_CMSGCRED) || defined(HAVE_STRUCT_FCRED) ||
defined(HAVE_STRUCT_SOCKCRED)
struct msghdr msg;
/* Credentials structure */
--- 904,910
return true;
! #elif defined(HAVE_STRUCT_CMSGCRED) || defined(HAVE_STRUCT_FCRED) ||
(defined(HAVE_STRUCT_SOCKCRED) && defined(LOCAL_CREDS))
struct msghdr msg;
/* Credentials structure */
Index: src/interfaces/libpq/fe-auth.c
===
RCS file: /cvsroot/pgsql/src/interfaces/libpq/fe-auth.c,v
retrieving revision 1.60
diff -c -r1.60 fe-auth.c
*** src/interfaces/libpq/fe-auth.c 2001/09/21 20:31:49 1.60
--- src/interfaces/libpq/fe-auth.c 2001/09/26 19:30:53
***
*** 435,444
#endif /* KRB5 */
- #if defined(HAVE_STRUCT_CMSGCRED) || defined(HAVE_STRUCT_FCRED) ||
defined(HAVE_STRUCT_SOCKCRED)
static int
pg_local_sendauth(char *PQerrormsg, PGconn *conn)
{
char buf;
struct iovec iov;
struct msghdr msg;
--- 435,444
#endif /* KRB5 */
static int
pg_local_sendauth(char *PQerrormsg, PGconn *conn)
{
+ #if defined(HAVE_STRUCT_CMSGCRED) || defined(HAVE_STRUCT_FCRED) ||
+(defined(HAVE_STRUCT_SOCKCRED) && defined(LOCAL_CREDS))
char buf;
struct iovec iov;
struct msghdr msg;
***
*** 485,492
return STATUS_ERROR;
}
return STATUS_OK;
! }
#endif
static int
pg_password_sendauth(PGconn *conn, const char *password, AuthRequest areq)
--- 485,496
return STATUS_ERROR;
}
return STATUS_OK;
! #else
! snprintf(PQerrormsg, PQERRORMSG_LENGTH,
!libpq_gettext("SCM_CRED authentication method not
supported\n"));
! return STATUS_ERROR;
#endif
+ }
static int
pg_password_sendauth(PGconn *conn, const char *password, AuthRequest areq)
***
*** 614,627
break;
case AUTH_REQ_SCM_CREDS:
- #if defined(HAVE_STRUCT_CMSGCRED) || defined(HAVE_STRUCT_FCRED) ||
defined(HAVE_STRUCT_SOCKCRED)
if (pg_local_sendauth(PQerrormsg, conn) != STATUS_OK)
return STATUS_ERROR;
- #else
- snprintf(PQerrormsg, PQERRORMSG_LENGTH,
-libpq_gettext("SCM_CRED authentication method
not supported\n"));
- return STATUS_ERROR;
- #endif
break;
default:
---
Re: [HACKERS] Spinlock performance improvement proposal
Neil Padgett <[EMAIL PROTECTED]> writes: > Initial results (top five -- if you would like a complete profile, let > me know): > Each sample counts as 1 samples. > % cumulative self self total > time samples samplescalls T1/call T1/call name > 26.57 42255.02 42255.02 FindLockCycleRecurse Yipes. It would be interesting to know more about the locking pattern of your benchmark --- are there long waits-for chains, or not? The present deadlock detector was certainly written with an eye to "get it right" rather than "make it fast", but I wonder whether this shows a performance problem in the detector, or just too many executions because you're waiting too long to get locks. > However, this seems to be a red herring. Removing the deadlock detector > had no effect. In fact, benchmarking showed removing it yielded no > improvement in transaction processing rate on uniprocessor or SMP > systems. Instead, it seems that the deadlock detector simply amounts to > "something to do" for the blocked backend while it waits for lock > acquisition. Do you have any idea about the typical lock-acquisition delay in this benchmark? Our docs advise trying to set DEADLOCK_TIMEOUT higher than the typical acquisition delay, so that the deadlock detector does not run unnecessarily. > For example, there has been some suggestion > that perhaps some component of the database is causing large lock > contention. My thought as well. I would certainly recommend that you use more than one test case while looking at these things. regards, tom lane ---(end of broadcast)--- TIP 3: 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] Spinlock performance improvement proposal
"D. Hageman" <[EMAIL PROTECTED]> writes: > The plan for the new spinlocks does look like it has some potential. My > only comment in regards to permformance when we start looking at SMP > machines is ... it is my belief that getting a true threaded backend may > be the only way to get the full potential out of SMP machines. Depends on what you mean. For scaling well with many connections and simultaneous queries, there's no reason IMHO that the current process-per-backend model won't do, assuming the locking issues are addressed. If you're talking about making a single query use multiple CPUs, then yes, we're probably talking about a fundamental rewrite to use threads or some other mechanism. -Doug -- In a world of steel-eyed death, and men who are fighting to be warm, Come in, she said, I'll give you shelter from the storm.-Dylan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Spinlock performance improvement proposal
Tom Lane wrote: > > At the just-past OSDN database conference, Bruce and I were annoyed by > some benchmark results showing that Postgres performed poorly on an > 8-way SMP machine. Based on past discussion, it seems likely that the > culprit is the known inefficiency in our spinlock implementation. > After chewing on it for awhile, we came up with an idea for a solution. > > The following proposal should improve performance substantially when > there is contention for a lock, but it creates no portability risks > because it uses the same system facilities (TAS and SysV semaphores) > that we have always relied on. Also, I think it'd be fairly easy to > implement --- I could probably get it done in a day. > > Comments anyone? We have been doing some scalability testing just recently here at Red Hat. The machine I was using was a 4-way 550 MHz Xeon SMP machine, I also ran the machine in uniprocessor mode to make some comparisons. All runs were made on Red Hat Linux running 2.4.x series kernels. I've examined a number of potentially interesting cases -- I'm still analyzing the results, but some of the initial results might be interesting: - We have tried benchmarking the following: TAS spinlocks (existing implementation), SysV semaphores (existing implementation), Pthread Mutexes. Pgbench runs were conducted for 1 to 512 simultaneous backends. For these three cases we found: - TAS spinlocks fared the best of all three lock types, however above 100 clients the Pthread mutexes were lock step in performance. I expect this is due to the cost of any system calls being negligible relative to lock wait time. - SysV semaphore implementation faired terribly as expected. However, it is worse, relative to the TAS spinlocks on SMP than on uniprocessor. - Since the above seemed to indicate that the lock implementation may not be the problem (Pthread mutexes are supposed to be implemented to be less bang-bang than the Postgres TAS spinlocks, IIRC), I decided to profile Postgres. After much trouble, I got results for it using oprofile, a kernel profiler for Linux. Unfortunately, I can only profile for uniprocessor right now using oprofile, as it doesn't support SMP boxes yet. (soon, I hope.) Initial results (top five -- if you would like a complete profile, let me know): Each sample counts as 1 samples. % cumulative self self total time samples samplescalls T1/call T1/call name 26.57 42255.02 42255.02 FindLockCycleRecurse 5.55 51081.02 8826.00 s_lock_sleep 5.07 59145.03 8064.00 heapgettup 4.48 66274.03 7129.00 hash_search 4.48 73397.03 7123.00 s_lock 2.85 77926.03 4529.00 HeapTupleSatisfiesSnapshot 2.07 81217.04 3291.00 SHMQueueNext 1.85 84154.04 2937.00 AllocSetAlloc 1.84 87085.04 2931.00 fmgr_isbuiltin 1.64 89696.04 2611.00 set_ps_display 1.51 92101.04 2405.00 FunctionCall2 1.47 94442.04 2341.00 XLogInsert 1.39 96649.04 2207.00 _bt_compare 1.22 98597.04 1948.00 SpinAcquire 1.22 100544.04 1947.00 LockBuffer 1.21 102469.04 1925.00 tag_hash 1.01 104078.05 1609.00 LockAcquire . . . (The samples are proportional to execution time.) This would seem to point to the deadlock detector. (Which some have fingered as a possible culprit before, IIRC.) However, this seems to be a red herring. Removing the deadlock detector had no effect. In fact, benchmarking showed removing it yielded no improvement in transaction processing rate on uniprocessor or SMP systems. Instead, it seems that the deadlock detector simply amounts to "something to do" for the blocked backend while it waits for lock acquisition. Profiling bears this out: Flat profile: Each sample counts as 1 samples. % cumulative self self total time samples samplescalls T1/call T1/call name 12.38 14112.01 14112.01 s_lock_sleep 10.18 25710.01 11598.01 s_lock 6.47 33079.01 7369.00 hash_search 5.88 39784.02 6705.00 heapgettup 5.32 45843.02 6059.00 HeapTupleSatisfiesSnapshot 2.62 48830.02 2987.00 AllocSetAlloc 2.48 51654.02 2824.00 fmgr_isbuiltin 1.89 53813.02 2159.00 XLogInsert 1.86 55938.02 2125.00 _bt_compare 1.72 57893.03 1955.00
Re: [HACKERS] Spinlock performance improvement proposal
The plan for the new spinlocks does look like it has some potential. My only comment in regards to permformance when we start looking at SMP machines is ... it is my belief that getting a true threaded backend may be the only way to get the full potential out of SMP machines. I see that is one of the things to experiment with on the TODO list and I have seen some people have messed around already with this using Solaris threads. It should probably be attempted with pthreads if PostgreSQL is going to keep some resemblance of cross-platform compatibility. At that time, it would probably be easier to go in and clean up some stuff for the implementation of other TODO items (put in the base framework for more complex future items) as threading the backend would take a little bit of ideology shift. Of course, it is much easier to stand back and talk about this then actually do it - especially comming from someone who has only tried to contribute a few pieces of code. Keep up the good work. On Wed, 26 Sep 2001, Tom Lane wrote: > At the just-past OSDN database conference, Bruce and I were annoyed by > some benchmark results showing that Postgres performed poorly on an > 8-way SMP machine. Based on past discussion, it seems likely that the > culprit is the known inefficiency in our spinlock implementation. > After chewing on it for awhile, we came up with an idea for a solution. > > The following proposal should improve performance substantially when > there is contention for a lock, but it creates no portability risks > because it uses the same system facilities (TAS and SysV semaphores) > that we have always relied on. Also, I think it'd be fairly easy to > implement --- I could probably get it done in a day. > > Comments anyone? > > regards, tom lane -- //\\ || D. Hageman<[EMAIL PROTECTED]> || \\// ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Combining chars in psql (pre-patch)
Hi,
I have been working a bit at a patch for that problem in psql. The
patch is far from being ready for inclusion or whatever, it's just for
comments...
By the way, someone can tell me how to generate nice patches showing
the difference between one's version and the cvs code that has been
downloaded ? I'm new to this (I've only used cvs for personal projects
so far, and I don't need to send patches to myself ;) ).
The good things in this patch :
- it works for me :)
- I've used Markus Kuhn's implementation of wcwidth.c : it is locale
independant, and is in the public domain. :) [if we keep it, I'll
have to tell him, though !]
- No dependency on the local libc's UTF-8-awareness ;) [I've seen that
psql has no such dependancy, at least in print.c, so I haven't added
any]. Actually, the change is completely self-contained.
- I've made my own utf-8 -> ucs converter, since I haven't found any
without a copyright notice yesterday. It checks invalid and
non-optimal UTF-8 sequences, as requested per Unicode 3.0.1 (or 3.1,
I don't remember).
- it works for japanese (and I believe other "full-width" characters).
- if MULTIBYTE is not defined, the code doesn't change from the
commited version.
The not so good things :
- I've made my own utf-8 -> ucs converter... It seems to work fine,
but it's not tested well enough, it may not be so robust.
- The printf( "%*s", width, utfstr) doesn't work as expected, so I had
to fix by doing printf( "%*s%s", width - utfstrwidth, "", utfstr);
- everything in #ifdef MULTIBYTE/#endif . Since they're is no
dependancy on anything else (including the rest of the multibyte
implementation - which I haven't had the time to look at in detail),
it doesn't depend on it.
- I get this (for each call to pg_mb_utfs_width) and I don't know why :
print.c:265: warning: passing arg 1 of `pg_mb_utfs_width' discards
qualifiers from pointer target type
- If pg_mb_utfs_width finds an invalid UTF-8 string, it truncates it.
I suppose that's what we want to do, but that's probably not the
best place to do it.
The bad things :
- If MULTIBYTE is defined, the strings must be in UTF-8, it doesn't
check any encoding.
- it is not integrated at all with the rest of the MB code.
- it doesn't respect the indentation policy ;)
To do :
- integrate better with the rest of the MB (client-side encoding), and
with the rest of the code of print.c .
- verify utf8-to-ucs robustness seriously.
- make a visually nicer code :)
- find better function names.
And possibly :
- consolidate the code, in order to remove the need for the #ifdef's
in many places.
- make it working with some others multiwidth-encoding (but then, I
don't know anything about these encodings myself !).
- check also utf-8 stream at input time, so that no invalid utf-8 is
sent to the backend (at least from psql - the backend will need also
a strict checking for UTF-8).
- add nice UTF-8 borders as an option :)
- add a command-line parameter to consider Unicode Ambiguous
characters (characters which can be narrow or wide, depending on the
terminal) wide characters, as it seems to be the case for CJK
terminals (as per TR#11).
- What else ?
BTW, here is the table I had in the first mail. I would have shown the
one with all the weird Unicode characters, but my mutt is configured
with iso-8859-15, and I doubt many of you have utf-8 as a default yet
:)
+--+---++
| lang | text | text |
+--+---++
| isl | álíta | áleit |
| isl | álíta | álitum |
| isl | álíta | álitið |
| isl | maður | mann |
| isl | maður | mönnum |
| isl | maður | manna |
| isl | óska | -aði |
+--+---++
The files in attachment :
- a diff for pgsql/src/bin/psql/print.c
- a diff for pgsql/src/bin/psql/Makefile
- two new files :
pgsql/src/bin/psql/pg_mb_utf8.c
pgsql/src/bin/psql/pg_mb_utf8.h
Have fun !
Patrice
--
Patrice HÉDÉ --- patrice à islande org -
-- Isn't it weird how scientists can imagine all the matter of the
universe exploding out of a dot smaller than the head of a pin, but they
can't come up with a more evocative name for it than "The Big Bang" ?
-- What would _you_ call the creation of the universe ?
-- "The HORRENDOUS SPACE KABLOOIE !" - Calvin and Hobbes
-- http://www.islande.org/ -
*** pgsql/src/bin/psql/print.c~ Wed Aug 1 20:44:54 2001
--- pgsql/src/bin/psql/print.c Wed Sep 26 19:30:42 2001
***
*** 33,38
--- 33,41
#include
#endif
+ #ifdef MULTIBYTE
+ #include "pg_mb_utf8.h"
+ #endif
/*/
/* Unaligned text */
***
*** 213,218
--- 216,227
FILE *fout)
{
unsigned int col_count = 0;
+
+ #ifdef MULTIBYTE
+ unsigned int cell_count = 0;
+ unsigned int *head_w, *cell_w;
+ #endif
+
Re: [HACKERS] Spinlock performance improvement proposal
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > Let''s try and target Monday for Beta then? Sounds like a plan. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Spinlock performance improvement proposal
Sounds cool to me ... definitely something to fix before v7.2, if its as "easy" as you make it sound ... I'm expecting the new drive to be installed today (if all goes well ... Thomas still has his date/time stuff to finish off, now that CVSup is fixed ... Let''s try and target Monday for Beta then? I think the only two outstaandings are you and Thomas right now? Bruce, that latest rtree patch looks intriguing also ... can anyone comment positive/negative about it, so that we can try and get that in before Beta? On Wed, 26 Sep 2001, Tom Lane wrote: > At the just-past OSDN database conference, Bruce and I were annoyed by > some benchmark results showing that Postgres performed poorly on an > 8-way SMP machine. Based on past discussion, it seems likely that the > culprit is the known inefficiency in our spinlock implementation. > After chewing on it for awhile, we came up with an idea for a solution. > > The following proposal should improve performance substantially when > there is contention for a lock, but it creates no portability risks > because it uses the same system facilities (TAS and SysV semaphores) > that we have always relied on. Also, I think it'd be fairly easy to > implement --- I could probably get it done in a day. > > Comments anyone? > > regards, tom lane > > > Plan: > > Replace most uses of spinlocks with "lightweight locks" (LW locks) > implemented by a new lock manager. The principal remaining use of true > spinlocks (TAS locks) will be to provide mutual exclusion of access to > LW lock structures. Therefore, we can assume that spinlocks are never > held for more than a few dozen instructions --- and never across a kernel > call. > > It's pretty easy to rejigger the spinlock code to work well when the lock > is never held for long. We just need to change the spinlock retry code > so that it does a tight spin (continuous retry) for a few dozen cycles --- > ideally, the total delay should be some small multiple of the max expected > lock hold time. If lock still not acquired, yield the CPU via a select() > call (10 msec minimum delay) and repeat. Although this looks inefficient, > it doesn't matter on a uniprocessor because we expect that backends will > only rarely be interrupted while holding the lock, so in practice a held > lock will seldom be encountered. On SMP machines the tight spin will win > since the lock will normally become available before we give up and yield > the CPU. > > Desired properties of the LW lock manager include: > * very fast fall-through when no contention for lock > * waiting proc does not spin > * support both exclusive and shared (read-only) lock modes > * grant lock to waiters in arrival order (no starvation) > * small lock structure to allow many LW locks to exist. > > Proposed contents of LW lock structure: > > spinlock mutex (protects LW lock state and PROC queue links) > count of exclusive holders (always 0 or 1) > count of shared holders (0 .. MaxBackends) > queue head pointer (NULL or ptr to PROC object) > queue tail pointer (could do without this to save space) > > If a backend sees it must wait to acquire the lock, it adds its PROC > struct to the end of the queue, releases the spinlock mutex, and then > sleeps by P'ing its per-backend wait semaphore. A backend releasing the > lock will check to see if any waiter should be granted the lock. If so, > it will update the lock state, release the spinlock mutex, and finally V > the wait semaphores of any backends that it decided should be released > (which it removed from the lock's queue while holding the sema). Notice > that no kernel calls need be done while holding the spinlock. Since the > wait semaphore will remember a V occurring before P, there's no problem > if the releaser is fast enough to release the waiter before the waiter > reaches its P operation. > > We will need to add a few fields to PROC structures: > * Flag to show whether PROC is waiting for an LW lock, and if so > whether it waits for read or write access > * Additional PROC queue link field. > We can't reuse the existing queue link field because it is possible for a > PROC to be waiting for both a heavyweight lock and a lightweight one --- > this will occur when HandleDeadLock or LockWaitCancel tries to acquire > the LockMgr module's lightweight lock (formerly spinlock). > > It might seem that we also need to create a second wait semaphore per > backend, one to wait on HW locks and one to wait on LW locks. But I > believe we can get away with just one, by recognizing that a wait for an > LW lock can never be interrupted by a wait for a HW lock, only vice versa. > After being awoken (V'd), the LW lock manager must check to see if it was > actually granted the lock (easiest way: look at own PROC struct to see if > LW lock wait flag has been cleared). If not, the V must have been to > grant us a HW lock --- but
Re: [HACKERS] [BUGS] PostgreSQL / PHP Overrun Error
Interesting. I am using that same configuration. We are using the same thing on our website as well. I have never seen this problem. Weird. My guess is that you are getting an error. The PHP code is some how mucking this up. But I would try executing the query in psql and see what comes up. The PHP code than handles the error may have a fixed langth buffer for speed, and it is to short for a longer 7.1 error message. Again, I am guessing. My bet is that the query is failing with an error, so you really have two problems. A problem in your SQL which is causing you to see a bug in PHP. Mike Rogers wrote: > Sorry: > PHP 4.0.6 (with memory leak patch [download listed right below > php-4.0.6.tar.gz download- It was a problem]) > PostgreSQL 7.1.3 > Apache 1.3.20 (with mod_ssl- but it does the same thing without mod_ssl) > -- > Mike > > - Original Message - > From: "mlw" <[EMAIL PROTECTED]> > To: "Mike Rogers" <[EMAIL PROTECTED]> > Cc: "Tom Lane" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Wednesday, September 26, 2001 1:55 PM > Subject: Re: [BUGS] PostgreSQL / PHP Overrun Error > > > Mike Rogers wrote: > > > > > Well it really isn't your code (true), but the only thing that is > changed is > > > the 7.0-7.1- Was a data length changed on the return or something that > > > could affect this? > > > > What version of PHP are you using? > > > > > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [BUGS] PostgreSQL / PHP Overrun Error
Sorry: PHP 4.0.6 (with memory leak patch [download listed right below php-4.0.6.tar.gz download- It was a problem]) PostgreSQL 7.1.3 Apache 1.3.20 (with mod_ssl- but it does the same thing without mod_ssl) -- Mike - Original Message - From: "mlw" <[EMAIL PROTECTED]> To: "Mike Rogers" <[EMAIL PROTECTED]> Cc: "Tom Lane" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, September 26, 2001 1:55 PM Subject: Re: [BUGS] PostgreSQL / PHP Overrun Error > Mike Rogers wrote: > > > Well it really isn't your code (true), but the only thing that is changed is > > the 7.0-7.1- Was a data length changed on the return or something that > > could affect this? > > What version of PHP are you using? > > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] PostgreSQL / PHP Overrun Error
Mike Rogers wrote: > Well it really isn't your code (true), but the only thing that is changed is > the 7.0-7.1- Was a data length changed on the return or something that > could affect this? What version of PHP are you using? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [BUGS] PostgreSQL / PHP Overrun Error
Well it really isn't your code (true), but the only thing that is changed is the 7.0-7.1- Was a data length changed on the return or something that could affect this? -- Mike - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Mike Rogers" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, September 26, 2001 1:23 PM Subject: Re: [BUGS] PostgreSQL / PHP Overrun Error > "Mike Rogers" <[EMAIL PROTECTED]> writes: > > This problem is of great concern to me and I have been working for days > > trying to debug it myself and find other reports, with little success. The > > line it claims to be failing on is PHP's ext/pgsql/pgsql.c on line 167 (by > > what this claims) which is the following function [the > > efree(PGG(last_notice)) line]. > > This isn't our code, so you'd likely have better luck complaining on > some PHP-related list. But it looks to me like this code is simply > trying to free any previous notice message before it stores the new > one into PGG(last_notice) (whatever the heck that is). I'm guessing > that that pointer is uninitialized or has been clobbered somehow. > > regards, tom lane > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] PostgreSQL / PHP Overrun Error
"Mike Rogers" <[EMAIL PROTECTED]> writes: > This problem is of great concern to me and I have been working for days > trying to debug it myself and find other reports, with little success. The > line it claims to be failing on is PHP's ext/pgsql/pgsql.c on line 167 (by > what this claims) which is the following function [the > efree(PGG(last_notice)) line]. This isn't our code, so you'd likely have better luck complaining on some PHP-related list. But it looks to me like this code is simply trying to free any previous notice message before it stores the new one into PGG(last_notice) (whatever the heck that is). I'm guessing that that pointer is uninitialized or has been clobbered somehow. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Spinlock performance improvement proposal
At the just-past OSDN database conference, Bruce and I were annoyed by some benchmark results showing that Postgres performed poorly on an 8-way SMP machine. Based on past discussion, it seems likely that the culprit is the known inefficiency in our spinlock implementation. After chewing on it for awhile, we came up with an idea for a solution. The following proposal should improve performance substantially when there is contention for a lock, but it creates no portability risks because it uses the same system facilities (TAS and SysV semaphores) that we have always relied on. Also, I think it'd be fairly easy to implement --- I could probably get it done in a day. Comments anyone? regards, tom lane Plan: Replace most uses of spinlocks with "lightweight locks" (LW locks) implemented by a new lock manager. The principal remaining use of true spinlocks (TAS locks) will be to provide mutual exclusion of access to LW lock structures. Therefore, we can assume that spinlocks are never held for more than a few dozen instructions --- and never across a kernel call. It's pretty easy to rejigger the spinlock code to work well when the lock is never held for long. We just need to change the spinlock retry code so that it does a tight spin (continuous retry) for a few dozen cycles --- ideally, the total delay should be some small multiple of the max expected lock hold time. If lock still not acquired, yield the CPU via a select() call (10 msec minimum delay) and repeat. Although this looks inefficient, it doesn't matter on a uniprocessor because we expect that backends will only rarely be interrupted while holding the lock, so in practice a held lock will seldom be encountered. On SMP machines the tight spin will win since the lock will normally become available before we give up and yield the CPU. Desired properties of the LW lock manager include: * very fast fall-through when no contention for lock * waiting proc does not spin * support both exclusive and shared (read-only) lock modes * grant lock to waiters in arrival order (no starvation) * small lock structure to allow many LW locks to exist. Proposed contents of LW lock structure: spinlock mutex (protects LW lock state and PROC queue links) count of exclusive holders (always 0 or 1) count of shared holders (0 .. MaxBackends) queue head pointer (NULL or ptr to PROC object) queue tail pointer (could do without this to save space) If a backend sees it must wait to acquire the lock, it adds its PROC struct to the end of the queue, releases the spinlock mutex, and then sleeps by P'ing its per-backend wait semaphore. A backend releasing the lock will check to see if any waiter should be granted the lock. If so, it will update the lock state, release the spinlock mutex, and finally V the wait semaphores of any backends that it decided should be released (which it removed from the lock's queue while holding the sema). Notice that no kernel calls need be done while holding the spinlock. Since the wait semaphore will remember a V occurring before P, there's no problem if the releaser is fast enough to release the waiter before the waiter reaches its P operation. We will need to add a few fields to PROC structures: * Flag to show whether PROC is waiting for an LW lock, and if so whether it waits for read or write access * Additional PROC queue link field. We can't reuse the existing queue link field because it is possible for a PROC to be waiting for both a heavyweight lock and a lightweight one --- this will occur when HandleDeadLock or LockWaitCancel tries to acquire the LockMgr module's lightweight lock (formerly spinlock). It might seem that we also need to create a second wait semaphore per backend, one to wait on HW locks and one to wait on LW locks. But I believe we can get away with just one, by recognizing that a wait for an LW lock can never be interrupted by a wait for a HW lock, only vice versa. After being awoken (V'd), the LW lock manager must check to see if it was actually granted the lock (easiest way: look at own PROC struct to see if LW lock wait flag has been cleared). If not, the V must have been to grant us a HW lock --- but we still have to sleep to get the LW lock. So remember this happened, then loop back and P again. When we finally get the LW lock, if there was an extra P operation then V the semaphore once before returning. This will allow ProcSleep to exit the wait for the HW lock when we return to it. Fine points: While waiting for an LW lock, we need to show in our PROC struct whether we are waiting for read or write access. But we don't need to remember this after getting the lock; if we know we have the lock, it's easy to see by inspecting the lock whether we hold read or write access. ProcStructLock cannot be replaced by an LW lock, since a backend cannot use an LW lock until i
[HACKERS] PostgreSQL / PHP Overrun Error
I have just upgraded to the new PostgreSQL 7.1.3 (from 7.0.3) and have been
experiencing a pretty serious problem:
On one particular page, in what seems to be completely random instances,
I get buffer overruns and either 0-rows or a crashed apache child. Turning
on PHP's --enable-debug, I receive the following:
[Wed Sep 26 06:21:12 2001] Script: '/path/to/script.php'
---
pgsql.c(167) : Block 0x086A6DF8 status:
Beginning: Overrun (magic=0x, expected=0x7312F8DC)
End: Unknown
---
Sometimes it will actually crash mid-way (probably overwrote some valuable
code):
---
pgsql.c(167) : Block 0x08684290 status:
Beginning: Overrun (magic=0x111A, expected=0x7312F8DC)
[Wed Sep 26 09:22:46 2001] [notice] child pid 8710 exit signal Segmentation
fault (11)
This problem is of great concern to me and I have been working for days
trying to debug it myself and find other reports, with little success. The
line it claims to be failing on is PHP's ext/pgsql/pgsql.c on line 167 (by
what this claims) which is the following function [the
efree(PGG(last_notice)) line].
static void
_notice_handler(void *arg, const char *message)
{
PGLS_FETCH();
if (! PGG(ignore_notices)) {
php_log_err((char *) message);
if (PGG(last_notice) != NULL) {
efree(PGG(last_notice));
}
PGG(last_notice) = estrdup(message);
}
}
Can anyone provide further input as to why this is causing problems? The
PHP code works sometimes and not others, and it seems to be only that one
script, so I do not believe it to be a hardware issue.
Any thoughts? I can provide any further system information if needed. I
have tried recompiling pgsql, php and apache with different optimizations
[including none at all and debug mode on as i have now] with little change
in the result.
Thanks in advance;
--
Mike
cc: pgsql-hackers; pgsql-php; pgsql_bugs
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] optimizer question
"Reinoud van Leeuwen" <[EMAIL PROTECTED]> writes: > I have a table that contains almost 8 milion rows. The primary key is a > sequence, so the index should have a good distribution. Why does the > optimizer refuse to use the index for getting the maximum value? The optimizer has no idea that max() has anything to do with indexes. You could try something like select * from tab order by foo desc limit 1; regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PERFORMANCE IMPROVEMENT by mapping WAL FILES
Janardhana Reddy <[EMAIL PROTECTED]> writes: > By mapping the WAL files by each backend in to its address > space using "mmap" system call , There are a lot of problems with trying to use mmap for Postgres. One is portability: not all platforms have mmap, so we'd still have to support the non-mmap case; and it's not at all clear that fsync/msync semantics are consistent across platforms, either. A bigger objection is that mmap'ing a file in one backend does not cause it to become available to other backends, thus the entire concept of shared buffers breaks down. If you think you can make it work, feel free to try it ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] iscacheable for date/time?
Thomas Lockhart <[EMAIL PROTECTED]> writes: > ... and how about the istrusted attribute for various routines? Should > it be always false or always true for C builtin functions? At the moment it seems to be true for every pg_proc entry in template1. AFAIK the attribute is not actually being looked at, anyway. I think it used to be used to determine which functions needed to be executed in a separate subprocess for safety reasons (ie, coredump of the function wouldn't kill the backend) ... but that code's been gone for a long while. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] iscacheable for date/time?
Thomas Lockhart <[EMAIL PROTECTED]> writes: > Can anyone recall why the interval data type would have been > considered non-cacheable? I believe I made all functions for all datetime-related types noncacheable, simply because I wasn't sure which of them had the "current" behavior. > For timestamp and timestamptz, I've eliminated the "current" special > value which afaicr is the only reason timestamp had not been cacheable > in the past. Are there any functions which should *not* be considered > cacheable for those types? Apparently the _in() and _out() functions > should not be? in() should not be, since its result for the strings "now", "today", "tomorrow", etc is variable. But AFAICS there's no reason to mark out() as noncacheable anymore. The general rule is: if there are any fixed input values for which the output might vary over time, then it should be noncachable. Dunno why to_char is marked noncachable; does it perhaps have format-string entries that pick up current time somehow? I might just have been worried about its response to "current", though. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PERFORMANCE IMPROVEMENT by mapping WAL FILES
> Hi all, > By mapping the WAL files by each backend in to its address > space using "mmap" system call , there will be big > improvements in performance from the following point of view: > 1. Each backend directly writes in to the address > space which is obtained by maping the WAL files. > this saves the write system call at the end of > every transaction which transfres 8k of > data from user space to kernel. >2. since every transaction does not modify all the 8k > content of WAL page , so by issuing the >fsync , the kernel only transfers only the > kernel pages which are modified , which is 4k for > linux so fsync time is saved by twice. > Any comments ?. This is interesting. We are concerned about using mmap() for all I/O because we could eat up quite a bit of address space for big tables, but WAL seems like an ideal use for mmap(). -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Converting from pgsql to sqlserver?
Hi Armindo, Ian Harding has written a guide for converting from MS SQL Server to PostgreSQL. I know this is the opposite of what you want, but it might be useful as it highlights some of the areas of difference between these products : http://techdocs.postgresql.org/techdocs/sqlserver2pgsql.php Hope that's useful. :-) Regards and best wishes, Justin Clift [EMAIL PROTECTED] wrote: > > Greetings. > I don´t know if this is the right place to ask this, sorry if this don't > belong here. > I begun this week working in a new firm. They use linux and PostgreSQL as > the database for the Intranet site and for the management of CV's and > Knowledge Management (they have an on-line system to manage and search > information about workers of the firm and projects they have). > > They now want to convert from the current linux/postgresql platform to > Windows and SQLServer. I've never worked before with linux or PostgreSQL so > i know nothing about the capabilities of this combo. > > My question are: > > - the PostgreSQL database has a lot of information. Is it possible to > migrate the data of the PostgreSQL to SQLServer in Windows? What do i need > to do? > > - is it possible to migrate the tables relationships (the relational schema) > of the DB to SQLServer or do i have to build the DB from scratch? > > Thanks for reading, > Armindo Dias > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] iscacheable for date/time?
Thomas Lockhart wrote: > ... and how about the istrusted attribute for various routines? Should > it be always false or always true for C builtin functions? How about for > builtin SQL functions which are built on top of trusted C functions? Are > we guarding against catalog changes on the underlying C routines? I have always had trouble with the "iscacheable" flag, there needs to be a number of "cache" levels: (1) cache per transaction, so you can use a function in a where statement and it does not force a table scan. IMHO this should be the default for all functions, but is not supported in PostgreSQL. (2) nocache, which would mean it forces a tables scan. This is the current default. (3) global cache, which means the results can be stored in perpetuity, this is the current intended meaning of iscacheable. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] iscacheable for date/time?
... and how about the istrusted attribute for various routines? Should it be always false or always true for C builtin functions? How about for builtin SQL functions which are built on top of trusted C functions? Are we guarding against catalog changes on the underlying C routines? - Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] iscacheable for date/time?
How about iscacheable for the to_char() functions? Can we recall why those are not cacheable, even for non-date/time types? - Thomas ---(end of broadcast)--- TIP 3: 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
[HACKERS] iscacheable for date/time?
I'm looking at pg_proc.h to adjust the cacheable attribute for date/time functions. Can anyone recall why the interval data type would have been considered non-cacheable? I didn't make internal changes to that type, but istm that it should be cacheable already. For timestamp and timestamptz, I've eliminated the "current" special value which afaicr is the only reason timestamp had not been cacheable in the past. Are there any functions which should *not* be considered cacheable for those types? Apparently the _in() and _out() functions should not be? Everything else is deterministic so would seem to be a candidate. Comments? - Thomas ---(end of broadcast)--- TIP 3: 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
[HACKERS] optimizer question
Hi,
I have a table that contains almost 8 milion rows. The primary key is a
sequence, so the index should have a good distribution. Why does the
optimizer refuse to use the index for getting the maximum value?
(even after a vacuum analyze of the table)
radius=# explain select max(radiuspk) from radius ;
NOTICE: QUERY PLAN:
Aggregate (cost=257484.70..257484.70 rows=1 width=8)
-> Seq Scan on radius (cost=0.00..237616.76 rows=7947176 width=8)
Table and key info:
Did not find any relation named "radius_pk".
radius=# \d radius
Table "radius"
Attribute | Type | Modifier
-+--+---
sessionid | character varying(30)| not null
username| character varying(30)| not null
nas_ip | character varying(50)| not null
logfileid | integer |
login_ip_host | character varying(50)| not null
framed_ip_address | character varying(50)|
file_timestamp | timestamp with time zone | not null
corrected_timestamp | timestamp with time zone | not null
acct_status_type| smallint | not null
bytesin | bigint |
bytesout| bigint |
handled | boolean | not null default 'f'
sessionhandled | boolean | not null default 'f'
radiuspk| bigint | not null default nextval
('radiuspk_seq'::text)
Indices: pk_radius,
radius_us
radius=# \d pk_radius
Index "pk_radius"
Attribute | Type
---+
radiuspk | bigint
unique btree (primary key)
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Unicode combining characters
> BTW, I see "CHARACTER SET" in gram.y. Does current already support > that syntax? Yes and no. gram.y knows about CHARACTER SET, but only for the long form, the clause is in the wrong position (it preceeds the length specification) and it does not do much useful (generates a data type based on the character set name which does not get recognized farther back). Examples: thomas=# create table t1 (c varchar(20) character set sql_ascii); ERROR: parser: parse error at or near "character" thomas=# create table t1 (c character varying character set sql_ascii (20)); ERROR: Unable to locate type name 'varsql_ascii' in catalog I'm pretty sure I'll get shift/reduce troubles when trying to move that clause to *after* the length specifier. I'll try to do something with the syntax for 7.2 once I've finished the date/time stuff. - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] tuple statistics function
Tatsuo Ishii wrote:
> Here is a revised version of pg_stattuple, which shows how many tuples
> are "dead" etc. Per Tom's suggestion, a statistic of free/resuable
> space is now printed.
>
> test=# select pgstattuple('accounts');
> NOTICE: physical length: 39.06MB live tuples: 10 (12.59MB, 32.23%) dead tuples:
>20 (25.18MB, 64.45%) free/reusable space: 0.04MB (0.10%) overhead: 3.22%
> pgstattuple
> -
>64.453125
>
> What I'm not sure is:
>
> o Should I place any kind of lock after reading buffer?
>
> o Should I use similar algorithm to the one used in vacuum to determin
> whether the tuple is "dead" or not?
>
> Suggestions?
A little unrelated to your question, but what about returning
an array of all the values and adding another argument to
suppress the NOTICE? That would IMHO make the function very
useful in administrative tools.
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Converting from pgsql to sqlserver?
[EMAIL PROTECTED] wrote: > > Greetings. > I don´t know if this is the right place to ask this, sorry if this don't > belong here. > I begun this week working in a new firm. They use linux and PostgreSQL as > the database for the Intranet site and for the management of CV's and > Knowledge Management (they have an on-line system to manage and search > information about workers of the firm and projects they have). > > They now want to convert from the current linux/postgresql platform to > Windows and SQLServer. I've never worked before with linux or PostgreSQL so > i know nothing about the capabilities of this combo. I have no idea under what circumstance one would wish to go from PostgreSQL to MicrosoftSQL, it just seems like a mistake, but hey, you'll be back when you realize how much it costs and how much you lose. > > My question are: > > - the PostgreSQL database has a lot of information. Is it possible to > migrate the data of the PostgreSQL to SQLServer in Windows? What do i need > to do? PostgreSQL has a lot of export utilities. I'm not sure what MSSQL currently supports. Find a match, and do the export/import. You may be able to use "pg_dump" to dump out the database as a series of SQL inserts. It will be slow, but it should be the more portable way to do it. Just be carfull of date formats. You will probably need a few tries. > > - is it possible to migrate the tables relationships (the relational schema) > of the DB to SQLServer or do i have to build the DB from scratch? You will need to dump out the schema for the postgres database by executing "pg_dump -s database" and that will dump out a SQL script which will create the schema. no doubt, you will have to edit it to make it work with MSSQL but, it is a good start. May I ask why you are going from PostgreSQL to MSSQL? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] PERFORMANCE IMPROVEMENT by mapping WAL FILES
Hi all, By mapping the WAL files by each backend in to its address space using "mmap" system call , there will be big improvements in performance from the following point of view: 1. Each backend directly writes in to the address space which is obtained by maping the WAL files. this saves the write system call at the end of every transaction which transfres 8k of data from user space to kernel. 2. since every transaction does not modify all the 8k content of WAL page , so by issuing the fsync , the kernel only transfers only the kernel pages which are modified , which is 4k for linux so fsync time is saved by twice. Any comments ?. Regards jana ---(end of broadcast)--- TIP 3: 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
[HACKERS] Converting from pgsql to sqlserver?
Greetings. I don´t know if this is the right place to ask this, sorry if this don't belong here. I begun this week working in a new firm. They use linux and PostgreSQL as the database for the Intranet site and for the management of CV's and Knowledge Management (they have an on-line system to manage and search information about workers of the firm and projects they have). They now want to convert from the current linux/postgresql platform to Windows and SQLServer. I've never worked before with linux or PostgreSQL so i know nothing about the capabilities of this combo. My question are: - the PostgreSQL database has a lot of information. Is it possible to migrate the data of the PostgreSQL to SQLServer in Windows? What do i need to do? - is it possible to migrate the tables relationships (the relational schema) of the DB to SQLServer or do i have to build the DB from scratch? Thanks for reading, Armindo Dias ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] time without time zone
Gah. Ignore my previous email - I read the docs further and it turns out that "time" and "time without time zone" are synonymns. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] time without time zone
In 7.1.3, you can create a column as "time without time zone", but it
doesn't seem to show as such in psql...
eg:
test=# alter table chat_meetings add column timeofday time without time
zone;
ALTER
test=# \d chat_meetings
Table "chat_meetings"
Attribute | Type |Modifier
++--
--
meeting_id | integer| not null default
nextval('chat_meetings_mee
ting_id_seq'::text)
host_id| integer| not null
title | character varying(255) | not null
abstract | text | not null
time | integer| not null
dayofweek | smallint |
timeofday | time |
Indices: chat_meetings_pkey,
host_id_chat_meetings_key
There's no easy way of seeing what exact type the column is it seems.
Chris
---(end of broadcast)---
TIP 3: 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] anoncvs failure...
> On Mon, Sep 24, 2001 at 10:22:28AM -0400, Marc G. Fournier wrote: > > > > okay, somehow you have two different CVSROOT's configured? > > /home/projects/pgsql/cvsroot was the old server, /projects/cvsroot is the > > new one > > Any hints? I had done a (csh) > cd /usr/src/local/pgsql > find . -name Root -print > allroots > grep -v CVS allroots > foreach i ( `cat allroots`) > echo ":pserver:[EMAIL PROTECTED]:/projects/cvsroot" > $i > end > > and CVSROOT is not set as an environment variable... Also odd that it > appears there and there is no sign of "home" anywhere.. I would just delete the old CVS tree and download a new one. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Unicode combining characters
> > I would like to see SQL99's charset, collate functionality for 7.3 (or > > later). If this happens, current multibyte implementation would be > > dramatically changed... > > I'm *still* interested in working on this (an old story I know). I'm > working on date/time stuff for 7.2, but hopefully 7.3 will see some > advances in the SQL99 direction on charset etc. BTW, I see "CHARACTER SET" in gram.y. Does current already support that syntax? -- Tatsuo Ishii ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
