[HACKERS] Attaching and using the Postgres shared memory segment
to look into an idea I currently have I need (and implemented) a new piece of memory that resides in (Postgres) shared memory. My data structures are in place an the new database seems running fine. Even under high load :-) Since these data structures are for collecting information I was able to create a new function that retrieves its information from these data structures and returns them as a result from a query. However this is too intrusive. Since everything is in shared memory it should be possible for an external, yet to be created, process to attach to the shared memory segment (read only mode only) to collect these data structures. However, when I read the source I feel a little stuck. Kind of chicken and egg situation perhaps. I have the address mapping of the shared memory (doing stuff on Solaris only). I can pass this information to InitShmemAccess (ipc/ shmem.c). There is however a missing settting for ShmemIndex. This one can be initialsed by calling InitShmemIndex(). The only thing that makes me wonder is its need to use ShmemIndexLock. Although this is just an enum, and therefore an index in some array created in CreateLWLock (lwlock.c). I do not see how I can get access to this lock. Is it just there since obviously the whole Postgres shared memory is allocated, initialised and likely already heavily used. Do the locks map to a well know address so I can do without further in process initialisation? Or is there another routine (or two ...) that I need to call in order to make things working. Advise on how to proceed or pointers to docs in which this stuff is explained are highly appreciated. Thanks Paul. - Paul van den Bogaard [EMAIL PROTECTED] ISV-E -- ISV Engineering, Opensource Engineering group Sun Microsystems, Inc phone:+31 334 515 918 Saturnus 1 extentsion: x (70)15918 3824 ME Amersfoort mobile: +31 651 913 354 The Netherlandsfax: +31 334 515 001 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
to add some complexity to this topic :-) Please note I admit upfront I am not familiar with every parameter out there, but during my quest in finding bottleneck while stressing the back-end I find many GUC parameters with names that show they should be interesting. I read the comments, the docs (that I know of), go into the source to learn the parameters and their units. And wondering if altering a setting would do miracles in my case. Indeed how can I measure the effect of a new setting? Enhanced throughput? I learned in the past that fiddling with parameter A in a database that was not properly setup/used --and there were many of these--, had side effects in other parts of the engine. Yes, increased throughput was observed. A new hype created. In the end it turned out the parameter A was not set correctly at all. That parameter B, once set to a sane value, cured the wrong behavior, and parameter A was not optimal at all after the cure. We were just side tracked because we did not know. Incorrect knowledge was borne (parameter A setting). Throughout the years this database product has matured, many more parameter realized and much, much more instrumentation been implemented. It still is quite a challenge to understand what is happening. But proper analysis is possible indeed. The black box is much more open now. One current example: wal_writer_delay. In my team there is an advise to set this parameter to 100. However, after implementing a counter (home grown instrumentation) I now know that the background log flush routine is never called when stressing the database. Therefore I now think the best setting is 1 (its maximum) since it does not do useful work (in my context) and therefore should wake up as little times as possible. Without this instrumentation I can only guess about the usability of this parameter and spend many tests in order to get an impression of its validity to me. So overhauling the GUC parameters is one step, but adding proper instrumentation in order to really measure the impact of the new setting is necessary too. Especially when looking in the direction of auto tuning. Proper measurement is crucial to enable correct analysis. Of course I am in favor of doing this with DTrace, however not all platforms can benefit in that case :-) --Paul On 2 jun 2008, at 20:06, Josh Berkus wr Greg, Like some of the other GUC simplification ideas that show up sometimes (unifying all I/O and limiting background processes based on that total is another), this is hard to do internally. Josh's proposal has a fair amount of work involved, but the code itself doesn't need to be clever or too intrusive. Unifying all the memory settings would require being both clever and intrusive, and I doubt you'll find anybody who could pull it off who isn't already overtasked with more important improvements for the 8.4 timeframe. Plus, I'm a big fan of enable an API rather than write a feature. I think that there are people companies out there who can write better autotuning tools than I can, and I'd rather give them a place to plug those tools in than trying to write autotuning into the postmaster. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers Regards, Paul van den Bogaard - Paul van den Bogaard [EMAIL PROTECTED] ISV-E -- ISV Engineering, Opensource Engineering group Sun Microsystems, Inc phone:+31 334 515 918 Saturnus 1 extentsion: x (70)15918 3824 ME Amersfoort mobile: +31 651 913 354 The Netherlandsfax: +31 334 515 001 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] CLogControlLock
just started with 8.4 devel. Still focussing on LWlocks. With the same load (#users benchmarktool) I now see LockID 11 (CLogControlLock) to be in the top waiting list. This one was never noticable in 8.3. Did anything change with respect to this? Thanks Paul - Paul van den Bogaard [EMAIL PROTECTED] ISV-E -- ISV Engineering, Opensource Engineering group Sun Microsystems, Inc phone:+31 334 515 918 Saturnus 1 extentsion: x (70)15918 3824 ME Amersfoort mobile: +31 651 913 354 The Netherlands fax:+31 334 515 001 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] build environment: a different makefile
Peter, finally I had a chance to check it out. One word: perfect! Thanks Paul On 25-feb-2008, at 19:09, Peter Eisentraut wrote: Am Mittwoch, 6. Februar 2008 schrieb Paul van den Bogaard: I was hoping someone in the community already has a makefile that just creates object files from C-sources directly that I can use to try out the effect of in-lining to the performance of postgres. This is now the default in 8.4devel. Let us know what you find out. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings - Paul van den Bogaard [EMAIL PROTECTED] ISV-E -- ISV Engineering, Opensource Engineering group Sun Microsystems, Inc phone:+31 334 515 918 Saturnus 1 extentsion: x (70)15918 3824 ME Amersfoort mobile: +31 651 913 354 The Netherlands fax:+31 334 515 001 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reducing Transaction Start/End Contention
Just started a blog session on my findings running Postgres 8.3(beta) on a mid range Sun Fire server. Second entry is about the time lost on LWLock handling. When concurrency increases you can see the ProcArrayLock wait queue to start and explode. http://blogs.sun.com/paulvandenbogaard/entry/ leight_weight_lock_contention I will add more posts on all the other LWlock findings and the instrumentation method being used. Unfortunately a high priority project popped up I need to focus on. So please be patient. Hope to finish this in the first week of april. Thanks, Paul On 13-mrt-2008, at 16:56, Tom Lane wrote: Mark Mielke [EMAIL PROTECTED] writes: Alvaro Herrera wrote: How about this wording: Review Simon's claims to improve performance What sort of evidence is usually compelling? It seems to me that this sort of change only benefits configurations with dozens or more CPUs/cores? The main point in my mind was that that analysis was based on the code as it then stood. Florian's work to reduce ProcArrayLock contention might have invalidated some or all of the ideas. So it needs a fresh look. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers - Paul van den Bogaard [EMAIL PROTECTED] ISV-E -- ISV Engineering, Opensource Engineering group Sun Microsystems, Inc phone:+31 334 515 918 Saturnus 1 extentsion: x (70)15918 3824 ME Amersfoort mobile: +31 651 913 354 The Netherlands fax:+31 334 515 001 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reducing Transaction Start/End Contention
Just started a blog session on my findings running Postgres 8.3(beta) on a mid range Sun Fire server. Second entry is about the time lost on LWLock handling. When concurrency increases you can see the ProcArrayLock wait queue to start and explode. http://blogs.sun.com/paulvandenbogaard/entry/ leight_weight_lock_contention I will add more posts on all the other LWlock findings and the instrumentation method being used. Unfortunately a high priority project popped up I need to focus on. So please be patient. Hope to finish this in the first week of april. Thanks, Paul On 13-mrt-2008, at 16:56, Tom Lane wrote: Mark Mielke [EMAIL PROTECTED] writes: Alvaro Herrera wrote: How about this wording: Review Simon's claims to improve performance What sort of evidence is usually compelling? It seems to me that this sort of change only benefits configurations with dozens or more CPUs/cores? The main point in my mind was that that analysis was based on the code as it then stood. Florian's work to reduce ProcArrayLock contention might have invalidated some or all of the ideas. So it needs a fresh look. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers - Paul van den Bogaard [EMAIL PROTECTED] ISV-E -- ISV Engineering, Opensource Engineering group Sun Microsystems, Inc phone:+31 334 515 918 Saturnus 1 extentsion: x (70)15918 3824 ME Amersfoort mobile: +31 651 913 354 The Netherlands fax:+31 334 515 001 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposed changes to DTrace probe implementation
but putting these and other counters in context is what could be missing. Correlating a given (set of) stats with others (possible outside of the application domain) is one of the assets offered by DTrace. Besides the generic transaction begin/start/end it could also be helpful to see the number of parses,binds,executes per transaction, user, connection etc. And yes, I feel Tom is right in fearing that these things can be used in creative ways. However is this not true for most benchmarks/ results when ones objective is to show how perfect/better/whatever product/platform A behaves/is compared to B, C, etc... One benefit for generalizing a subset of the DTrace probes is the possibility of creating generic DTrace scripts that can be used for many database installations. DTrace is great, programming DTrace scripts is fun (my view, and sure I am biased as a Sun employee :-), but it is not likely to be something a dba would like to master. The availability of generic scripts does add value. BTW I wonder if we could somehow combine DTrace as a contextual tool with the counters provided through the stats interface. Any insight/ ideas? --Paul. On 27-feb-2008, at 10:28, Magnus Hagander wrote: On Tue, Feb 26, 2008 at 03:48:28PM -0600, Robert Lor wrote: Gregory Stark wrote: I think both types of probes are useful to different people. I think certain higher level probes can be really useful to DBAs. Perhaps looking at the standard database SNMP MIB counters would give us a place to start for upward facing events people want to trace for databases in general. Great idea. I found this link for public RDBMS MIB http://www.mnlab.cs.depaul.edu/cgi-bin/sbrowser.cgi? HOST=OID=RDBMS-MIB!rdbmsMIB The stats in rdbmsSrvInfoTable is quite useful, and it's one of the tables that Oracle implements in their SNMP support. http://download-east.oracle.com/docs/cd/B14099_19/manage.1012/ b16244/appdx_d_rdbms.htm Incidentally, most of that's already supported by the pg snmp provider, through the stats system. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq - Paul van den Bogaard [EMAIL PROTECTED] ISV-E -- ISV Engineering, Opensource Engineering group Sun Microsystems, Inc phone:+31 334 515 918 Saturnus 1 extentsion: x (70)15918 3824 ME Amersfoort mobile: +31 651 913 354 The Netherlands fax:+31 334 515 001 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] build environment: a different makefile
Zdenek is right. Normal inlining using -O3 or higher means inlining within the source file. I currently try to see the effect of inlining over all the sources. The -xipo flag is specific to the Sun Studio (version 12) suite. It creates large objects that now include meta data for the final linking stage. During this linking stage all this stuff is read, analyzed an the object files are changed. Later this can be expanded by compiler profiling and/or other trickery. BTW the actual binary does *not* include all that metadata and is just bigger due to all that code being inlined in those objects. This trick has been done for other applications and we find in general significant approvement. Not a guarantee though ... However my suite has a dependecy on dtrace so the initial idea I saw from Peter is not complete. Currently creating a quick and dirty build script. Just to get that ipo-ing done and tested. I'll keep you all updated on the results. If we decide it is too good to be excluded can we start thinking about an adaption of the build environement. Hope this sound fair and acceptable to all. Thanks so far for all that feedback Cheers Paul BTW I build and test on a 16 SPARC @1350MHz V890. 64 bit mode, currently 16GB of shared memory, and 7 disk arrays (RAID0). This to exclude the IO part of the equation as much as possible, so we can focus on CPU related matter: the need for faster (smarter) code pathes and/or scalability issues like ProcArrayLock contention. On 7-feb-2008, at 11:08, Zdenek Kotala wrote: Peter Eisentraut napsal(a): Paul van den Bogaard wrote: The SunStudio compiler we are using fortunately has an option for this. Unfortunately there are restrictions. One restriction I face is its inability to deal with ld -rs. These are used in the build environment to create all the SUBSYS.o object files. I was hoping someone in the community already has a makefile that just creates object files from C-sources directly that I can use to try out the effect of in-lining to the performance of postgres. I don't know if anyone has a makefile for it, but the following seems to work for me: pgsql/src/backend$ cc -O2 -Wall -Wmissing-prototypes -Wpointer- arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno- strict-aliasing -g -L../../src/port -Wl,-rpath,'/home/peter/devel/ pg83/pg-install/lib' -Wl,-E $(find -name *.o | grep -v SUBSYS | grep -v conversion_procs) ../../src/timezone/SUBSYS.o ../../src/ port/libpgport_srv.a -lxslt -lxml2 -lpam -lssl -lcrypto - lgssapi_krb5 -lcrypt -ldl -lm -lldap -o postgres If you find that the optimizations you are hoping for are useful, I'm sure we could put an option of that sort somewhere in the makefiles. Peter, Suns studio performs inline optimization on -xO3 level. Optimization levels are different from GCC. Maximal level is -xO5. I think Paul plays with xipo flag which requires at least -xO4. See http://docs.sun.com/app/docs/doc/819-5265/bjapp?a=view Zdenek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster - Paul van den Bogaard [EMAIL PROTECTED] ISV-E -- ISV Engineering, Opensource Engineering group Sun Microsystems, Inc phone:+31 334 515 918 Saturnus 1 extentsion: x (70)15918 3824 ME Amersfoort mobile: +31 651 913 354 The Netherlands fax:+31 334 515 001 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] build environment: a different makefile
Currently trying to enhance the way we can make binaries that run on Solaris. One thing I found was a scalability bottleneck in the use of the ProcArrayLock. (this one has also been reported by a couple of my colleagues). One big user of this lock is GetSnapshotData. After it has taken this lock it does its work and releases it again. While it is holding the lock it is not doing any system calls and the lock holding process is barely preempted. The only way to make this code faster is making the code use less CPU cycles to achieve its goal. One way is having the compiler do some strong code in-lining. The SunStudio compiler we are using fortunately has an option for this. Unfortunately there are restrictions. One restriction I face is its inability to deal with ld -rs. These are used in the build environment to create all the SUBSYS.o object files. I was hoping someone in the community already has a makefile that just creates object files from C-sources directly that I can use to try out the effect of in-lining to the performance of postgres. Any other hints to achieve my goal are welcome too, of-course. Please note that in-lining is done in both the compiler and the linker. Thanks, Paul - Paul van den Bogaard [EMAIL PROTECTED] ISV-E -- ISV Engineering, Opensource Engineering group Sun Microsystems, Inc phone:+31 334 515 918 Saturnus 1 extentsion: x (70)15918 3824 ME Amersfoort mobile: +31 651 913 354 The Netherlands fax:+31 334 515 001 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
