Hlo,

Here is the result of free -m

#free -m
total used free shared buffers cached
Mem: 4017 3986 31 0 72 2375
-/+ buffers/cache: 1538 2478
Swap: 2000 0 1999

And here are the values of httpd params
MaxClients : 150
TimeOut : 300
MaxSpareServers : 20
MinSpareServers : 5
KeepAlive : off
KeepAliveTimeout : 15

I ll take a look into the stat.php meanwhile.

I have another question. Around 30% of our queries are taking above 5 secs to execute. The no of row scans that these queries have to do is around 5 lacs. And they are using index. They are using index with low cardinality, say 4 or 5. So is it ideal for these queries to take such a long time?? I really dont know....

From your stats, it seemed like you do little or no image serving at your httpd server and the same users aren't supposed to make simulataneous requests to the server, so only 1-2 changes are required.

Make the following changes:
MaxClients : 250
TimeOut : 30
MaxSpareServers : 85
MinSpareServers : 25
KeepAlive : off
KeepAliveTimeout : 15

Since the bottleneck is I/O, you might also want to install op-code cache software such as APC cache for PHP and assign it memory as much as 96mb (shm_size).

Since it's a busy server, that's normal. There's a little help you can get by installing cache software, decreasing disk operations such as disabling apache logs, disabling mysql logs etc. The disks too busy with the operations. So I will recommend an upgrade to SATA II (10,000 rpm with much on-board cache) or SCSI.

For Mysql

You aren't mirroring/clustering/replication your mysql right, so i think you can disable it's logging. Comment log_bin to look as following:

#log_bin

change the values of following variables as:

key_buffer = 16M
innodb_log_file_size = 375M
innodb_buffer_pool_size = 1800M

You can decrease the size of key_buffer as it's useful only for MyISAM.

-------
Do the following if the above ones do not help:
--------

About other mysql params, if you do a lot of table joins (8-10+) in your queries, you might want to increase the value of join_buffer_size to something like 3mb or even 4mb.

If you have huge tables/indexes and have to do SORTS (ORDER by and GROUP BY), then increase the size of sort_buffer_size, it should be enough to hold most of the data. A size of upto 4-5mb might be needed depending on size of indexes and how you perform sorts. The sorted data should fit in this memory buffer or mysql will use disk for writing.

Another variable you can set is 'read_rnd_buffer_size = 5M'.. It should improve the performance of ORDER by a lot.

These all values affect your memory usage, so use them wisely (don't set too high, but make them reasonable enough.. do not lower them than your current values though) and keep a watch on "free -m" (memory in buffers as explained in my linux overloading explained article) after you've made changes.

Good Luck!

This post was last modified: 09-07-2006 12:58 PM by Pacifier.

You can see the below link for the stat.php that you provided

http://durga.tallysolutions.com/test/stat.php

Could you please tell me how would changing the httpd paramaters as u suggested help?
Also tell me how would the cache software that u recomended help?

I require these answers bcoz i would be questioned to the point once i make the recommendation to these changes. And I should have a strong answer :o). You have been very hlpful to me with ur suggestion. Thanks a lot for tat.

I also require one more suggestion. Should we be seperating our application server and DB server. Currently all are running in the same server.

Thanks

Thanks.

I suggested increase the value of MaxClients to increase the capacity of HTTP serving. When you reach the MaxClient limit, they're put on WAIT rather than 'blocked out'. A quick search on google about MaxClient should be helpful. Other changes were suggested looking at the Apache stats you provided. I recommend MaxSpareServers to 85 because your Apache stats showed it was serving 150 at a point. Spawning new apache processes uses more resources than having using already running apache processes. Again a quick google search or reading about it at official apache site should give you enough points to back your suggestion.

It caches compiled version of PHP scripts (though that doesn't means MySQL data or I/O data is cached), and kills the need of compiling the php code on each page load. Sure it uses some memory but it speeds up PHP serving (reducing TCP/IP overhead as well by faster serving) and decreases load since the CPU isn't required to compile the PHP code on each request.

Wikimedia has something about php caching and optimization and they use APC: http://meta.wikimedia.org/wiki/PHP_cachi...timization

Yes, if you can afford it that shall be the best thing you can do. But, your current server is also capable of enough serving (at least for now) if you can turn to fast SCSI drives.

By the way, from your mysql stats it's seems like you have very heavy 'read' queries. Try optimization on queries on the most heavily hit pages, if you havn't already done so.

Hlo,

Here is a bit of Hardware info I can give u about our server. Could u pls point on wat needs to be upgraded here.

Processors 4
Model Intel® Xeon™ CPU 3.60GHz
Chip MHz 3600.25 MHz
Cache Size 1024 KB
System Bogomips 28730.96
PCI Devices 00:00.1 Class ff00: Intel Corp.: Unknown device 3591
00:08.0 System peripheral: Intel Corp.: Unknown device 359b
00:1f.1 IDE interface: Intel Corp. 82801EB Ultra ATA Storage Controller
00:1f.3 SMBus: Intel Corp. 82801EB SMBus Controller
01:06.0 VGA compatible controller: ATI Technologies Inc Radeon RV100 QY [Radeon 7000/VE]
05:00.0 Ethernet controller: Broadcom Corporation: Unknown device 1659
06:00.0 Ethernet controller: Broadcom Corporation: Unknown device 1659
08:07.0 RAID bus controller: Adaptec AIC-7902
08:07.1 RAID bus controller: Adaptec AIC-7902

IDE Devices hda: HL-DT-STDVD-ROM GDR8082N

SCSI Devices ADAPTEC RAID 1 (Direct-Access)
IBM 25R5170a S320 0 (Processor)

USB Devices Linux 2.4.21-4.ELsmp ehci-hcd Intel Corp. 82801EB USB2 00:1d.7
USB UHCI Root Hub 2600
USB UHCI Root Hub 2200

Thanks

If possible, can you post the output of:

df -h

cat /proc/scsi/scsi

cat /proc/scsi/scsi gave the foll result

Attached devices:
Host: scsi1 Channel: 00 Id: 00 Lun: 00
Vendor: ADAPTEC Model: RAID 1 Rev: 1.0
Type: Direct-Access ANSI SCSI revision: ffffffff
Host: scsi1 Channel: 00 Id: 08 Lun: 00
Vendor: IBM Model: 25R5170a S320 0 Rev: 1
Type: Processor ANSI SCSI revision: 02


And df -h gave the foll :

Filesystem Size Used Avail Use% Mounted on
/dev/sda11 981M 318M 613M 35% /
/dev/sda1 114M 15M 94M 14% /boot
/dev/sda8 3.9G 492M 3.2G 14% /home
/dev/sda10 981M 17M 915M 2% /home/recon
none 2.0G 0 2.0G 0% /dev/shm
/dev/sda2 20G 2.3G 17G 13% /tmp
/dev/sda6 4.9G 1.1G 3.5G 24% /usr
/dev/sda3 15G 1.4G 13G 10% /var
/dev/sda12 77G 63G 11G 86% /var/lib/mysql
/dev/sda7 3.9G 2.0G 1.7G 55% /var/log
/dev/sda5 7.7G 96M 7.3G 2% /var/spool/mail

Thanks,

Ratheesh K J

It happened again today.

At the time of load, I captured a snapshot of mytop and the stat.php result.

Both have been attached with this mail.

Kindly see if anything can be found!!!


Thanks

It seems like temporary tables is a problem as well. Did you apply any modifications to my.cnf yet? *focus* comment log-bin if you havn't already and _do_ apply other modifications as I mentioned. Another modification that you can do is: (Created_tmp_disk_tables is high)

tmp_table_size = 64M

should be increased to: (or even 256 if required)

tmp_table_size = 128M

If you have done what all I mentioned above, you have no choice but to put in a new SCSI disk to the server dedicated to MySQL. Currently, I can see that "/dev/sda12 77G 63G 11G 86% /var/lib/mysql" is dedicated for MySQL but it's only a partition of /dev/sda but not a different disk. Try getting a 73g SCSI or 80gb 10,000 RPM SATA - II disk and assign it to MySQL. That should help a lot!

Also you should analyze all of your queries and see where they slow down. Try to think what can result in writing to disk? Temporary tables, lots of sorting resulting in temporary tables, etc..

But how can i comment log-bin. What if i need to restore data in case of failure?. Is bin logging done for replication only?

Also you said :
"I can see that "/dev/sda12 77G 63G 11G 86% /var/lib/mysql" is dedicated for MySQL but it's only a partition of /dev/sda but not a different disk."

What exactly is the disadv of the above? How will a 73g SCSI or 80gb 10,000 RPM SATA - II disk assigned to MySQL help?

Does it have the same implication of having a seperate DB server?

Actually I have not changed the values in my.cnf. Its a busy server. Will be testing with the cnf file in the weekend!!!

Thanks,

Ratheesh K J