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 -- Jørn 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
