Webpages take ages to load..!! (MySQL + PHP)

WhisperiN · 11-03-2009, 09:11 AM

Hello Fellows,
How you doing?

I have built a small VPS, with 256MB of Ram.
I managed to install every thing I need.. as below:

OS: CentOS 5.3

Code:

[root@mail /]# uname -srv
Linux 2.6.24-24-xen #1 SMP Tue Aug 18 18:15:39 UTC 2009

Web Server: Apache

Code:

[root@mail /]# httpd -v
Server version: Apache/2.2.14 (Unix)
Server built:   Oct 14 2009 13:20:11

Database Server: MySQL

Code:

[root@mail /]# mysql -V
mysql  Ver 14.12 Distrib 5.0.86, for redhat-linux-gnu (x86_64) using readline 5.1

PHP

Code:

[root@mail /]# php -v
PHP 5.2.11 (cli) (built: Sep 21 2009 14:52:42)
Copyright (c) 1997-2009 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2009 Zend Technologies

My /etc/my.cnf:

Code:

[client]

port = 3306
socket = /var/lib/mysql/mysql.sock

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

#skip-networking

skip-federated

log-bin=mysql-bin


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Tables Type used into MySQL Databases: MyISAM

I'm so sure, this problem is related to something with MySQL.
Because when I'm surfing the website it loads just quick as long as there is no call to MySQL.
Whenever there is a MySQL query.. it delays..

I tried to tweak MySQL as I found some tutorials about that.. but nothing helped..!! so I went back to default ( my-large.cnf ).

Any help, is more than appreciated..

kainlite · 11-03-2009, 09:18 AM

Hi WhisperiN, what about cpu load, ram usage, etc ? maybe the problem are the physical resources...

WhisperiN · 11-03-2009, 09:35 AM

Humm..

I'm not sure, but I don't think so, simply because never got an error message about that..

Code:

[root@mail etc]# free -m
             total       used       free     shared    buffers     cached
Mem:           256        219         37          0          6         47
-/+ buffers/cache:        165         90
Swap:          511         24        487

Code:

[root@mail etc]# uptime
 15:30:17 up 4 days, 21:45,  1 user,  load average: 0.02, 0.19, 0.21

kschmitt · 11-03-2009, 10:10 AM

Quote:

Originally Posted by WhisperiN

Humm..

I'm not sure, but I don't think so, simply because never got an error message about that..

Code:

[root@mail etc]# free -m
             total       used       free     shared    buffers     cached
Mem:           256        219         37          0          6         47
-/+ buffers/cache:        165         90
Swap:          511         24        487

Code:

[root@mail etc]# uptime
 15:30:17 up 4 days, 21:45,  1 user,  load average: 0.02, 0.19, 0.21

Look in your log files, I'm going to guess that it's generating an SSL key for the first time, and running out of entropy in the entropy pool.

This is not uncommon for Virtual Machines, I've had it happen several times. Just run "find /" a few times, it should generate some entropy and make it able to create the key. There are other, possibly better ways of generating entropy, but "find" is quick

WhisperiN · 11-03-2009, 10:44 AM

Quote:

Originally Posted by kschmitt

Look in your log files, I'm going to guess that it's generating an SSL key for the first time, and running out of entropy in the entropy pool.

This is not uncommon for Virtual Machines, I've had it happen several times. Just run "find /" a few times, it should generate some entropy and make it able to create the key. There are other, possibly better ways of generating entropy, but "find" is quick

Hello kschmitt,
First, I'd like to thank you a lot for the info..

But, would you be so kind to give me few words explaining what's meant by entropy, or entropy pool?

I'm kinda still learning about Linux servers.. sill collecting my infoes ;-)

Also, is there a specific log file that you want me to look into?
MySQL Log file for example?

Thanks a lot..

kschmitt · 11-03-2009, 03:03 PM

Quote:

Originally Posted by WhisperiN

Hello kschmitt,
First, I'd like to thank you a lot for the info..

But, would you be so kind to give me few words explaining what's meant by entropy, or entropy pool?

I'm kinda still learning about Linux servers.. sill collecting my infoes ;-)

Also, is there a specific log file that you want me to look into?
MySQL Log file for example?

Thanks a lot..

OK.

The kernel has an entropy pool: a place where it collects random noise from the pci cards, the buses, keyboard/mouse input, and (if you're lucky) a hardware random number generator. Being such a clean idealized system, VMs tend to be slow to build up a sizable entropy pool, and they don't have hardware random number generators, because they aren't hardware.
(You can find info on your system's entropy pool in /proc/sys/kernel/random/ look at what's in those files & look up the directory on google)

Linux has two random devices /dev/random and /dev/urandom.

/dev/random uses the entropy pool to create real random numbers, and is considered OK for cryptographic uses*. If the entropy pool runs dry, it will hang until the pool fills up enough to generate more real random numbers.

If there is entropy in the pool, /dev/urandom works the same as /dev/random. The difference is when the pool runs dry, urandom will give you psudo random numbers, which are not sufficient for cryptographic use, but are OK for many other uses.

When an SSL key is generated, it uses random, not urandom. On a fresh VM, there will be little to no entropy, so generating the key will hang. You have to do things on the system for the entropy pool to fill up. There are probably a dozen other things than generating SSL keys that will hang without enough entropy, but SSL key generation is the most common.

Good luck!

* Crypto & CS folks would probably argue this point, but for general use, ya know?

sleddog · 11-03-2009, 06:24 PM

Well this:

Code:

key_buffer = 256M

Could be problematic on a VPS with 256 MB of memory

Try:

Code:

key_buffer = 16M

These settings are also too high (IMO), given your overall RAM:

Code:

myisam_sort_buffer_size = 64M
query_cache_size= 16M

Instead, try:

Code:

myisam_sort_buffer_size = 8M
query_cache_size= 2M

WhisperiN · 11-04-2009, 03:10 AM

Alright fellows..

I'll give a try, and be back with results.. :-)

Thanks ..

WhisperiN · 11-08-2009, 02:27 PM

I'm back again..

Well, now.. after few more looking here and there..
I ended using the configs below:

Code:

[mysqld]
key_buffer = 16M
max_allowed_packet = 1M
table_cache= 64
sort_buffer_size = 512K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
query_cache_size= 8M
skip-locking
max_connections = 150
safe-show-database
net_buffer_length = 8K
thread_cache_size = 8
thread_concurrency = 8

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

Well, to be honest, it's much much better..
But there is still something..

When I open a page for the first time, it takes some time..
next time I open, or let's say if I did a hard refresh.. it takes a second..

I guess, there is still something to tweak, any guesses?

Also, for the memory.. is this normal:

Code:

[root@mail /]# free -m
             total       used       free     shared    buffers     cached
Mem:           256        252          3          0          1         69
-/+ buffers/cache:        181         74
Swap:          511        224        287