Server load too high

I am running the tagboard service on my server. Few months ago I bought
the new dedicated server with Dual Xeon 3.2 GHz and 2GB Ram.
The board is written in PHP with Mysql. Not long ago I updated the
version PHP to 5. and the problem started.

The load on the server skyrocked between 39-52. And I seriously have no
idea what to do about it.

This is remotedly hosted servise and there are number of connections
from other websites to my own. However I don't have any other working
websites on this server. Each client is connected to the database with
mysql_connect. But I do not know why there is a problem with server
load and why the database takes so much from the server resources?
Below is a sample of the Current CPU usage. As you can see the database
connection takes a lot of resources.

Pid Owner Priority Cpu % Mem % Command
7128 mysql 0 1.3 2.0 /usr/sbin/mysqld
--basedir=/ --datadir=/var/lib/mysql --user=mysql
--pid-file=/var/lib/mysql/host.yobug.com.pid --skip-locking
7112 nobody 0 0.1 0.8
/usr/local/apache/bin/httpd -DSSL

I also checked CPU/Memory/MySQL Usage and last few days I this since I
installed php version 5 the CPU % usage is almost 99-123%! The website
can't work like this.

I thought about couple of solutions but if you can help please write a
constructive response.

1. I was thinking changing the structure of the database. Since all
the posts are in one single table I thought to change it to separate
tables for each customer. Than the process of posting or writing to the
database table would be much faster since the tables would not be
locked for others and the processes don't have to wait.

2. reinstalling PHP to older version php4.

Is there any way that the problem can be solved? if Yes let me know.

ewunia@earthlink.net wrote:

> I am running the tagboard service on my server. Few months ago I bought
> the new dedicated server with Dual Xeon 3.2 GHz and 2GB Ram.
> The board is written in PHP with Mysql. Not long ago I updated the
> version PHP to 5. and the problem started.
>
> The load on the server skyrocked between 39-52. And I seriously have no
> idea what to do about it.
>
> This is remotedly hosted servise and there are number of connections
> from other websites to my own. However I don't have any other working
> websites on this server. Each client is connected to the database with
> mysql_connect. But I do not know why there is a problem with server
> load and why the database takes so much from the server resources?
> Below is a sample of the Current CPU usage. As you can see the database
> connection takes a lot of resources.
>
> Pid Owner Priority Cpu % Mem % Command
> 7128 mysql 0 1.3 2.0 /usr/sbin/mysqld
> --basedir=/ --datadir=/var/lib/mysql --user=mysql
> --pid-file=/var/lib/mysql/host.yobug.com.pid --skip-locking
> 7112 nobody 0 0.1 0.8
> /usr/local/apache/bin/httpd -DSSL

Hi,

That looks like mySQL is doing all the numbercrunching here indeed.


>
> I also checked CPU/Memory/MySQL Usage and last few days I this since I
> installed php version 5 the CPU % usage is almost 99-123%! The website
> can't work like this.

I am suprised that php5 is the reason for this.
I suspect you better have a look at the SQL and database.
PHP5 has a better performance than php4. (So they claim)

>
> I thought about couple of solutions but if you can help please write a
> constructive response.
>
> 1. I was thinking changing the structure of the database. Since all
> the posts are in one single table I thought to change it to separate
> tables for each customer. Than the process of posting or writing to the
> database table would be much faster since the tables would not be
> locked for others and the processes don't have to wait.

Do you expect that the WHOLE table is locked?
I would think that a rowlock would be enough... But then again, I do not
know the SQL and the databasedesign.

But making a seperate table for each customer sound crazy.

I think you better add a few smart indexes on the table.

eg: If all your queries use a datecolumn in the WHERE-clause, index that
column.

You might want to do some profiling first, to find out what is taking up all
the time.

>
> 2. reinstalling PHP to older version php4.

I don't think PHP is the reason for the delay, unless you somehow also
installed inferiour mySQL drivers. But my knowledge of mySQL is close to
zero, so check this out yourself.

>
> Is there any way that the problem can be solved? if Yes let me know.

1) Look at your queries, can you add some indexes?
2) Is your mySQL driver OK? (ask somebody else than me)

If you decide to switch back to PHP4, please tell us what you find.
I would be surprised if PHP5 is the one giving troubles here and would like
to know. Ok?

Good luck.

Regards,
Erwin Moller

If you do switch back to php 4, before you do just check and see what
table types you are using. It is myisam or innodb?

If it is myisam and there are lots of writes occurring on the table it
will preform a table lock on each write, you maybe better off switching
to innodb and row locking.

Also switch to using mysql_pconnect.

ewunia@earthlink.net wrote:
> I am running the tagboard service on my server. Few months ago I bought
> the new dedicated server with Dual Xeon 3.2 GHz and 2GB Ram.
> The board is written in PHP with Mysql. Not long ago I updated the
> version PHP to 5. and the problem started.
>
> The load on the server skyrocked between 39-52. And I seriously have no
> idea what to do about it.
>
[snip]

Well, since it looks like MySQL is eating more of the CPU, it could be
part of the equation. I'm not entirely certain of all of the
differences between the PHP 4.x and PHP 5.x, but it is entirely possible
by that upgrading, you may have found a design error in the application
or database arrangement.

My inclination would be to see what version of MySQL you're using, check
that against the MySQL client library that PHP5 is using, and see if
you're using the mysql or mysqli extensions for PHP. If you're using
mysql.so and you're using a newer version of MySQL, consider switching
to the mysqli.so extension.

Also, as stated earlier by someone else, check to see what the queries
are doing.

Your load average may have jumped, also, if the way processes are
spawned changed between the releases, but I'm not sure if that's
something to do with PHP at all, so that could be totally wrong.

In any case, I've been using PHP5 for a while now, and I've not
encountered any bugs of this sort caused by valid code... although one
thing that I haven't really done yet is work with PHP4 object code on
PHP5, since I find PHP5 objects better. Do you use PHP4 objects? Maybe
you can try to isolate the behavior better somewhere, if you have the
chance to play in a test environment and try to isolate the cause.

Later,
Mike