Hello

(again I must say).

Over a year ago I experienced a severe drop in the MySQL Innodb performance after ugrading to MySQL
5.6. I did not found any solution to that so I downgraded back to 5.5.33 and lived with in until
recently.

After a system disk crash I replaced the system disk with an identical disk, upgraded the test
server to Fedora Core 21 and installed MySQL 5.7.7 by downloading RPM files.

Now I'm facing the same problem with poor Innodb performance.

How have I messured the performance? I got a data collector system that get new data from mails
(stored as files). The system read the files, store data in the database and then prerform a lot of
calulcations on the data.

The performance is messured by how many jobs the system can process when putting months of mails
into a queue at once. While I was using 5.5.53 it processed about 2000 "jobs" per minute with a peak
up to 2500.

Each time I test the system I use an initial database and the same mail files, so the input is
always the same.

With 5.7.7 the performance has dropped by a factor 4 - about 500-600 jobs per minute.

I have played with the settings in my.cnf file but nothing seem to have any influence on the
performance. So I'm using more or less the same settings for both version.

Another thing I have noticed is that when running the test with MySQL 5.5.33, the hard disk LED was
mostly dark. But now, it is flashing like h**l. So it seems like the problem has to do with the disk
activity.

I would be very glad to get some feedback on this that would bring my performance back to "normal"
:)

About the server:
System disk (where the binlogs are stored) is a regular magnetic disk.

All Innodb files are stored on a SSD disk mounted with "defaults,
nouser_xattr,noatime,data=writeback,barrier=0"

The system got 32 Gb memory and have an AMD 8-core CPU (AMD FX-8120).



This is my.cnf I'm currently using:

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock

explicit_defaults_for_timestamp = TRUE

# Logging
slow_query_log_file = /var/log/mysql-slow.log
slow_query_log = 1
long_query_time = 10
log_queries_not_using_indexes = OFF

skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 32M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
max_connections = 50

join_buffer_size = 64M

# Replication Master Server (default)
# binary logging is required for replication
log-bin=/var/mysql/mysql-bin
server-id = 1
binlog_format=mixed

# Innodb settings.
innodb_open_files = 2048
open_files_limit = 8096
innodb_data_home_dir = /data/mysql/data
innodb_data_file_path = ibdata1:20G;ibdata2:20G;ibdata3:20G;ibdata4:20G:autoextend
innodb_file_per_table = 0
innodb_autoextend_increment = 256
innodb_log_group_home_dir = /data/mysql/data
innodb_buffer_pool_size = 25G
innodb_log_file_size = 300M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 128M

innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 16

innodb_fast_shutdown = 0

[mysql]
no-auto-rehash

=2D-
J=C3=B8rn Dahl-Stamnes
homepage: http://photo.dahl-stamnes.net/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql