Web lists-archives.com

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
<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
>
>