Thread: Process balancing on smp db server/apache web server

Friends,
    I have been thinking about my smp db server and how it interacts with my
web server.  I'm using mod_perl on Apache, which uses Apache::DBI to connect
to the db server via a private network segment.  It occurs to me that since
the web server is connecting early (on startup), when there is probably no
load on the db server, the cpu that each backend is assigned to will be
largely random, or, if there is a large syslogd operation or something right
at that time, it might even put the majority of backends on the same
processor.
    When someone hits the web site it seems to me that there would be a greater
than 50% chance that any two large queries from the web server would end up
being run on the same processor.  Similarly, if I start a large processing
script that uses the db, since the web associated backends are already
assigned to a processor, there's a good (~50%?) chance that any big queries
that come in through the web will be on the loaded cpu.
    Does this make sense to anyone?  If this is true, are there any suggestions
about how I can keep my persistent connections from Apache, while getting
the db server to balance the load more efficiently?
Thanks,
Peter Darley

On Thu, May 23, 2002 at 06:56:05AM -0700, Peter Darley wrote:
> Friends,
>     Does this make sense to anyone?  If this is true, are there any suggestions
> about how I can keep my persistent connections from Apache, while getting
> the db server to balance the load more efficiently?

I makes sense to me although until your email I didn't
know that's how postgres (or is it kernel) will assign jobs.
I was under the impression that if the loads get higher
kernel will automatically distributive "active" processes
to multiple cpus.  Isn't that the case?  I have the
same set up (mod_perl, apache dbi with postgres backend)
as you do and I am in the process of getting an smp box to run postgres

If your idea is the correct one I can think of artificially
loading postgres server at startup.....

Also bear in mind that modperl processes at front end got
periodically killed, new childs periodically born,
and new connections periodically re-established.  (Assuming
you have reasonable apache config file) So
over the course of web server life time things should
work out.  Hmm... I like this scenerio and feel
that things aren't as bad as I thought when I first
started to type this post.

Peter Darley sez:
} Friends,
}     I have been thinking about my smp db server and how it interacts
} with my web server.  I'm using mod_perl on Apache, which uses Apache::DBI
} to connect to the db server via a private network segment.  It occurs to
} me that since the web server is connecting early (on startup), when there
} is probably no load on the db server, the cpu that each backend is
} assigned to will be largely random, or, if there is a large syslogd
} operation or something right at that time, it might even put the majority
} of backends on the same processor.
}     When someone hits the web site it seems to me that there would be a
} greater than 50% chance that any two large queries from the web server
} would end up being run on the same processor.  Similarly, if I start a
} large processing script that uses the db, since the web associated
} backends are already assigned to a processor, there's a good (~50%?)
} chance that any big queries that come in through the web will be on the
} loaded cpu.
}     Does this make sense to anyone?  If this is true, are there any
} suggestions about how I can keep my persistent connections from Apache,
} while getting the db server to balance the load more efficiently?

In general, user processes don't have the option of locking themselves to
one CPU or another. Just because a process was started on one CPU does not
mean it will always be executed on it. Certain parts of the OS may demand
that they are only executed on a particular CPU for synchronization
purposes, but user-level processes use synchronization methods which
interact through memory with the CPU-locked OS code.

The short answer is that the OS does load balancing dynamically, and this
has nothing to do with on which CPU a process was spawned.

} Thanks,
} Peter Darley
--Greg

"Peter Darley" <pdarley@kinesis-cem.com> writes:

> Friends,
>     I have been thinking about my smp db server and how it interacts with my
> web server.  I'm using mod_perl on Apache, which uses Apache::DBI to connect
> to the db server via a private network segment.  It occurs to me that since
> the web server is connecting early (on startup), when there is probably no
> load on the db server, the cpu that each backend is assigned to will be
> largely random, or, if there is a large syslogd operation or something right
> at that time, it might even put the majority of backends on the same
> processor.

Ummm....  All Unices that I know of do dynamic migration of tasks
between processors as needed.  When a process (or thread) is ready to
run, the OS will try to pick a free CPU for it.  So this shouldn't be
an issue.  You have to do something special to lock processes to one
processor--commercial systems like Solaris and Irix have APIs to do
this, and there are patches for Linux I believe.  But if you just want
to use the CPUs most efficiently, the default schedulaer behavior is
usually what you want.

Ultimately, most databases loads are I/O bound anyway, so it'd be more
worth your while to worry about your disk subsystem.  ;)

-Doug