mysql hanging? win2000, PHP, apache2

[pjames]

Having a problem with a website running on win2000 Pro. It will just hang-up sometimes for huge amounts of time, never shuts down, just seems PHP is not able to find a connection and will sit there for up to a couple of minutes. This happened before upgrading to apache2 also. the mysql is version 3.23.46 and php is 4.3 which is running as a module. The Machine is a dual 1700xp with 1g of ram and 36gig scsi 10k drive, with nothing running but mail, ftp, and webserver for 2 sites this php site and a static html site, so the machine should handle it. I have tried to find a website that could help explain some optimization settings for mysql and php with win2000 and haven\'t really had any luck. The website that is running is 99.9% used over a lan, it is used for company customer information and employees some job info tracking. PHPmyadmin will even hang quite often, so not sure why. Not sure where to begin to look for bottlenecks. The same version of apache also hosts an html based website that runs flawlessly, so not sure if apache is the problem, it never reports an error when the hangs occur. I am using mysql_connect on all connections and for each query their is a connect. I sometimes wonder if it is the modified session handler that causes problems, it writes the session to mysql along with an array for the variable. Their are two pages that have a lot of queries in them around 10 to 15 each and I have added a timer for benchmark purposes, when running well they both load in the .3 second range and have seen it up to .6sec range, but when it hangs they will never load or i get frustated and cancel out.

 

Would like to find some suggestions on where to look for problems or optimizing techniques.

 

Thanks

Paul James

[pjames]

This MySQL server has been running for 4 days, 1 hours, 56 minutes and 6 seconds. It started up on Feb 03, 2003 at 06:46 AM.

 

Server traffic: These tables show the network traffic statistics of this MySQL server since its startup.

 

Traffic ø per hour

Received 0 Bytes 0 Bytes

Sent 0 Bytes 0 Bytes

Total 0 Bytes 0 Bytes

 

Connections ø per hour %

Failed attempts 397,269 4,056.46 88.84 %

Aborted 118 1.20 0.03 %

Total 447,190 4,566.19 100,00 %

 

Query statistics: Since its startup, 191,997 queries have been sent to the server.

Query type ø per hour %

 

More status variables

Variable Value

Created tmp disk tables 13734

Created tmp tables 20278

Created tmp files 288

Delayed insert threads 0

Delayed writes 0

Delayed errors 0

Flush commands 1

Handler delete 110

Handler read first 940

Handler read key 571993

Handler read next 3498

Handler read prev 0

Handler read rnd 422356

Handler read rnd next 11517120

Handler update 1968

Handler write 67406

Key blocks used 7764

Key read requests 1040558

Key reads 2870

Key write requests 43717

Key writes 1711

Max used connections 100

Not flushed key blocks 0

Not flushed delayed rows 0

Open tables 12

Open files 21

Open streams 0

Opened tables 3463

Select full join 6

Select full range join 0

Select range 2

Select range check 0

Select scan 28761

Slave running OFF

Slave open temp tables 0

Slow launch threads 0

Slow queries 2

Sort merge passes 144

Sort range 9

Sort rows 422356

Sort scan 14089

Table locks immediate 75188

Table locks waited 8

Threads cached 0

Threads created 447189

Threads connected 8

Threads running 1

 

//==================================//

 

And here are the mysql variables:

 

Variable Value

back log 50

basedir C:mysql

binlog cache size 32768

character set latin1

character sets latin1 big5 czech euc_kr gb2312 gbk 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

connect timeout 5

datadir C:mysqldata

delay key write ON

delayed insert limit 100

delayed insert timeout 300

delayed queue size 1000

flush OFF

flush time 1800

have bdb NO

have gemini NO

have innodb NO

have isam YES

have raid NO

have openssl NO

init file

interactive timeout 28800

join buffer size 131072

key buffer size 8388600

language C:mysqlshareenglish

large files support ON

log OFF

log update OFF

log bin OFF

log slave updates OFF

log long queries OFF

long query time 10

low priority updates OFF

lower case table names 1

max allowed packet 1048576

max binlog cache size 4294967295

max binlog size 1073741824

max connections 100

max connect errors 10

max delayed threads 20

max heap table size 16777216

max join size 4294967295

max sort length 1024

max user connections 0

max tmp tables 32

max write lock count 4294967295

myisam max extra sort file size 256

myisam max sort file size 2047

myisam recover options 0

myisam sort buffer size 8388608

net buffer length 16384

net read timeout 30

net retry count 10

net write timeout 60

open files limit 0

pid file C:mysqldataMSServer.pid

port 3306

protocol version 10

record buffer 131072

record rnd buffer 131072

query buffer size 0

safe show database OFF

server id 0

slave net timeout 3600

skip locking ON

skip networking OFF

skip show database OFF

slow launch time 2

socket MySQL

sort buffer 2097144

sql mode 0

table cache 64

table type MYISAM

thread cache size 0

thread stack 65536

transaction isolation READ-COMMITTED

timezone Central Standard Time

tmp table size 33554432

tmpdir C:WINNTTEMP

version 3.23.46-nt

wait timeout 28800

[effigy]

a few things you could try:

 

- try using mysql_pconnect instead (see settings in the php.ini as well)

 

- check your php.ini for mysql\'s max connections allowed

 

- check how much memory apache / mysql is using

 

- what mpm is apache running in? in your apache bin run http -l and look for the mpm - should also be listed in here: http://httpd.apache.org/docs-2.0/mpm.html then check the httpd.conf for it\'s configuration

 

- apache tuning: http://httpd.apache.org/docs-2.0/misc/perf-tuning.html

 

but it sounds like mysql is running out of open connections for php — how busy is this server?

[pjames]

Thanks for the reply!

 

I initially used pconnects and then switched to connect, my thinking was that the pconnect wasn\'t always closing a connection and was overloading the connections, so that is for the switch to connect, it runs through a function that opens the connection runs the query and then closes the connection with mysql_close. I then loop through the result and free the result with mysql_free_result.

 

Havn\'t checked the php.ini settings, will let you know, I\'ll try to find some info on good settings.

 

I have checked the memory on occasion. mysql seems to stay fairly stable. apache will appear to fire up a child and that will spike pretty high in mem usage. (I am not that thorough on apache and how it works in windows).

 

apache is running with the winnt mpm and i did change the settings reccommended on the apache sight. The things that confuse me is that some of the suggestions that are listed are not in the http conf like the others and am a little nervous adding those variables because they may not work with the windows version? I am pretty hazy on this.

 

My thinking was the running out of connections or table locks. Thank you for your insight. The sight isn\'t really that busy, i guess, it serves 8 users and has close to 30 pages that are most commonly accessed, which is pretty much all day, checking info, adding names, addresses, etc.

 

Thank you,

Paul

 

Here is the output of the mysql variables from phpinfo.

 

mysql

MySQL Support enabled

Active Persistent Links 0

Active Links 0

Client API version 3.23.49

 

Directive Local Value Master Value

mysql.allow_persistent On On

mysql.connect_timeout Unlimited Unlimited

mysql.default_host no value no value

mysql.default_password no value no value

mysql.default_port no value no value

mysql.default_socket no value no value

mysql.default_user no value no value

mysql.max_links Unlimited Unlimited

mysql.max_persistent Unlimited Unlimited

[pjames]

I think I am narrowing it down -

 

1) possibly the way mysql locks tables or table locking

 

2) Apache2 Virtual hosting

 

Still a little unsure on the Mysql variables and settings but am starting to think that one or both of the above could be the root of the problem. ReDid some things on the session handler and will see if that was it and also changed a couple of settings, so we will see how it does today.

 

Paul