i am using a MySql server 4.1 with the following conf:
-windows 2000, 1.6 Ghz and 2 gigs ram.
on this windows i have:
apache, mysql, phpbb and a game server i wrote.
this game server is doing lot of select * at anytime,
and every 5mn/10mn lot of UPDATE/“DELETE * WHERE key=”/INSERT,
the tables have more than 2 000 000 objsects, but all indexed,
basically all queries done by server are not shown as slow queries, however through the web php script there are some slow queries yes.
What we see is that at startup, all is fine (55K objects are dowloaded from tables), then after a couple of days, we see that some insert are timing out because sql daemon seems to be completly out. The Ram of the server get increased, correct, but not that much, i mean when problem occurs the ram is 1.38 out of 2 Ghz.
My server seems to be fine, but is blocked on some queries timeout so everything freezed (i could change this design , but the fact is that usually the main game engine does not interact with sql, just few times, anyway yes i can change this it will be very good improvement), BUT my point if WHY are we seeing this graduate decrease in perf ? How could i debug this issue ? i have dumped status variable but didnt show extensive prob (i mean looks ok to me, i will post it if needed).
maybe mysql doesnt support this kind of queries traffic ? (lot of updates/insert/delete).
maybe some gameengine queries (the ones executes by main game process) are just “blocked” beacuse table is locked so long ? But then why in the status variable there are few lock (489 out of 500 000 not locked wait) ?
to end i will add that game server is writing a lot on disk,
i didnt check frag but its not rare that the hard disk reach the 90-95% usage,
one big doubt : shall i use MyISAM or InoDB storage engine for my application ?
well any expert view on this more than welcome!