Posted by Erwin Moller on 09/27/47 11:36

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

[Back to original message]