[HACKERS] 8.2 Crash on Query
I have been able to crash the database consistently on a Fedora Core 5 machine running postgresql 8.2.0. The attached files are an example database (crash.shema) and the query that is used (crash.sql) as well as the log output from turning on all the debugging (crash.log). I have a couple of other queries that do this as well, but this is the first one that I noticed. This database schema and query works fine version 8.1.4. -- //\\ || D. Hagemandhageman@dracken.com || \\//LOG: 0: database system was shut down at 2007-01-02 12:16:33 CST LOCATION: StartupXLOG, xlog.c:4672 LOG: 0: checkpoint record is at 0/130E42C LOCATION: StartupXLOG, xlog.c:4762 LOG: 0: redo record is at 0/130E42C; undo record is at 0/0; shutdown TRUE LOCATION: StartupXLOG, xlog.c:4789 LOG: 0: next transaction ID: 0/59568; next OID: 32768 LOCATION: StartupXLOG, xlog.c:4793 LOG: 0: next MultiXactId: 1; next MultiXactOffset: 0 LOCATION: StartupXLOG, xlog.c:4796 LOG: 0: database system is ready LOCATION: StartupXLOG, xlog.c:5188 DEBUG: 0: transaction ID wrap limit is 2147484171, limited by database postgres LOCATION: SetTransactionIdLimit, varsup.c:278 DEBUG: 0: proc_exit(0) LOCATION: proc_exit, ipc.c:94 DEBUG: 0: shmem_exit(0) LOCATION: shmem_exit, ipc.c:125 DEBUG: 0: exit(0) LOCATION: proc_exit, ipc.c:112 DEBUG: 0: reaping dead processes LOCATION: reaper, postmaster.c:2010 DEBUG: 0: forked new backend, pid=32688 socket=7 LOCATION: BackendStartup, postmaster.c:2573 DEBUG: 0: Ident protocol identifies remote user as postgres LOCATION: authident, hba.c:1615 DEBUG: 0: postmaster child[32688]: starting with ( LOCATION: BackendRun, postmaster.c:2917 DEBUG: 0: postgres LOCATION: BackendRun, postmaster.c:2920 DEBUG: 0: -v196608 LOCATION: BackendRun, postmaster.c:2920 DEBUG: 0: -y LOCATION: BackendRun, postmaster.c:2920 DEBUG: 0: test LOCATION: BackendRun, postmaster.c:2920 DEBUG: 0: ) LOCATION: BackendRun, postmaster.c:2922 DEBUG: 0: InitPostgres LOCATION: PostgresMain, postgres.c:3137 DEBUG: 0: StartTransaction LOCATION: ShowTransactionState, xact.c:3985 DEBUG: 0: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 59568/1/0, nestlvl: 1, children: LOCATION: ShowTransactionStateRec, xact.c:4010 DEBUG: 0: CommitTransaction LOCATION: ShowTransactionState, xact.c:3985 DEBUG: 0: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 59568/1/0, nestlvl: 1, children: LOCATION: ShowTransactionStateRec, xact.c:4010 DEBUG: 0: StartTransactionCommand LOCATION: start_xact_command, postgres.c:2200 STATEMENT: SELECT f.id, f.category_id, f.status, f.moderated, f.topics, f.posts, f.forum, f.last_post, f.system_name, f.description, p.last_post_date, p.last_post_time, p.topic FROM crash.forum f LEFT JOIN crash.forum_post p ON ( f.last_post = p.id ) WHERE ( f.status = 1 ) ORDER BY f.forum, f.id LIMIT ALL OFFSET 0; DEBUG: 0: StartTransaction LOCATION: ShowTransactionState, xact.c:3985 STATEMENT: SELECT f.id, f.category_id, f.status, f.moderated, f.topics, f.posts, f.forum, f.last_post, f.system_name, f.description, p.last_post_date, p.last_post_time, p.topic FROM crash.forum f LEFT JOIN crash.forum_post p ON ( f.last_post = p.id ) WHERE ( f.status = 1 ) ORDER BY f.forum, f.id LIMIT ALL OFFSET 0; DEBUG: 0: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 59569/1/0, nestlvl: 1, children: LOCATION: ShowTransactionStateRec, xact.c:4010 STATEMENT: SELECT f.id, f.category_id, f.status, f.moderated, f.topics, f.posts, f.forum, f.last_post, f.system_name, f.description, p.last_post_date, p.last_post_time, p.topic FROM crash.forum f LEFT JOIN crash.forum_post p ON ( f.last_post = p.id ) WHERE ( f.status = 1 ) ORDER BY f.forum, f.id LIMIT ALL OFFSET 0; LOG: 0: statement: SELECT f.id, f.category_id, f.status, f.moderated, f.topics, f.posts, f.forum, f.last_post, f.system_name, f.description, p.last_post_date, p.last_post_time, p.topic FROM crash.forum f LEFT JOIN crash.forum_post p ON ( f.last_post = p.id ) WHERE ( f.status = 1 ) ORDER BY f.forum, f.id LIMIT ALL OFFSET 0; LOCATION: exec_simple_query, postgres.c:811 STATEMENT: SELECT f.id, f.category_id, f.status, f.moderated, f.topics, f.posts, f.forum, f.last_post, f.system_name, f.description, p.last_post_date, p.last_post_time, p.topic FROM crash.forum f LEFT JOIN crash.forum_post p ON ( f.last_post = p.id ) WHERE ( f.status = 1 ) ORDER BY f.forum, f.id LIMIT ALL OFFSET 0; DEBUG: 0: reaping dead processes LOCATION: reaper, postmaster.c:2010 DEBUG: 0: server process (PID 32688) was terminated by signal 11 LOCATION: LogChildExit, postmaster.c:2425 LOG: 0: server process (PID 32688) was terminated
[HACKERS] Namespace/Table Visibility Behavior Issues
I didn't see this make it to the list. I thought I would try again. -- //\\ || D. Hageman[EMAIL PROTECTED] || \\// -- Forwarded message -- Date: Sat, 18 Jan 2003 16:51:12 -0600 (CST) From: D. Hageman [EMAIL PROTECTED] To: PostgreSQL-development [EMAIL PROTECTED] Subject: Namespace/Table Visibility Behavior Issues Assume a database with a couple of namespaces. Give two of these namespaces the names test_1 and test_2. Under these namespaces create a couple of tables with the names: example, example_2, example_3. set search_path to test_1, test_2; In the psql client, using a standard \d you will only see the namespace test_1 listed and the tables underneath that. test_2 will not be visible due to the fact they fail the pg_table_is_visible() check. I am not sure that is wise to do the pg_table_is_visible check on those commands. In my humble opinion, those commands are for understanding the layout/structure/nature of the database. If you can't see all your namespaces that you set in your search_path then it could distort ones understanding of the database. -- //\\ || D. Hageman[EMAIL PROTECTED] || \\// ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Namespace/Table Visibility Behavior Issues
Assume a database with a couple of namespaces. Give two of these namespaces the names test_1 and test_2. Under these namespaces create a couple of tables with the names: example, example_2, example_3. set search_path to test_1, test_2; In the psql client, using a standard \d you will only see the namespace test_1 listed and the tables underneath that. test_2 will not be visible due to the fact they fail the pg_table_is_visible() check. I am not sure that is wise to do the pg_table_is_visible check on those commands. In my humble opinion, those commands are for understanding the layout/structure/nature of the database. If you can't see all your namespaces that you set in your search_path then it could distort ones understanding of the database. -- //\\ || D. Hageman[EMAIL PROTECTED] || \\// ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgresql and multithreading
This in many ways is a bogus argument in that 1) postgresql runs on more then just Linux and 2) amount of memmory that can be addressed by a process is tunable up to the point that it reaches a hardware limitation. It also should be noted that when a process reaches such a size that it better have a good reason. Now let us do a gedanken experiment and say you do have a good reason - fork a couple of these and your machine will thrash like nothing else ... also that whole hardware limitation will come into play more sooner then later ... On 21 Oct 2002, Doug McNaught wrote: Steve Wolfe [EMAIL PROTECTED] writes: On the recurring debate of threading vs. forking, I was giving it a fwe thoughts a few days ago, particularly with concern to Linux's memory model. On IA32 platforms with over 4 gigs of memory, any one process can only see up to 3 or 4 gigs of that. Having each postmaster fork off as a new process obviously would allow a person to utilize very copious quantities of memory, assuming that (a) they were dealing with concurrent PG sessions, and (b) PG had reason to use the memory. I'm not entirely clear on threading in Linux - would it provide the same benefits, or would it suddenly lock you into a 3-gig memory space? Linux threads are basically processes that share the same VM space, so you'd be limited to 3GB or whatever, since that's what a VM space can see. -Doug ---(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 -- //\\ || D. Hageman[EMAIL PROTECTED] || \\// ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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
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 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
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
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] 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] bugs - lets call an exterminator!
On Tue, 21 Aug 2001, Vince Vielhaber wrote: In the seemingly hundreds of thousands of messages on the bug database topic I think I've come up with the following.. Solution.. Is implementing yet another bugtool going to be the solution? Probably not. Do I want to go for number six? No. The current tool has a form - we keep it. The current tool mails to the bugs list - we keep it. You are correct on implementing another bug reporting tool - why re-invent the wheel? Why not use the bugzilla project for bug tracking? I do believe it has a postgresql backend by now and if it doesn't - I am sure it will soon or would be trivial to make a backend and contribute it back. This tool has been popularized by Mozilla and RedHat ... saying that I am sure the couple of RedHat employees on the list wouldn't mind giving a hand with setup and what not (though they will have to speak for themselves on this issues). -- //\\ || 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
