Re: MySQL 5.7 Innodb performans issue
- Date: Wed, 20 May 2015 15:18:40 -0300
- From: "wagnerbianchi.com" <me@xxxxxxxxxxxxxxxxx>
- Subject: Re: MySQL 5.7 Innodb performans issue
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
MySQL Certified Professional
Percona MySQL Forum <http://www.percona.com/forums/> Community V.I.P.
2015-05-20 15:15 GMT-03:00 wagnerbianchi.com <me@xxxxxxxxxxxxxxxxx>:
> Can you share the SHOW ENGINE INNODB STATUS\G ?
> *Wagner Bianchi, +55.31.8654.9510 <%2B55.31.8654.9510>*
> Oracle ACE Director
> MySQL Certified Professional
> Percona MySQL Forum <http://www.percona.com/forums/> Community V.I.P.
> Email: me@xxxxxxxxxxxxxxxxx
> Skype: wbianchijr
> 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