Fw: MySql4.1.15 Vs MySql5.0
am 05.11.2005 00:56:48 von Jorge BastosDoes the table has promary keys and index's ?
----- Original Message -----
From: "Ilavajuthy Palanisamy"
To:
Sent: Friday, November 04, 2005 11:47 PM
Subject: MySql4.1.15 Vs MySql5.0
Hi,
We are evaluating MySql database for our future project. We found that
the performance of MySql5.0 is slower compared to MySql4.1.15 in terms
of inserts and updates.
My PC configuration
Windows XP Professional version
1GB RAM
Pentium(r) 4 CPU 3.2 GHz
My-huge.ini setup
What I have tried.
Created a table and inserted 2 million records. In 4.1.15 it took ~7
mins and in 5.0 it took ~6 mins. This insertion is happened through java
program.
After inserting we tried update on the above table. In 4.1.15 it took
~10 mins and in 5.0 it took ~46 mins.
We need some of the features of 5.0 like view, auto increment, etc,
however due to this performance issues, we are confused which version to
go for or to continue to stay with our current database that we are
using?
Would like to know is this a real performance issue in 5.0 or do I need
to change any settings?
The schema of the table that I used is;
String stmt = "INSERT INTO " + tableName + " (id," +
"userId," +
"applicationInstanceId," +
"startTime," +
"endTime," +
"bytesIn," +
"bytesOut," +
"packetsIn," +
"packetsOut," +
"errorPackets," +
"droppedPackets," +
"flowCount," +
"lastUpdated," +
"entryStatus," +
"ageOut," +
"deviceId," +
"deviceSpecificId," +
"sourcePort)" +
" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
The update output of 4.1.15 Vs 5.0
MySql5.0 default setup
----------------------
mysql> update mytable set entrystatus=1 where userid=12 and
entrystatus=0;
Query OK, 105263 rows affected (50.25 sec)
Rows matched: 105263 Changed: 105263 Warnings: 0
mysql> update mytable set entrystatus=1 where userid=13 and
entrystatus=0;
Query OK, 105263 rows affected (42.45 sec)
Rows matched: 105263 Changed: 105263 Warnings: 0
mysql> update mytable set entrystatus=1 where userid=13 and
entrystatus=0;
Query OK, 0 rows affected (0.55 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> update mytable set entrystatus=1 where userid=14 and
entrystatus=0;
Query OK, 105263 rows affected (44.30 sec)
Rows matched: 105263 Changed: 105263 Warnings: 0
MySql5.0 huge setup
--------------------
mysql> update mytable set entrystatus=1 where userid=1 and
entrystatus=0;
Query OK, 105264 rows affected (40.94 sec)
Rows matched: 105264 Changed: 105264 Warnings: 0
mysql>
mysql> update mytable set entrystatus=1 where userid=2 and
entrystatus=0;
Query OK, 105264 rows affected (36.48 sec)
Rows matched: 105264 Changed: 105264 Warnings: 0
mysql>
mysql> update mytable set entrystatus=1 where userid=3 and
entrystatus=0;
Query OK, 105264 rows affected (37.97 sec)
Rows matched: 105264 Changed: 105264 Warnings: 0
MySql4.1.15 default setup
-------------------------
mysql> update mytable set entrystatus=1 where userid=1 and
entrystatus=0;
Query OK, 105264 rows affected (11.08 sec)
Rows matched: 105264 Changed: 105264 Warnings: 0
mysql>
mysql> update mytable set entrystatus=1 where userid=2 and
entrystatus=0;
Query OK, 105264 rows affected (11.28 sec)
Rows matched: 105264 Changed: 105264 Warnings: 0
mysql> update mytable set entrystatus=1 where userid=3 and
entrystatus=0;
Query OK, 105264 rows affected (11.50 sec)
Rows matched: 105264 Changed: 105264 Warnings: 0
mysql> update mytable set entrystatus=1 where userid=4 and
entrystatus=0;
Query OK, 105263 rows affected (11.30 sec)
Rows matched: 105263 Changed: 105263 Warnings: 0
MySql4.1.15 huge setup
----------------------
mysql>
mysql> update mytable set entrystatus=1 where userid=1 and
entrystatus=0;
Query OK, 105264 rows affected (10.72 sec)
Rows matched: 105264 Changed: 105264 Warnings: 0
mysql>
mysql> update mytable set entrystatus=1 where userid=2 and
entrystatus=0;
Query OK, 105264 rows affected (10.77 sec)
Rows matched: 105264 Changed: 105264 Warnings: 0
mysql> update mytable set entrystatus=1 where userid=3 and
entrystatus=0;
Query OK, 105264 rows affected (10.80 sec)
Rows matched: 105264 Changed: 105264 Warnings: 0
I would appreciate your response in this regard.
Ila.
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org