Hey,
is it possible to somehow 'limit' or tune usage of swapfile on
apache/mysql, apart from disabling swap completely?
I've got quite loaded web+sql server and frome time to time swap usage
goes up to 1gb and in the meantime server performance is very crippled.
After few hours swap frees itself and performance is back to normal. Any
clues?
--
Miha

Miha Verlic <fluke@no-spam.krneki.org> wrote:
> is it possible to somehow 'limit' or tune usage of swapfile on
> apache/mysql, apart from disabling swap completely?

Limit the number of users that can use _at the same time_ apache
cq mysql. The system is running out of memory because of too many
users (and thus too many "data segments" for all the threads cq
processes of apache cq mysql.

For apache, this is in the /etc/apache/httpd.conf file:
MaxClients 150 (this is the Slackware default, you probably want
this value lower).
I wouldn't know for mysql, we never ever have more then 2 instances
running (and mostly on different machines too) - our webserver
doesn't use sql, just the system managers do (and we only have 2 of
those).
--
************************************************** ******************
** Eef Hartman, Delft University of Technology, dept. EWI/TW **
** e-mail: E.J.M.Hartman@math.tudelft.nl, fax: +31-15-278 7295 **
** snail-mail: P.O. Box 5031, 2600 GA Delft, The Netherlands **
************************************************** ******************

Eef Hartman wrote:
> Limit the number of users that can use _at the same time_ apache
> cq mysql. The system is running out of memory because of too many
> users (and thus too many "data segments" for all the threads cq
> processes of apache cq mysql.

Limit is set to 1024 apache MaxClients and mysql max connections 700.
If I set limits any lower I'm getting "Too many connections" errors.

I do not want to (or better yet cannot) limit this though - current
configuration is working OK for a few hours or even days and then
suddenly mysql starts using 512-1024mb of swap and server performance is
unbearable untill apache&mysql are restarted...

--
Miha

Miha Verlic wrote:
> Hey,
> is it possible to somehow 'limit' or tune usage of swapfile on
> apache/mysql, apart from disabling swap completely?
> I've got quite loaded web+sql server and frome time to time swap usage
> goes up to 1gb and in the meantime server performance is very crippled.
> After few hours swap frees itself and performance is back to normal. Any
> clues?

More memory. Do not kill the swap. It's there for when memory gets low.

--
C

Chris Elvidge wrote:
> More memory. Do not kill the swap. It's there for when memory gets low.

Server already has 4gb of memory, and only 1 big database ~1,6gb with
proper indexes created...
--
Miha

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Miha Verlic wrote:
> Server already has 4gb of memory, and only 1 big database ~1,6gb with
> proper indexes created...

You'd probably be better taking this to a mysql forum, chances are its a
stray transaction or other error in the app causing problems. Either way
it sounds like a DB tuning issue which messing around with the swap file
won't fix.

Blumf
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDu+ZVMid3IcxolsoRAi2QAJ9l5gDX7UFOmDi/5Fk0s2avBbU0SQCdE9Ed
yH3kF2oV/63YKdm8syrOy1s=
=SNCz
-----END PGP SIGNATURE-----

On Wed, 04 Jan 2006 11:45:22 +0100, Miha Verlic <fluke@no-spam.krneki.org> wrote:

>Eef Hartman wrote:
>> Limit the number of users that can use _at the same time_ apache
>> cq mysql. The system is running out of memory because of too many
>> users (and thus too many "data segments" for all the threads cq
>> processes of apache cq mysql.
>
>Limit is set to 1024 apache MaxClients and mysql max connections 700.
>If I set limits any lower I'm getting "Too many connections" errors.

You could limit incoming connections, better than falling over?
>
>I do not want to (or better yet cannot) limit this though - current
>configuration is working OK for a few hours or even days and then
>suddenly mysql starts using 512-1024mb of swap and server performance is
>unbearable untill apache&mysql are restarted...

Check if your database is creating very large temp tables due to
poor SQL query design. Another option is to look at denormalising
some of your tables for query response speed, reduce number of
tables needed to satisfy a query. (Design by access method, rather
than a formal fully normalised database design).

A few years ago it used to be that mysql fell over on overload where
postgresql just slowed down, but kept going. Dunno if that still
applies.

Grant.

Grant wrote:
> Check if your database is creating very large temp tables due to
> poor SQL query design. Another option is to look at denormalising
> some of your tables for query response speed, reduce number of
> tables needed to satisfy a query. (Design by access method, rather
> than a formal fully normalised database design).

This is of course on TODO list and I'm in touch with programmers, but
until they fix this "poor design" it's up to me :/

> A few years ago it used to be that mysql fell over on overload where
> postgresql just slowed down, but kept going. Dunno if that still
> applies.

Actually lowering key_buffer_size does exactly that apparently... it
seems slow on moments, but at least it works... better...
I guess I was a bit too generous with giving resources to mysql...
--
Miha