Web lists-archives.com

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
<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: me@xxxxxxxxxxxxxxxxx
Skype: wbianchijr

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
> <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: me@xxxxxxxxxxxxxxxxx
> Skype: wbianchijr
>
> 2015-05-20 15:07 GMT-03:00 Jørn Dahl-Stamnes <sql07@xxxxxxxxxxxxxxxx>:
>
> 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
>>
>>
>