CPG 1.3.3 and MySQL 4.0.17 on Windows 2000 sp4 1.5GB RAM

[jarekn]

Not sure if it is mySQL server that is causing the issue or what not... but clicking on specific links in cpg always makes my server load go to 100% and does not come back with the page it should...  For example if I click on Admin Tools the mySQL server takes up all the cpu ... the cpu goes down after about a minute or so...  

The page comes back with maximum execution time of 30 seconds exceeded in functions.inc.php on line 79 and partial page is only displayed...

Config:
AMD 1.4
1.5GB Ram
Raid 1 200GB hd
Win 2k, sp4 and all windows updates
MySQL 4.0.17
PHP Version 4.3.10
Server API CGI/FastCGI

Running Coppermine 1.3.3, vBulletin, PHP Ads New...

Any suggestions/ideas?


Here is mySQL config:

Server variables and settings
Variable   Session value   Global value  
back log  50  50  
basedir  C:\mysql\  C:\mysql\  
bdb cache size  8388572  8388572  
bdb log buffer size  32768  32768  
bdb home  C:\mysql\data\  C:\mysql\data\  
bdb max lock  10000  10000  
bdb logdir    
bdb shared data  OFF  OFF  
bdb tmpdir  C:\WINNT\TEMP\  C:\WINNT\TEMP\  
bdb version  Sleepycat Software: Berkeley DB 3.2.9a: (December 14, 2003)  Sleepycat Software: Berkeley DB 3.2.9a: (December 14, 2003)  
binlog cache size  32768  32768  
bulk insert buffer size  8388608  8388608  
character set  latin1  latin1  
character sets  latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5  latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5  
concurrent insert  ON  ON  
connect timeout  5  5  
convert character set    
datadir  C:\mysql\data\  C:\mysql\data\  
default week format  0  0  
delay key write  ON  ON  
delayed insert limit  100  100  
delayed insert timeout  300  300  
delayed queue size  1000  1000  
flush  OFF  OFF  
flush time  1800  1800  
ft boolean syntax  + -><()~*:""&|  + -><()~*:""&|  
ft min word len  4  4  
ft max word len  254  254  
ft max word len for sort  20  20  
ft stopword file  (built-in)  (built-in)  
have bdb  YES  YES  
have crypt  NO  NO  
have innodb  YES  YES  
have isam  YES  YES  
have raid  NO  NO  
have symlink  YES  YES  
have openssl  NO  NO  
have query cache  YES  YES  
init file    
innodb additional mem pool size  1048576  1048576  
innodb buffer pool size  8388608  8388608  
innodb data file path  ibdata1:10M:autoextend  ibdata1:10M:autoextend  
innodb data home dir    
innodb file io threads  4  4  
innodb force recovery  0  0  
innodb thread concurrency  8  8  
innodb flush log at trx commit  1  1  
innodb fast shutdown  ON  ON  
innodb flush method    
innodb lock wait timeout  50  50  
innodb log arch dir  .\  .\  
innodb log archive  OFF  OFF  
innodb log buffer size  1048576  1048576  
innodb log file size  5242880  5242880  
innodb log files in group  2  2  
innodb log group home dir  .\  .\  
innodb mirrored log groups  1  1  
innodb max dirty pages pct  90  90  
interactive timeout  28800  28800  
join buffer size  131072  131072  
key buffer size  8388572  8388572  
language  C:\mysql\share\english\  C:\mysql\share\english\  
large files support  ON  ON  
local infile  ON  ON  
log  OFF  OFF  
log update  OFF  OFF  
log bin  OFF  OFF  
log slave updates  OFF  OFF  
log slow queries  OFF  OFF  
log warnings  OFF  OFF  
long query time  10  10  
low priority updates  OFF  OFF  
lower case table names  ON  ON  
max allowed packet  1048576  1048576  
max binlog cache size  4294967295  4294967295  
max binlog size  1073741824  1073741824  
max connections  100  100  
max connect errors  10  10  
max delayed threads  20  20  
max heap table size  16777216  16777216  
max join size  4294967295  4294967295  
max relay log size  0  0  
max seeks for key  4294967295  4294967295  
max sort length  1024  1024  
max user connections  0  0  
max tmp tables  32  32  
max write lock count  4294967295  4294967295  
myisam max extra sort file size  268435456  268435456  
myisam max sort file size  2147483647  2147483647  
myisam repair threads  1  1  
myisam recover options  OFF  OFF  
myisam sort buffer size  8388608  8388608  
net buffer length  16384  16384  
net read timeout  30  30  
net retry count  10  10  
net write timeout  60  60  
new  OFF  OFF  
open files limit  0  0  
pid file  C:\mysql\data\heaven.pid  C:\mysql\data\heaven.pid  
log error  .\heaven.err  .\heaven.err  
port  3306  3306  
protocol version  10  10  
query alloc block size  8192  8192  
query cache limit  1048576  1048576  
query cache size  0  0  
query cache type  ON  ON  
query prealloc size  8192  8192  
range alloc block size  2048  2048  
read buffer size  131072  131072  
read only  OFF  OFF  
read rnd buffer size  262144  262144  
rpl recovery rank  0  0  
server id  0  0  
slave net timeout  3600  3600  
skip external locking  ON  ON  
skip networking  OFF  OFF  
skip show database  OFF  OFF  
slow launch time  2  2  
sort buffer size  2097116  2097116  
sql mode  0  0  
table cache  64  64  
table type  MYISAM  MYISAM  
thread cache size  0  0  
thread stack  196608  196608  
tx isolation  REPEATABLE-READ  REPEATABLE-READ  
timezone  Eastern Daylight Time  Eastern Daylight Time  
tmp table size  33554432  33554432  
tmpdir  C:\WINNT\TEMP\  C:\WINNT\TEMP\  
transaction alloc block size  8192  8192  
transaction prealloc size  4096  4096  
version  4.0.17-max-debug  4.0.17-max-debug  
version comment  Source distribution  Source distribution  
wait timeout  28800  28800  


Here are some run time information, please note this was generated 1 hour after reboot during one of the slowest times for the site...

Runtime Information
This MySQL server has been running for 0 days, 1 hours, 12 minutes and 18 seconds. It started up on May 29, 2005 at 05:42 PM.
Server traffic: These tables show the network traffic statistics of this MySQL server since its startup.
Traffic ø per hour
Received 2,626 KB 2,179 KB
Sent 51,170 KB 42,465 KB
Total 53,796 KB 44,644 KB
Connections ø per hour %
Failed attempts 7 5.81 0.33 %
Aborted 0 0.00 0.00 %
Total 2,127 1,765.15 100.00 %

Query statistics: Since its startup, 24,209 queries have been sent to the server. Total ø per hour ø per minute ø per second
24,209 20,090.46 334.84 5.58

Query type ø per hour %
admin commands 0 0.00 0.00 %
alter table 0 0.00 0.00 %
analyze 0 0.00 0.00 %
backup table 0 0.00 0.00 %
begin 0 0.00 0.00 %
change db 2,167 1,798.34 9.81 %
change master 0 0.00 0.00 %
check 243 201.66 1.10 %
commit 0 0.00 0.00 %
create db 0 0.00 0.00 %
create function 0 0.00 0.00 %
create index 0 0.00 0.00 %
create table 0 0.00 0.00 %
delete 448 371.78 2.03 %
delete multi 0 0.00 0.00 %
drop db 0 0.00 0.00 %
drop function 0 0.00 0.00 %
drop index 0 0.00 0.00 %
drop table 0 0.00 0.00 %
flush 0 0.00 0.00 %
grant 0 0.00 0.00 %
ha close 0 0.00 0.00 %
ha open 0 0.00 0.00 %
ha read 0 0.00 0.00 %
insert 1,548 1,284.65 7.01 %
insert select 0 0.00 0.00 %
kill 0 0.00 0.00 %
load 0 0.00 0.00 %
load master data 0 0.00 0.00 %
load master table 0 0.00 0.00 %
lock tables 0 0.00 0.00 %
optimize 242 200.83 1.10 %
purge 0 0.00 0.00 %
rename table 0 0.00 0.00 %
Query type ø per hour %
repair 0 0.00 0.00 %
replace 30 24.90 0.14 %
replace select 0 0.00 0.00 %
reset 0 0.00 0.00 %
restore table 0 0.00 0.00 %
revoke 0 0.00 0.00 %
rollback 0 0.00 0.00 %
savepoint 0 0.00 0.00 %
select 16,367 13,582.57 74.12 %
set option 0 0.00 0.00 %
show binlog events 0 0.00 0.00 %
show binlogs 0 0.00 0.00 %
show create 0 0.00 0.00 %
show databases 4 3.32 0.02 %
show fields 0 0.00 0.00 %
show grants 0 0.00 0.00 %
show keys 0 0.00 0.00 %
show logs 0 0.00 0.00 %
show master status 0 0.00 0.00 %
show new master 0 0.00 0.00 %
show open tables 0 0.00 0.00 %
show processlist 1 0.83 0.00 %
show slave hosts 0 0.00 0.00 %
show slave status 0 0.00 0.00 %
show status 412 341.91 1.87 %
show innodb status 0 0.00 0.00 %
show tables 15 12.45 0.07 %
show variables 7 5.81 0.03 %
slave start 0 0.00 0.00 %
slave stop 0 0.00 0.00 %
truncate 0 0.00 0.00 %
unlock tables 0 0.00 0.00 %
update 608 504.56 2.75 %

More status variables
Variable Value
Created tmp disk tables 61
Created tmp tables 184
Created tmp files 11
Delayed insert threads 0
Delayed writes 0
Delayed errors 0
Flush commands 1
Handler commit 0
Handler delete 28
Handler read first 1075
Handler read key 303712
Handler read next 9178286
Handler read prev 660
Handler read rnd 28681
Handler read rnd next 40840096
Handler rollback 0
Handler update 28352
Handler write 3519546
Key blocks used 7764
Key read requests 1722633
Variable Value
Key reads 24460
Key write requests 5040
Key writes 5001
Max used connections 8
Not flushed key blocks 0
Not flushed delayed rows 0
Open tables 64
Open files 115
Open streams 0
Opened tables 1190
Qcache queries in cache 0
Qcache inserts 0
Qcache hits 0
Qcache lowmem prunes 0
Qcache not cached 0
Qcache free memory 0
Qcache free blocks 0
Qcache total blocks 0
Rpl status NULL
Variable Value
Select full join 9
Select full range join 0
Select range 1062
Select range check 0
Select scan 3157
Slave open temp tables 0
Slave running OFF
Slow launch threads 38
Slow queries 9
Sort merge passes 5
Sort range 1583
Sort rows 63586
Sort scan 285
Table locks immediate 22003
Table locks waited 9
Threads cached 0
Threads created 2126
Threads connected 2
Threads running 1

[Tranz]

hmm... vbulletin is known to be resource-intensive. What kind of links cause this behavior?

[jarekn]

Hello,

For example if I click on Admin Tools the mySQL server takes up all the cpu ... the cpu goes down after about a minute or so... 

The page comes back with maximum execution time of 30 seconds exceeded in functions.inc.php on line 79 and partial page is only displayed...

hmm... vbulletin is known to be resource-intensive. What kind of links cause this behavior?

[kegobeer]

If you are running your own server, perhaps a misconfiguration is the root of your problems.  Since this is not normal behavior for Coppermine to consume 100% of CPU cycles, I would verify your installations are correct and optimized.

There are a lot of resources available for server setup.  Whenever I run into server problems, I Google for specific forums (PHP, Apache, Windows, MySQL) and see if there's a solution for my particular problem.

[donnoman]

Yah something for sure isn't right. I run my development install's from coppermine 1.2x to 1.4x on a windows 2000 iis5 server with a celeron 500, and 128mb of ram, and I've never experienced that.

calling up the admin tools page doesn't use much resources... unless youre really talking about that you told the admin tools to oh say, recalculate all your thumbnails. Then yah, your server would hit 100% util because of invoking imagemagic or gd to do the image manipulations.  These are resource intensive and will use all the cpu cycles they can lay thier hands on until they're done.

[jarekn]

I know what you are saying about calling up the admin tools page ... and no it is not using any of it functions but just calling that page on its own...

I have couple of the same slow downs with PHP New Ads... clicking on some of the options just makes the SQL go nuts...

Anyone else using 4.0.17?  What is the best/most stable version to use?

[kegobeer]

I always go for the latest stable releases, MySQL 4.1.12, PHP 5.0.4, Apache 2.0.54 (or the latest IIS if you prefer).  I always do my own installations and never rely on any WAMP package to do it for me.