php/mysql help - game server getting hammered

[endofleg]

Hi,

 

Firstly, wasn't sure if I should post this in the PHP or the Mysql section, so if I picked the wrong one, I apologise.

-

 

Myself and another dev designed a browser based game, it utilises ajax to load individual php files in various divs.

 

It's hard to go into detail without killing you all with boredom so I hope I can get some help if I provide a general overview:

 

When 2 players enter a battle, "Battle.php" is loaded, which in turn calls 5 .php files into 5 divs, each of these 5 divs refreshes every few seconds (we wanted the game to seem as 'live' as possible).

 

Now, each of these sub-php files basically open and close a connection each time they run.  So 

<?php include ../Connection.php   //calls a php which simple holds the connection info and opens a connection
do stuff
mysql query
close mysql connection
?>

 

The problem we have hit is that once we have 6 games running (so 12 players refreshing these divs all the time) the php starts bugging out with mysql errors, and eventually the server runs out of memory (it's a vhost running ubuntu dapper but if it struggles with 6 games, I wouldn't think a dedicated server would handle much more either)

 

Would it be better practice to just open the connection once when Battle.php loads and then close it at the end of the battle (or if a sufficient timeout occurs)?  Would what we are doing generally be a massive RAM hog?  Is there a more sensible way of doing this?

 

Also, I ran mysqltuner on the server hoping for some aid, this is only a snapshot of a short period of time, during which I cut off new game creation

>>  MySQLTuner 0.9.1 - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[OK] You have the latest version of MySQLTuner
[OK] Currently running supported MySQL version 5.0.22-Debian_0ubuntu6.06.10-log
[!!] Switch to 64-bit OS - MySQL cannot currenty use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 31M (Tables: 118)
[--] Data in InnoDB tables: 1M (Tables: 118)
[!!] Total fragmented tables: 13

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 7m 24s (497K q [122.989 qps], 61K conn, TX: 213M, RX: 84M)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 2.6M per thread and 35.0M global
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.6G (32% of installed RAM)
[OK] Slow queries: 0% (0/497K)
[OK] Highest usage of available connections: 1% (18/999)
[OK] Key buffer size / total MyISAM indexes: 16.0M/3.1M
[OK] Key buffer hit rate: 100.0%
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0%
[!!] Joins performed without indexes: 12793
[OK] Temporary tables created on disk: 0%
[!!] Thread cache is disabled
[OK] Table cache hit rate: 79%
[OK] Open file limit used: 6%
[OK] Table locks acquired immediately: 99%
[OK] InnoDB data size / buffer pool: 1.9M/2.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    thread_cache_size (start at 4)

 

 

Any help will be greatly appreciated.. thanks in advance

[unkwntech]

Establish your link to the database at the beginning of the main file battle.php then executive each query one after the other using the same connection that way you are only using 1 connection to do the queries.  Make sure you done SELECT *... unless you NEED every record, also see how many queries you can reduce to 1 query with a sub query to actually push less info to/from php/mysql.

[endofleg]

I was hoping someone would say that... it may solve our problems!

 

our queries are pretty tight, we filter down to 1 record every query and only pull through the fields we require for the game.

 

Being that the main 2 tables that are being hammered show as "not indexed" in phpmyadmin, yet in mysqladministrator we set them with an auto-incrementing key field... would this be something we also need to take a look at (i know i'm straying into mysql territory here, but I didn't want to start 2 seperate threads to acheive the same goal)

 

Also, with opening a constant connection for each player, are there any settings I would need to change? Would it close down if they closed their browser without completing the game?  (the fact that I don't know this may give you a clue as to why I open a connection EVERY time I need to run a query)

[craygo]

just set the auto-increment field as the primary key so you have at least one field indexed. You can set a field as auto-increment but phpmyadmin will let you save it without a primary key.

 

Ray

[unkwntech]

Yes, if they close their browser it should close the connection do it like this:

 

<?php
//This is the top of the file battle.php
$link = mysql_connect(...);
...
<div>
//PHP file included here
$sql = "SELCT..."
$result = mysql_quert($sql, $link) or die(mysql_error);
</div>
...
//bottom of battle.php
mysql_close($link);

[endofleg]

thanks to the both of you.  If I had an un-wed sister, you could have her!

 

I'll take a look and see how it impacts performance

[endofleg]

Also, one last (hopefully) question:

 

mysql_pconnect was mentioned while i've been googling around, would this apply to our situation better to a normal mysql_connect or am I just confusing myself more?