PDA

View Full Version : Got Server Lag? This might be your fix..


WildcardX
02-03-2007, 07:42 PM
I have done a lot of research and testing into some serious server lag we have been experiencing on the PEQ server for the last month. If you are running a server and are trying to figure out why your server is lagging badly, I encourage you to read my post on this topic at PEQ forms:

http://www.projecteq.net/phpBB2/viewtopic.php?t=2148

WildcardX
02-05-2007, 03:57 PM
I thought I would post some more links about the InnoDB storage engine if anyone wants to read more.

Chances are if you are a small server then the delays caused by table locks are small and tolerable and you may not even notice. However, multi-user servers have probably noticed some performance degradation consistent with the situation I described in my post at the PEQ forum. If you have been scratching your head trying to figure it out, then I recommend you examine the InnoDB storage engine and decide if it may be right for you.

You do not need MySQL 5.0 to use the InnoDB engine, however MySQL 5.0 did enhance the engine so that it requires less resources from your system than MySQL 3, 4, or 4.1 does.

Enjoy the reads and please post your results and experiences.

http://www.databasejournal.com/features/mysql/article.php/2248101
http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.html
http://www.innodb.com/howtouse.php
http://opensource.apress.com/article/215/myisam-not-necessarily-faster-than-innodb
http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/
http://www.innodb.com/bench.php
http://dev.mysql.com/doc/refman/5.0/en/innodb.html
http://dev.mysql.com/doc/refman/4.1/en/innodb.html

NOTE: You can easily find out if your MySQL server instance has your InnoDB storage engine enabled or not. Run the following query:


show variables like 'have_innodb';


If this query returns a value of "YES" then InnoDB is enabled. If it returns "NO" then you need to enable it by editing your "/etc/my.cnf" file under linux or "my.ini" file under windows and then restart your MySQL server. Please refer to the links I posted above on how to enable InnoDB.

John Adams
02-06-2007, 03:08 AM
WildcardX, I don't have the SQL files in front of me, but is it true that some tables are InnoDB already, and some are still MyISAM in the current PEQ database? If so, how is that determined, or is it just "guessed" by the server depending on the table you are creating? I've never quite grasped that (I tend to favor MS SQL myself :)).

As I posted on PEQ, I am all for staying modern, especially since the alleged "supported" DB is no longer available from it's creators. Many have reported problems with MySQL 5.0, but many have also answered with solutions. I think it's time for the community to move forward with this.

It would be imperative that the devs acknowledge that using a newer DB will not out-and-out break the Emu. It's not likely, since SELECT, INSERT, UPDATE, and DELETE are pretty rudimentary. But you never know... data types and all. For instance, some older MySQLs do not support comments. ~twirls finger~ who cares about that tho. :)

Angelox
02-06-2007, 09:52 AM
LoL WildCardX is a Dev!
believe me when I tell you this has all been tested (I was part of it for a while).
It all works fine - and you can have a backup ready , for just incase.
Also, the current PEQ DB has no InnoDB tables

WildcardX
02-06-2007, 10:31 AM
It is the table create SQL statement that determines what storage engine MySQL will use for a given table. For example:


CREATE TABLE `pets` (
`type` varchar(64) NOT NULL default '',
`npcID` int(11) NOT NULL default '0',
`temp` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Notice the "Engine=" statement? That requests a particular storage engine from MySQL. In MySQL 4.1 or older, you will see a "TYPE=" statement instead of "ENGINE=". MySQL deprecated the "TYPE" keyword in MySQL 5.0.

WildcardX
02-06-2007, 10:34 AM
Actually, technically speaking, even the current PEQ database has two tables that will be made into InnoDB tables providing your MySQL instance has the InnoDB storage engine enabled. These two tables are "rule_sets" and "rule_values". This happened by accident if I understand correctly. However, the PEQ game server does not have InnoDB enabled so all tables served up by it now are all MyISAM.

John Adams
02-07-2007, 02:34 AM
LoL WildCardX is a Dev!
Put down the pom-poms, Cheerleader. I know he's a Dev. I was meaning, all devs in the community come to an agreement or understanding before one person unilaterally makes a change. No disrespect to WildcardX meant. :)

Besides, I freakin agree with him. So there. :p

WildcardX
02-07-2007, 02:41 AM
Fear not, when MySQL 5.0 and/or InnoDB become the "official" technologies of this project, it will be decision made by me and the rest of the dev team. In the meantime, I offer to share the information I have with the community so if people want to give these technologies a go, they can try it. I just ask people to post back to here their findings and experiences.