Can you share the SHOW ENGINE INNODB STATUS\G ?
-- *Wagner Bianchi, +55.31.8654.9510* Oracle ACE Director <https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541>, MySQL Certified Professional Percona MySQL Forum <http://www.percona.com/forums/> Community V.I.P. Email: m...@wagnerbianchi.com Skype: wbianchijr 2015-05-20 15:07 GMT-03:00 Jørn Dahl-Stamnes <sq...@dahl-stamnes.net>: > 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 > >
