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?

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

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)

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 <major@mhtx.net> >> 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