postgres performance improvements

Although you say your machine is "an (very) under-spec XP machine", you do not say what the specs actually are. I have an 8-yr old XP box, with a 2.4GHz Athlon and 2GB RAM and it does just fine.

As for the number of processes used by postgresql, see if this
https://wiki.postgresql.org/wiki/Number_Of_Database_Connections
helps at all.

Try googling
reduce number of postgres processes
and read through some of the articles.
Rather than spawn threads to handle additional connections, postgres apparently spawns processes.

When I raised this issue with the other major tracker I was told that they already do everything that they can to reduce the number of postgres processes needed. If they do, I'm sure PT does everything that they can as well.

You could also google
postgres performance tuning
and try some of the settings to see what works best.

[Mod note: I messed up dividing these posts out of the other thread.
The text in the quote above is from the post I missed. I edited it into this thread to keep it in sequence]

Thank you for the link Vorpal, it is in the right direction. From there I'll probably be able to fine what I'm looking for.

I didn't specify my machine exactly because it is totally irrelevant to my question.

Sorry about being impolite, but I have a hard time with vogons. If you read this whole thread you'll see why. It started out with me recommending PT4 because of its sound implementation. I dropped a question as an aside whose answer should be known to anyone working with PostgreSQL, and, once again, I made a nice note on PT4, mentioning that it runs flawlessly on old hardware. Now the vogon's instincts took over because it saw the opportunity to mess with people for its own pleasure referring to formalities, (whether they actually apply or not, that's how vogons operate - and get away with it).

You should seriously restrict these guys rights to do self-promotion, and they should absolutely not be able to point finger in a thread that doesn't even fall in the pure PT4 category. This thread lies in "Poker Tools" period. PostgreSQL counts as a poker tool, since most tracking software use it. I'm perfectly allowed to ask questions about PostgreSQL here, and I'm perfectly allowed to mention (and not mention) the machine I'm using. I mistook user PokerTracker for a real person that might know the answer, that's my mistake. He/she would do a fine job in any customer complaints department in any company that has enough customers as it is.

Originally posted by YohanN7
Sorry about being impolite, but I have a hard time with vogons.

NP.

+ 1 on the vogons -- except for the poetry, of course.

I tried all sorts of tweaks to make postgres less voracious, but I ended up reversing every tweak I tried.

Any improvement I tried turned out to make things worse, or changed nothing noticeable.

Peace...
--VS

I personally don't think their poetry is that bad. You should here me sing!

To explain more clearly what the issue is, something that PokerTracker didn't understand. He/she started a rant instead. ("I'm afraid your PC is the issue... We are forbidden to ...". Laughing out loud. Not pissed off any longer, just amazed.)

When a db server starts up, it usually spawns a number of agents, processes or threads, to stand by for incoming connection requests. It appears that in the case of PostgreSQL, the default is something like 10. For each new connection, grabbing one of the idle agents, a new one is started. This behavior is probably (should be) dynamic, so that if you have 8000 connections on average, and, say, 10 incoming connection requests per second, then a few more idle agents should stand by.

When you are in a single db client environment, it's obvious that you don't need to have 10 or more connection-less agents standing by, having zero or one is optimal. I'm pretty sure that there is a parameter for the configuration file where you can put a cap on the number of idle agents. For IBM DB2 you would simply write max_idle_agents = N, where N is your preference.

Pretty independent and PT4 and of my PC as you can see.

Originally posted by YohanN7
When a db server starts up, it usually spawns a number of agents, processes or threads, to stand by for incoming connection requests. It appears that in the case of PostgreSQL, the default is something like 10. For each new connection, grabbing one of the idle agents, a new one is started. This behavior is probably (should be) dynamic, so that if you have 8000 connections on average, and, say, 10 incoming connection requests per second, then a few more idle agents should stand by.

When you are in a single db client environment, it's obvious that you don't need to have 10 or more connection-less agents standing by, having zero or one is optimal. I'm pretty sure that there is a parameter for the configuration file where you can put a cap on the number of idle agents. For IBM DB2 you would simply write max_idle_agents = N, where N is your preference.

Are you sure postgres works that way?

If I close all clients, and restart postgres, there are a small number (6) of processes.
If I open pgadmin and connect it, another process is added.
If I then open a database, yet another, and still another if I open a query window.
As I close the additional windows connections, the processes are closed instantly, so that is good.

Do you have pslist? It is a command-line tool for listing processes.
Really handy for stuff like this. It used to be freely downloadable from MS.

I also found the following where were interesting...
[Warning: Lots of material here]
http://wiki.postgresql.org/wiki/Performance_Optimization
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

This is the man page for 8.4 server configuration.
http://www.postgresql.org/docs/8.4/static/runtime-config.html
Go for it. Sections 18.3 and 18.10 seem like they might have some bearing on this case.

I am assuming that you are running 8.4.
Versions 9 and above are (or were last time I checked) not recommended for single-client use.

I played with much of this stuff two or three years ago, and like I said, I'm running totally vanilla now.

One of the above links talks about overuse of VACUUM FULL so if that applies to you, there is a way to recover.

Best of luck,
--VS

It works (and is supposed to work) the way that both you and I describe it. I have 13 postgres.exe (with nothing going on) and you have 6 starting fresh.

I can't find anything really (following the links) to tweak this (the number 13 in my case) down to a minimum.

I want to emphasize that this is not much of a problem, it's mostly just annoying me :).

What version of postgres?

The version is usually part of the service name.
13 seems way huge.
Are any other apps using postgres?

I found this after my last post:
http://wiki.postgresql.org/wiki/FAQ#How_does_PostgreSQL_use_CPU_resources.3F
The next two topics immediately below this are useful as well.

The whole FAQ would be interesting to read...
--VS

Here is a random thought...
You don't by any chance have two instances of postgres service running do you?

At one time, I had both 8.3 and 8.4 running at the same time.

If you run
services.msc
from a command line or the run box you'll find a list of all the services other than device drivers on your system. (Yes, there is a mouse way to do it, but I never remember it)

Look for TWO instances of postgres...
If there are, right-click one of them (lowest number if there are numbers), choose Stop.
When it stops, try running your client -- PT4 I think you said.
If it works, go back to services.msc, right-click the stopped service, choose Properties and set startup type to "disabled"

If not, too bad, but it was a dang fine idea.

Cheers,
--VS

You are clearly a genius!

I have both 8.4 and 9.2 running. Both are started by PT4, but only the 8.4 is used (it has my "active" PT4 db). I vaguely recall that PT4 + 9.2 doesn't even work with XP (while PostgreSQL 9.2 in isolation is ok with XP).

Thanks!

(But I'm still wondering out of curiosity, how do I get rid of 4-5 of the remaining 6 idle agents? But this can surely wait til a rainy day :))

Originally posted by YohanN7
You are clearly a genius!

Oh, how I wish...
However I have learned to listen to my random thoughts.

It is amazing how many times something "popped up" like that when I was totally thinking of something else.

Glad it worked ♠_biggrin:

(But I'm still wondering out of curiosity, how do I get rid of 4-5 of the remaining 6 idle agents? But this can surely wait til a rainy day :))

I don't think that they are idle. According to one of the FAQs, there are internal processes that get their own process.

In MS SQLSever, for example, if you look at the management tools, you can see several processes, but there is only one OS-level process. If you examine that process w/ process_explorer (a 3rd party tool unfortunately) you can see that there are several hundred threads.

So for postgres, I would imagine it looks something like:
♦  master process that governs service details
♦  master/system database
♦  "template" database
♦  Task scheduler for things like auto-vacuum
♦  Client manager / Error handler / logger / transaction manager
♦  Language Handler (you can install procedural languages)

Well after I typed all that I found this:
http://www.postgresql.org/docs/8.4/static/connect-estab.html

The pertinent paragraph:

PostgreSQL is implemented using a simple "process per user" client/server model. In this model there is one client process connected to exactly one server process. As we do not know ahead of time how many connections will be made, we have to use a master process that spawns a new server process every time a connection is requested. This master process is called postgres and listens at a specified TCP/IP port for incoming connections. Whenever a request for a connection is detected the postgres process spawns a new server process. The server tasks communicate with each other using semaphores and shared memory to ensure data integrity throughout concurrent data access.

I think I remember you mentioned that you did C programming, so I think you'll understand that.

So I was wrong in my guess, but not TOO far off.

All-in-all, I'm glad you raised the question, because I got a lot further in my understanding of postgres that I did the last time I went digging.

The bit about sharing memory is interesting. What it means is that the total memory occupied by ALL processes is probably less than the total reported by Task Manager.

I don't think that I've ever written a program that shared memory, but everything I ever wrote was intended to work standalone.

Cheers,
--VS

Wow, thanks for that answer. You really dug it up!

The quote says, more or less, there is no idle pool. (It's another thing that they should have a pool. I've implemented something similar, with one process waiting for incoming connects, but also a small pool of "ready to go" server processes. With that scheme (let an idle process take the connection and then start a new idle process), the response times were just amazing (practically zero). I believe that still today, there is an overhead when starting a process. (Threads are lighter of course.))

Shared memory is extremely fast and efficient, the only problem is that when too many people mess around with such code, there is a huge risk that somebody screws up. When it comes to to poker tracking software, I'm sure CPU usage could be reduced with a factor of 20 - at least. Memory usage could surely be reduced too. (4Gb, w t f, think about it, this is a lot, plenty of copies of the bible fits in., Now I feel like showing these guys how to really do the stuff ♦)

Yeah, I've been cheating around with C, but mostly C++ a little (a LOT in fact :)). Nowadays I still code a little, mostly as a hobby. Mostly math stuff, but also poker stuff, like ICM. I quit on the ICM code, not because it wasn't going anywhere, but what's out there is pretty good (like Hold'em resources, nice stuff, Edit: And oh, here is what truly good customer support looks like: Hold'em Resources support thread) and probably hard to beat without working ones ass off X(.

Thanks again!

I saw mention of a C++ poker library.
I did not bookmark it because I was pretty certain that I would just be mimicking far, far better stuff if I ever tried to use it.

Cheers,
--VS