Additionally, take a look on this http://wagnerbianchi.com/blog/?p=982 as you're running mysql on SSD
-- *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:15 GMT-03:00 wagnerbianchi.com <m...@wagnerbianchi.com>: > Can you share the SHOW ENGINE INNODB STATUS\G ? > > > -- > *Wagner Bianchi, +55.31.8654.9510 <%2B55.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 >> >> >
