Jump to content


Photo

mysql hanging? win2000, PHP, apache2


  • Please log in to reply
4 replies to this topic

#1 pjames

pjames
  • Members
  • Pip
  • Newbie
  • 7 posts
  • LocationOmaha

Posted 07 February 2003 - 09:47 PM

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

#2 pjames

pjames
  • Members
  • Pip
  • Newbie
  • 7 posts
  • LocationOmaha

Posted 07 February 2003 - 09:49 PM

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

#3 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 07 February 2003 - 11:23 PM

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....cs-2.0/mpm.html then check the httpd.conf for it\'s configuration

- apache tuning: http://httpd.apache....erf-tuning.html

but it sounds like mysql is running out of open connections for php — how busy is this server?
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#4 pjames

pjames
  • Members
  • Pip
  • Newbie
  • 7 posts
  • LocationOmaha

Posted 07 February 2003 - 11:57 PM

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

#5 pjames

pjames
  • Members
  • Pip
  • Newbie
  • 7 posts
  • LocationOmaha

Posted 10 February 2003 - 01:45 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users