CODEWITHSUNDEEP
mysqlperformance
Rinks
Rinks

Reputation: 1067

MySQL 5.5 performance

We recently upgraded the mysql to version 5.5.17 frm 5.1. With this, the performance of many of the queries that worked great in 5.1 has gone down badly. I started updating the queries, but there are just too any to be updated. I was wondering if anyone here has already gone through this and has a better idea.

edit1: As an example, I realised that queries joining two tables and having a where criteria using a contant was really slow in 5.5 compared to 5.1. On adding the constant value in one of the tables and using it from there in the join helped. So in queries below, the second one was much faster than the first one.

select t1.* 
from t1, t2
where t1.field1 = t2.field1
and t1.field2 = 100;

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "t1"    "ALL"   "PRIMARY"   \N  \N  \N  "1" "Using where"
"1" "SIMPLE"    "t2"    "eq_ref"    "PRIMARY"   "PRIMARY"   "152"   "t1.field1" "1" "Using index"

select t1.* 
from t1, t2
where t1.field1 = t2.field1
and t1.field2 = t2.field2; -- i added a col field2 in t2 with value = 100

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "t1"    "ALL"   "PRIMARY"   \N  \N  \N  "1" ""
"1" "SIMPLE"    "t2"    "eq_ref"    "PRIMARY"   "PRIMARY"   "152"   "t1.field1" "1" "Using where"

Upvotes: 2

Views: 6200

Answers (2)

viktike
viktike

Reputation: 733

Unfortunatly somehow the underlying filesystem is also affecting mysql speed. In my measurements the same mysql version is much faster with ext3 filesystem than ext4.

Tested on CentOS.

Upvotes: 0

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

There are times when MySQL 5.1 may outperform MySQL 5.5 under certain circumstances.

Percona performed a bake-off among multiple releases of MySQL

  • MySQL 4.1
  • MySQL 5.0
  • MySQL 5.1 (with built-in InnoDB)
  • MySQL 5.1 with InnoDB-plugin
  • MySQL 5.5
  • MySQL 5.6

All tests were performed with MySQL unconfigured (In other words, no my.cnf was made). The results?

  • MySQL 4.1 performs the best single-threaded
  • MySQL 5.1 with InnoDB plug-in scales on multiple cores better than 5.1 built-in InnoDB, 5.5 and 5.6

If you want newer versions of MySQL to perform better, you must tune for it. In fact, I described in the DBA StackExchange the idea of performing a MySQL Bakeoff.

What do I mean tune for it?

In MySQL 5.5, there are new InnoDB options for utilizing more dedicated read threads, write threads, and overall I/O capacity. This can engage more CPUs in multicore servers. Left unconfigured, MySQL 5.5 would operate on the same level playing field, in most cases, as older versions of MySQL. Sometimes, it could perform worse.

Upvotes: 4

Related Questions