MySql4.1.15 Vs MySql5.0

原贴:http://www.issociate.de/board/post/270984/MySql4.1.15_Vs_MySql5.0.html

 

MySql4.1.15 Vs MySql5.0

------_=_NextPart_001_01C5E19A.28B8C7BE
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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 =3D "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=3D1 where userid=3D12 and
entrystatus=3D0;

Query OK, 105263 rows affected (50.25 sec)

Rows matched: 105263 Changed: 105263 Warnings: 0



mysql> update mytable set entrystatus=3D1 where userid=3D13 and
entrystatus=3D0;

Query OK, 105263 rows affected (42.45 sec)

Rows matched: 105263 Changed: 105263 Warnings: 0



mysql> update mytable set entrystatus=3D1 where userid=3D13 and
entrystatus=3D0;

Query OK, 0 rows affected (0.55 sec)

Rows matched: 0 Changed: 0 Warnings: 0



mysql> update mytable set entrystatus=3D1 where userid=3D14 and
entrystatus=3D0;

Query OK, 105263 rows affected (44.30 sec)

Rows matched: 105263 Changed: 105263 Warnings: 0





MySql5.0 huge setup

--------------------

mysql> update mytable set entrystatus=3D1 where userid=3D1 and
entrystatus=3D0;

Query OK, 105264 rows affected (40.94 sec)

Rows matched: 105264 Changed: 105264 Warnings: 0



mysql>

mysql> update mytable set entrystatus=3D1 where userid=3D2 and
entrystatus=3D0;

Query OK, 105264 rows affected (36.48 sec)

Rows matched: 105264 Changed: 105264 Warnings: 0



mysql>

mysql> update mytable set entrystatus=3D1 where userid=3D3 and
entrystatus=3D0;

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=3D1 where userid=3D1 and
entrystatus=3D0;

Query OK, 105264 rows affected (11.08 sec)

Rows matched: 105264 Changed: 105264 Warnings: 0



mysql>

mysql> update mytable set entrystatus=3D1 where userid=3D2 and
entrystatus=3D0;

Query OK, 105264 rows affected (11.28 sec)

Rows matched: 105264 Changed: 105264 Warnings: 0



mysql> update mytable set entrystatus=3D1 where userid=3D3 and
entrystatus=3D0;

Query OK, 105264 rows affected (11.50 sec)

Rows matched: 105264 Changed: 105264 Warnings: 0



mysql> update mytable set entrystatus=3D1 where userid=3D4 and
entrystatus=3D0;

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=3D1 where userid=3D1 and
entrystatus=3D0;

Query OK, 105264 rows affected (10.72 sec)

Rows matched: 105264 Changed: 105264 Warnings: 0



mysql>

mysql> update mytable set entrystatus=3D1 where userid=3D2 and
entrystatus=3D0;

Query OK, 105264 rows affected (10.77 sec)

Rows matched: 105264 Changed: 105264 Warnings: 0



mysql> update mytable set entrystatus=3D1 where userid=3D3 and
entrystatus=3D0;

Query OK, 105264 rows affected (10.80 sec)

Rows matched: 105264 Changed: 105264 Warnings: 0





I would appreciate your response in this regard.



Ila.




------_=_NextPart_001_01C5E19A.28B8C7BE--