Quick Test Drive Storage Engine MySQL 5.0

Testing ini dilakukan pada notebook Aspire 4715Z, dengan spesifikasi: Intel Pentium dual-core processor T2310, 1GB DDR2, 80GB HDD, OSnya WendowXP. disini ada 4 MySQL server yang running, nih screenshoot services nya :)

Pertama kita buat schema ‘test_drive’ untuk testing insert 3 juta record, mulai yo masuk mysql prompt…
C:Documents and Settingsdavid>mysql -u root -p -P 3307
Enter password: ********

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 12 to server version: 5.0.45-community-nt

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql> CREATE TABLE `test_drive` (
-> `C1` int(11) default NULL,
-> `C2` varchar(20) default NULL,
-> `C3` date default NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

mysql> show create table test_driveG;
*************************** 1. row ***************************
Table: test_drive
Create Table: CREATE TABLE `test_drive` (
`C1` int(11) default NULL,
`C2` varchar(20) default NULL,
`C3` date default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR:
No query specified

Kemudian kita buat stored procedure untuk melakukan test insert:

mysql> delimiter //
mysql> create procedure test_insert()
-> begin
-> declare v_ctr mediumint;
-> set v_ctr = 0;
-> while v_ctr < 3000000
-> do
-> insert into test_drive values (1,'sample audit string',now());
-> set v_ctr = v_ctr + 1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.14 sec)

mysql> delimiter ;

Sekarang kita test insert 3 juta record di engine InnoDB ini dengan memanggil procedure yang sudah dibuat:

mysql> call test_insert();
Query OK, 1 row affected (4 min 51.83 sec)

Nah, lumayan lama juga (4 menit lebih tuh…) sekarang kita hapus semua recordnya dan ubah engine dari InnoDB ke MyISAM kemudian panggil procedure test_insert() lagi untuk test insert di engine MyISAM

mysql> truncate table test_drive;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table test_drive engine=MyISAM;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> call test_insert();
Query OK, 1 row affected (3 min 52.58 sec)

Tuh hasilnya MyISAM lebih cepat dari InnoDB (3 menit lebih), kemudian kita hapus record dan ubah engine lagi untuk testing engine ARCHIVE

mysql> truncate table test_drive;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table test_drive engine=archive;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> call test_insert();
Query OK, 1 row affected (56.83 sec)

dan engine ARCHIVE ini yang tercepat (gak sampe 1 menit tuh…) sekarang kita test select count pada engine Archive nih,

mysql> select count(*) from test_drive;
+----------+
| count(*) |
+----------+
| 3000000 |
+----------+
1 row in set (1.59 sec)

mysql> select count(*) from test_drive where c1=1;
+———-+
| count(*) |
+———-+
| 3000000 |
+———-+
1 row in set (1.72 sec)

Oke, selanjutnya ubah engine sesuai yang ingin kita test dan lakuin select count seperti diatas pada engine InnoDb dan MyISAM

Referensi:
http://dev.mysql.com/tech-resources/…mysql_5.0_psea2.html

semoga tulisan dan informasi ini bermanfaat…

Advertisements

About davidand

Hi, welcome to my blogs. My name is David Andriansyah. I am currently working as an independent Cognos Business Intelligence consultant, and I also as Freelance Web Developer. View all posts by davidand

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: