Re: MySQL 5.7 Innodb performans issue
- Date: Wed, 20 May 2015 15:15:51 -0300
- From: "wagnerbianchi.com" <me@xxxxxxxxxxxxxxxxx>
- Subject: Re: MySQL 5.7 Innodb performans issue
Can you share the SHOW ENGINE INNODB STATUS\G ?
*Wagner Bianchi, +55.31.8654.9510*
Oracle ACE Director
MySQL Certified Professional
Percona MySQL Forum <http://www.percona.com/forums/> Community V.I.P.
2015-05-20 15:07 GMT-03:00 Jørn Dahl-Stamnes <sql07@xxxxxxxxxxxxxxxx>:
> (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
> 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
> 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
> 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,
> 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
> 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
> 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
> server-id = 1
> # Innodb settings.
> innodb_open_files = 2048
> open_files_limit = 8096
> innodb_data_home_dir = /data/mysql/data
> innodb_data_file_path =
> 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
> 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