usally these types of problems are caused by server errors and mainly the connections and there are alot of people trying to log on to the site and the servers are usally the source i hope
Persistent connections stay open at the end of PHP script execution. While this can certainly give you better performance, it's not at all the same as a connection pool. There is no pool manager, you cannot set upper or lower limits, each process can open an additional connection, etc. On a high-traffic site, using p_connect can actually kill your database by overloading it with too many opened connection
There are a couple of additional caveats to keep in mind when using persistent connections. One is that when using table locking on a persistent connection, if the script for whatever reason cannot release the lock, then subsequent scripts using the same connection will block indefinitely and may require that you either restart the httpd server or the database server. Another is that when using transactions, a transaction block will also carry over to the next script which uses that connection if script execution ends before the transaction block does. In either case, you can use register_shutdown_function() to register a simple cleanup function to unlock your tables or roll back your transactions. Better yet, avoid the problem entirely by not using persistent connections in scripts which use table locks or transactions (you can still use them elsewhere).
It's also worth using iostat -x 10 to see whether the server is short of disk seeks. Particularly likely if you're doing a lot of transactions and don't have a write caching disk controller. If setting innodb_flush_log_at_trx_commit=2 solves the problem that strongly supports this theory.
Using vmstat 10 during this to look for swapping/faulting activity is also useful, since a large number of active connections may have caused swapping to start.
* vnstat: an application to monitor data transfer at the network interface. Highly recommended!
* mytop: an application similar to 'top' but which monitors a MySQL server.
* innotop - A MySQL and InnoDB monitor program.
* mysqlreport: a perl script that generates an analysis of MySQL performance.
Edited by: ---shahost--- on 10/02/07 at 12:58:24 PM
Just check who are online at the time of the spikes. Perhaps it will be coincidence but you will find some interesting congruence. There are several statistic tests who will lead to.....
Persistent connections stay open at the end of PHP script execution. While this can certainly give you better performance, it's not at all the same as a connection pool. There is no pool manager, you cannot set upper or lower limits, each process can open an additional connection, etc. On a high-traffic site, using p_connect can actually kill your database by overloading it with too many opened connection
There are a couple of additional caveats to keep in mind when using persistent connections. One is that when using table locking on a persistent connection, if the script for whatever reason cannot release the lock, then subsequent scripts using the same connection will block indefinitely and may require that you either restart the httpd server or the database server. Another is that when using transactions, a transaction block will also carry over to the next script which uses that connection if script execution ends before the transaction block does. In either case, you can use register_shutdown_function() to register a simple cleanup function to unlock your tables or roll back your transactions. Better yet, avoid the problem entirely by not using persistent connections in scripts which use table locks or transactions (you can still use them elsewhere).
http://www.total-domination.org/persistent.txt
I'm not too hot on PHP but MySQL by default uses a connection pool manager. I know from experience that there is a bug in the .NET connector that causes the DB max connections to be hit. By putting 'pooling=false' at the end of the DB connection string you can get round this. This may also be the same with whatever PHP uses.
By default doesn't MySQL have some stupid timeout value of like 8 hours before it kills a DB connection. Maybe all the dead connections hanging round are building up and causing the lag?
I am a very experienced with PC's and that things but i never have saw about this... Maybe people are downloading things and can cause lags at the game or if people comes logging in at the same moment... Thats what i think...
Checkout my bazaar people! xD
http://www.torn.com/#/forums.php?forumID=4&ID=10637932 is my thread about bazaars.
Well im not hot on DB's or programing etc. But I am shit hot on networking, servers, infrastructure and work with enough DB people to say your issue here is NOT down to the number of connections.
Looking at the data, it doesn't show a gradual increase in load.
System load is jumping quickly which suggest a single process suddenly causing the problem.
Also if the issue was with persistant connections then it doesnt explain why it happens around the same period consistantly.
I think you need to see what else is happening around this time, other system processes that could be running and locking up files such as anti-virus, if you have any installed, is it updating around that time and running a scan afterwards?
I would suggest running process monitoring tools around the time to see what other processes could be causing an effect.
this link coming from the innoDB website has a bit about troubleshooting and perfornace related problems. i dunno if that can help seeming as my knowledge only extends to innoDB being a storage system or something that works with MySQL, but i hope it can.
Good luck ched:D
@Ched:
Are the Beta servers still available? Can you do a DB backup and restore on the Beta?
If you can, run with no connections and if the performance is OK, then open it back up to users.
It needs to be representative though, with all crons etc working.
Open it for 48hrs and tell as many users as possible to be on the Beta system (perhaps a reward for the most active).
I know that Beta environments don't always exhibit the same faults, but if the Beta environment is faster we could stay there... jk
You have lost your Donater Status. You will no longer recieve 5 energy every 10 minutes and your 150 energy bar has been reduced to 100. If you would like them back, please go here to donate again.
In a word... NO!!!
heh... I know a TON about php / mysql, but you did say theres about 10 MILLION lines of php / sql / crons / assorted other code ... so i doubt i could isolate the info.
Check for zombie processes spawned from ddos / dos atacks that use malformed syn / syn/ack / ack packets to exploit the old 3-way handshake bug, (its a long running way to covertly &*^& apache webservers)
also, is the auction market done in real time? item market as well? why not change those to updating every 30-45 sec. Same with pts / users online / etc.
I have a lot of ideas.... but... ya know. there probably all thought of / mentioned before.
If you like something I've done on the forums send me a morphine, I'm running out!
Well im not hot on DB's or programing etc. But I am shit hot on networking, servers, infrastructure and work with enough DB people to say your issue here is NOT down to the number of connections.
Looking at the data, it doesn't show a gradual increase in load.
System load is jumping quickly which suggest a single process suddenly causing the problem.
Also if the issue was with persistant connections then it doesnt explain why it happens around the same period consistantly.
I think you need to see what else is happening around this time, other system processes that could be running and locking up files such as anti-virus, if you have any installed, is it updating around that time and running a scan afterwards?
I would suggest running process monitoring tools around the time to see what other processes could be causing an effect.
i agree with the anti virus..whenever avast or avg is working my computer goes slow as does TC..but other wise i have no problem with TC...maybe its something thats running on our computers:s
agreed looks like the spike happens and hits critical within 10 min - at which point I am guessing you kill access to the servers? Deadlock is coursed when one process is waiting on another before it can continue. so.. maybe try checking the crons that have been moved around. see if some that where supposed to interact with each other at certain times are now unavailable to get the data they require before they can continue..
Ched, I'm not saying I'm an expert, but I know my share of databases and such.
I think that if you want a decent advice, we need more info then just the lag peaks.
If one wants to find out what's causing these peaks, one needs to know what happens in the databases at that moment, to find the relations between them.
For example user-counts? Is there a sudden increase of people logging in? (Maybe the West Coast of the USA gets up at that time for instance)
Certain crons running at that moment (Job payouts, city stats gathering)?
Actually, in short: One should be able to see where all these connections are going to.
I would love to get more in-depth in the problem, but I think one needs more information for that.
The UK gets out of school at about 4. And must be on torn by half past!!
![[]](http://awardimages.torn.com/--large.png "[]")
![Marshmallow [127232]](http://awardimages.torn.com/127232-60631-large.png "Marshmallow [127232]")
![Doomsday [86124]](http://awardimages.torn.com/86124-63052-large.png "Doomsday [86124]")
![Chedburn [1]](http://awardimages.torn.com/1-67459-large.png "Chedburn [1]")
![Spliffeh [315389]](http://awardimages.torn.com/315389-11899-large.png "Spliffeh [315389]")
![Ping [59347]](http://awardimages.torn.com/59347-17030-large.png "Ping [59347]")
![MrPink [95850]](http://awardimages.torn.com/95850-26017-large.png "MrPink [95850]")
![_Psychosocial_ [309085]](http://awardimages.torn.com/309085-71845-large.png "_Psychosocial_ [309085]")
![Sildraco [369974]](http://awardimages.torn.com/369974-8205-large.png "Sildraco [369974]")
![327535 [327535]](http://awardimages.torn.com/327535-47241-large.png "327535 [327535]")
![Defences [263839]](http://awardimages.torn.com/263839-72513-large.png "Defences [263839]")
![_Cynic_ [206537]](http://awardimages.torn.com/206537-54811-large.png "_Cynic_ [206537]")
![Bernetty [242823]](http://awardimages.torn.com/242823-33706-large.png "Bernetty [242823]")
![My_Cat_Quit [165026]](http://awardimages.torn.com/165026-82897-large.png "My_Cat_Quit [165026]")
![DemonSkye [37124]](http://awardimages.torn.com/37124-73634-large.png "DemonSkye [37124]")
![BlackRoseKiller [271707]](http://awardimages.torn.com/271707-82218-large.png "BlackRoseKiller [271707]")
![cobrachang [343620]](http://awardimages.torn.com/343620-99000-large.png "cobrachang [343620]")
![DirtyWarlock [116791]](http://awardimages.torn.com/116791-99845-large.png "DirtyWarlock [116791]")
![GH-Tony_Montana [353206]](http://awardimages.torn.com/353206-98085-large.png "GH-Tony_Montana [353206]")
![FIN [57057]](http://awardimages.torn.com/57057-73609-large.png "FIN [57057]")
![thecrow [241519]](http://awardimages.torn.com/241519-61441-large.png "thecrow [241519]")
![Legend [233886]](http://awardimages.torn.com/233886-35126-large.png "Legend [233886]")
![Shippoe_Crazzy [114544]](http://awardimages.torn.com/114544-22062-large.png "Shippoe_Crazzy [114544]")
Follow @TornRPG