This article is a sequel to the PostgreSQL history, you can check the backgrounds there.

We've seen what PostgreSQL do, let's see what the big enemy can do!

But first of all, let's take a look at the release dates of the tested versions (4.1.25, 5.0.85, 5.1.38, 5.4.1 és 6.0.11):

There are too versions, which don't really fit into the line. The first is 5.4, which -according to Sun- is a "performance & scalability" release:

The 5.4 release tries to alleviate MySQL's big problem primarily on -how ironic- Sun's CMT servers: scaling to many cores/threads. 5.4 is really an unanticipated release -6.0 should have made these promises come true-, but it seems if Sun had spent 1 billion to the acquisition, something had to be pushed out of the door...

5.4 is beta, but according to the experiences with it, there aren't too much problems with it, compared to 5.1.

The other suspicious contestant is 6.0, which is practically dead now, it won't be released in this form, any time. This release should've bring many new features, like falcon and maria storage, but a lot of things have changed in between...

We begin with the same sysbench OLTP test, at 1 million rows, on a 24 core, 128 GiB RAM 8086-clone. The storage engine is InnoDB.

The graph looks really great. Of course the weakest participant is 4.1, which feels itself quite bad at 64 threads, but after that it can get over even 5.0 in performance. The others don't deserve a word, because 5.4 simply beats them all. It could reach its maximum performance at 16 threads, after which it had to sustain heavy losses in TPS, to finally loose performance after 256 threads. We can have a lot of fast CPUs, if we have to serve 1024 clients with MySQL, we get exactly the same performance as one client could get from one CPU.

In tabular format:

Interestingly, nearly all MySQL versions have reached its maximum performance at 16 threads, and the difference between the top and the bottom of the performance top list if 21 percents. This is how much MySQL could accelerate from 4.1 to 5.4. You can see that the speedup changes introduced in 5.4 are missing from 6.0, which is nearly is as fast as its predecessor (5.1)

Let's take a look at the read write test too!

5.4 do it's job quite good here, too. The curve falls down much sooner, than at the previous test, but this can be FreeBSD (as in everything other points, but it should be noted, that with PostgreSQL, FreeBSD has showed that it can do better).

4.1 shows the same weird pattern, but with a bigger hollow, and at many threads an obscure speedup.

We must say in favor of MySQL, that besides it's read only performance is only a fraction of PostgreSQL's, the performance degradation between the RO and RW tests are much smaller:

I think it's safe to say that PostgreSQL is better for reading data, while MySQL can be good, if you have to change them too.

Tabular format:

It can be seen that more features doesn't explicitly mean lower performance, because each of the tested MySQL versions have had more performance than the previous.

The configuration used was:

[mysqld]
datadir = /mnt/mysql
port = 3306
socket = /tmp/mysql.sock
skip-locking
max-connections = 3500
set-variable = key_buffer=1600M
transaction_isolation = REPEATABLE-READ
innodb_data_file_path = ibdata1:100M:autoextend
innodb_buffer_pool_size = 64G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 1G
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 300
innodb_locks_unsafe_for_binlog = 1
innodb_max_dirty_pages_pct = 90
innodb_thread_concurrency = 0
[isamchk]
set-variable = key_buffer=12M

A bejegyzés trackback címe:

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.