Tuning Apache-PHP-MySQL for CPG

[blackmamba]

Some of us use CPG under heavy traffic load!
This topic tries to help starting webmasters in dealing with Apache, PHP and MySQL tuning for CPG.
I myself I’m in this situation and any help we can get is most welcome.

Please, when posting for the first time in this topic don’t forget to mention your hardware, software and traffic info (as minimum and relevant as possible).

Topics related:
1. High CPU load

[blackmamba]

Info:
Hardware:
Intel PIII 933 Mhz, 500 RAM, HDD maxtor 80Gb, 3COM networking, optical fiber broadband WAN.
Soft:
WinXP SP2, latest XAMPP (Apache 2.2.2, MySQL 5.0.21, PHP 5.1.4)
CPG 1.4.8
Traffic
3000 visits/day; 80.000 pages/day

MySQL has no cache sistem enabled by default, so you have to enable it!
Querie's performance rises to more then 100%.

MySQL config (only the important parts based on the "my_installer_heavy_load_conf.cnf"):

Code Select Expand



[client]
port            = 3306
socket          = mysql

[mysqld]
port            = 3306
socket          = mysql

old-passwords
character-set-server = latin1
collation-server = latin1_general_ci

skip-locking
max_connections=511
query_cache_size=13M
table_cache=1022
tmp_table_size=16M
thread_cache_size=25
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=19M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=208K

server-id = 1

skip-bdb

skip-innodb

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout



results: 27 queries in 0.165 seconds with maximum 4% CPU load!!!

[trippinsweet]

It might be helpful to take a look at this thread: http://forum.coppermine-gallery.net/index.php?topic=30233.msg145702#msg145702

I was having huge problems with coppermine and CPU load, but I managed to solve all that.


Since then I also added a content cache, so a page only have to be created once per day...saving innumerable amounts of queries while delivering the page as fast as if it the server were under 0 load.

I also set the picture table to InnoDB so a table lock isn't performed each time someone votes. For hit counting, I have a seperate table resident in memory that temporarily stores hits. Every hour a cron job batch inserts the data from the temp table into the main pictures table.

That way I can keep the query cache from being invalidated every second.

---

You might want to lok into increasing the size of your query cache. On my machine ,the pictures table alone is taking up near 100MB (it would take up a mere 57 MB if it were MyIASM instead of InnoDB)