[Guide]MySQL InnoDB tunning L2J

[codewarrior]

/*
As requeted by mods, follow an photo-step-featured guide to do some mysql tunning =)

For sure it's dun do miracles by itself, but, it's can help u to get more performance from your hard disk, disk controller, operational system, memory and all kind of strange things than happen in a DBMS (

To view the content, you need to sign in or register

)

And remember:i just what to help ya; no kind of warranty is given, and u always should proceed with caution w/ changes to your system, and i'm not, (ragezone either) responsible to damages caused in your server if u follow next steps; (oohhhhh :rudolph

Also: you love mysql! You luv him, remember that! (i do =))
*/

I) If you are not running the last revision of MySQL 5.0, it's strongly suggested u upgrade before try any kind of mysql tune, as MySQL AB and Innobase Oy (Oracle powered) works hard to make their product good as.. hum,, good as it is =0)
So your first stop is at:

To view the content, you need to sign in or register

Also, Nargen isn't just a cute girl,,, she wrote an good Install guide to MySQL 5.0, check it here
if u dun have an mysql installed.

II) From now, i'll assume you are pro enough to open any file in notepad (or any other text editor (MS Word works,, but,, if u do that, stop to read it, plx)
like

To view the content, you need to sign in or register

III) As before, it's recommended you use some kind of query GUI to handle your requests. I strongly recommend the

To view the content, you need to sign in or register

, and i'll use it to illustrate some steps here.

PREFACE

MySQL 5, by default configuration, use InnoDB engine to manage new tables. It's also, by default, store ALL TABLE "DATA" (data, idx, names, etc) in a single file. Why it's all that bad?
a) File limit : limited to file system (4GiB in FAT32, for example; nevertheless, FAT32 isn't recommended for any kind of use, as NTFS works mutch better, and "almost no performance difference" in modern hardware);
b) DBMS overload : the poor DBMS need to manage all kind of operations in memory and flush them to disk time to time. When it does that, in a single file,, dude, believe me,,, it's pretty hard to him =\
c) From

To view the content, you need to sign in or register

you reads:

Using multiple tablespaces can be beneficial to users who want to move specific tables to separate physical disks or who wish to restore backups of single tables quickly without interrupting the use of the remaining InnoDB tables.

So, stop to talk and let's have some fun =)

(FINALLY)
GUIDE (1) : Enable the innodb_file_per_table in MySQL/InnoDB Enviroment;

Step 0:
Make than: MySQL is running, GUI is configurated, and it's can access the server (this kind of configuration is not the scope of this guide);


Step 1:
Check your current innodb_file_per_table status;

If it's showed like mine, jump to GUIDE (2)

Step 2:
Make sure mom & dad isn't using the MySQL, and L2j is closed: Stop the MySQL Server;


Step 2.1:
Copy entire MySQL directory, data directory (if it's in a different place from mysql main folder), and MY.INI/MY.CNF file to a security place (bullet proof glass isn't necessary, but still recommended)(j/k ?), so you can roll back the server if god dun like ya =);

(no picts about that right? i kno you can handle it... :goku-s: )


Step 3:
Check in your MySQL configuration file (i'll assume it's "MY.INI") for something like "INNODB Specific options";
You can put any config, anywhere bellow "[mysqld]" and it's works fine. i'll telling you to search it to (try to) help ya to organize make some organized change in the config file;


Step 4:
Check if u have the folling line in the file:

#innodb_file_per_table

If u dun found it, dun cry, puts put it in some good place in the file, w/out the #, like this:

Step 5: (VERY IMPORTANT!!!)
Save the config file (

 

[terminator23]

very nice work man
thx a lot

 

[Droppo]

Nice guide, BTW, I tested a whole new installation of my server (as test server) and modified all the scripts to use InnoDB, I must say in some cases takes ore time to install the tables, but once you have the server working, it simply runs smooth that before... So im now importing my players data to the new DBs of my servers, already have one migrated and it's much faster now... BTW, I'm testing JDK 7 on another server now and seems to be working great.

BTW, innodb_file_per_table is NOT recommended for MySQL4 versions, coz there is a known bug with those versions.

A critical bug found: if one uses the 4.1 my.cnf option

innodb_file_per_table

to create tables, and some of the secondary index records are inserted to the
InnoDB 'insert buffer', then after a normal mysqld shutdown InnoDB loses all
those secondary index records! CHECK TABLE will print to the mysqld .err log
that there are less records in the secondary index than in the clustered index.

The corruption does not occur after a mysqld crash. It only follows a normal
shutdown.

Droppo.-

 

[codewarrior]

Nice guide, BTW, I tested a whole new installation of my server (as test server) and modified all the scripts to use InnoDB, I must say in some cases takes ore time to install the tables, but once you have the server working, it simply runs smooth that before... So im now importing my players data to the new DBs of my servers, already have one migrated and it's much faster now... BTW, I'm testing JDK 7 on another server now and seems to be working great.

Yeah,, new part i'll try to explain how change current database to
file per table, w/out need to re import all data.
But do that (re-import all data) is recommended, as innodb huge old file still on server until you do some work w/ it;

BTW, innodb_file_per_table is NOT recommended for MySQL4 versions, coz there is a known bug with those versions.

Droppo.-

From my post, u reads:

I) If you are not running the last revision of MySQL 5.0, it's strongly suggested u upgrade before try any kind of mysql tune, as MySQL AB and Innobase Oy (Oracle powered) works hard to make their product good as.. hum,, good as it is =0)

Anyway, Thx to share this bug =)

hugz!