Re: checking for repetitive queries
https://www.thomas-krenn.com/en/wiki/MySQL_Performance_Tuning#Tuning_Tools On Mon, May 18, 2015 23:44, Reindl Harald wrote: Am 18.05.2015 um 23:37 schrieb Steve Quezadas: I want to make sure my caching system is working properly and I want to make sure my mysql server isnt being held up by repetitive queries (ie like the side products table that appears on every web page). I'm pretty sure I cached the site pretty well, but want to make sure that I didn't miss anything. Is there some sort of tool that allows me to check for repetitive queries? likely nobody knows what you are talking about http://www.catb.org/esr/faqs/smart-questions.html#beprecise -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL 5.7 Innodb performans issue
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
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: 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
Re: MySQL 5.7 Innodb performans issue
On Wednesday, May 20, 2015, wagnerbianchi.com wrote: Can you share the SHOW ENGINE INNODB STATUS\G ? Sure, here it is. *** 1. row *** Type: InnoDB Name: Status: = 2015-05-20 20:29:56 0x7f9a4c189700 INNODB MONITOR OUTPUT = Per second averages calculated from the last 10 seconds - BACKGROUND THREAD - srv_master_thread loops: 204 srv_active, 0 srv_shutdown, 2320 srv_idle srv_master_thread log flush and writes: 2524 -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 5507 OS WAIT ARRAY INFO: signal count 5827 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 1901, rounds 3750, OS waits 1849 RW-excl spins 108, rounds 1784098, OS waits 8 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 0.00 mutex, 1.97 RW-shared, 16519.43 RW-excl, 0.00 RW-sx TRANSACTIONS Trx id counter 1826118 Purge done for trx's n:o 1826115 undo n:o 0 state: running but idle History list length 1793 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421804350357096, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421804350355272, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 1826117, ACTIVE (PREPARED) 0 sec mysql tables in use 1, locked 1 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 MySQL thread id 12, OS thread handle 140329332676352, query id 123121 localhost 127.0.0.1 dahls query end UPDATE washperiod SET washperiodstatus_id=1 WHERE washperiod_id=127687474 FILE I/O I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 104317 OS file reads, 6881 OS file writes, 6297 OS fsyncs 164.08 reads/s, 16384 avg bytes/read, 21.90 writes/s, 21.90 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf: size 1, free list len 0, seg size 2, 38 merges merged operations: insert 130, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 53118839, node heap has 400 buffer(s) 783.22 hash searches/s, 854.11 non-hash searches/s --- LOG --- Log sequence number 137891623643 Log flushed up to 137891623643 Pages flushed up to 137884471017 Last checkpoint at 137884471017 0 pending log flushes, 0 pending chkp writes 4586 log i/o's done, 21.90 log i/o's/second -- BUFFER POOL AND MEMORY -- Total large memory allocated 27485798400 Dictionary memory allocated 1342452 Buffer pool size 1638225 Free buffers 1533395 Database pages 104430 Old database pages 38704 Modified db pages 2007 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 14, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 104305, created 125, written 1448 164.08 reads/s, 0.30 creates/s, 0.00 writes/s Buffer pool hit rate 992 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 50.39/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 104430, unzip_LRU len: 0 I/O sum[0]:cur[8], unzip sum[0]:cur[0] -- INDIVIDUAL BUFFER POOL INFO -- ---BUFFER POOL 0 Buffer pool size 204775 Free buffers 188541 Database pages 16183 Old database pages 5993 Modified db pages 587 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 2, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 16136, created 47, written 257 18.00 reads/s, 0.20 creates/s, 0.00 writes/s Buffer pool hit rate 996 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 7.50/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 16183, unzip_LRU len: 0 I/O sum[0]:cur[1], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 204775 Free buffers 192460 Database pages 12269 Old database pages 4548 Modified db pages 179 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 12269, created 0, written 153
Re: MySQL 5.7 Innodb performans issue
Hi Jørn, - The data collector system processing jobs, is it multi threaded? Sorry, forgot about that. No, it is not multi threaded. It is a PHP bases system using several script running sequently in an infinite loop. Each script taking care of part of the job of processing the data. Newer MySQL releases are often more scalable (= works better for multi-threaded applications). On a single threaded basis though, as functionality is added it is difficult to always keep the same performance numbers. When you mentioned that there was a regression in 5.6 as well, it might be interesting to compare to the most recent 5.6. There were some single-threaded regression fixes after the GA release. - Do you have a sample schema + set of queries we could look at? (We pay close attention to regressions.) This will be BIG, since it it so many different queries and tables. Not sure what you mean by We pay close attention to regressions”. Bugs that introduce a loss of performance have a higher priority factor applied to them. We are very interested to hear clear cases where an operation takes more time in MySQL 5.7 versus earlier releases. In terms of your configuration: I would usually recommend assuming the default values for some of the settings you’ve specified (table_open_cache, sort_buffer_size, thread_cache_size, innodb_log_buffer_size, innodb_thread_concurrency..). A 25G buffer pool on a 32G server with some of your other buffers being quite large is something you may need to look into too. I know, but sofare no swapping is taking place. The test server is used for testing this system only. And in 99% of the time, only one client is using the SQL server. This makes some sense based on your workload being single threaded as well. It looks from show engine innodb status that your server is just starting up, and caches are empty, so versus a 5.5 server that has been running for a while it will likely be slower. What you may be able to do to track a specific set of statements that take longer in 5.7, is convert a mysqlbinlog to sql (mysqlbinlog mybinlogfile output.sql) and replay it on a 5.5 and 5.7 server. If you can pair it down a little from there, then the output.sql file can be uploaded to bugs.mysql.com and evaluated. Thanks! - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
On Wednesday, May 20, 2015, Morgan Tocker wrote: Hi Jørn, Wagner’s point about SHOW ENGINE INNODB STATUS is a good one. A couple of other questions about your workload: - The data collector system processing jobs, is it multi threaded? Sorry, forgot about that. No, it is not multi threaded. It is a PHP bases system using several script running sequently in an infinite loop. Each script taking care of part of the job of processing the data. - Do you have a sample schema + set of queries we could look at? (We pay close attention to regressions.) This will be BIG, since it it so many different queries and tables. Not sure what you mean by We pay close attention to regressions. In terms of your configuration: I would usually recommend assuming the default values for some of the settings you’ve specified (table_open_cache, sort_buffer_size, thread_cache_size, innodb_log_buffer_size, innodb_thread_concurrency..). A 25G buffer pool on a 32G server with some of your other buffers being quite large is something you may need to look into too. I know, but sofare no swapping is taking place. The test server is used for testing this system only. And in 99% of the time, only one client is using the SQL server. -- 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
Re: MySQL 5.7 Innodb performans issue
I'd like to add to the Morgan's note that if you want to restrict the number of transactions inside InnoDB kernel to 16, you need at least configure the tickets... = http://www.pythian.com/blog/once-again-about-innodb-concurrency-tickets/ BTW, leave it as its default, IMHO, innodb_thread_concurrency=0, is better... -- *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:25 GMT-03:00 Morgan Tocker morgan.toc...@oracle.com: Hi Jørn, Wagner’s point about SHOW ENGINE INNODB STATUS is a good one. A couple of other questions about your workload: - The data collector system processing jobs, is it multi threaded? - Do you have a sample schema + set of queries we could look at? (We pay close attention to regressions.) In terms of your configuration: I would usually recommend assuming the default values for some of the settings you’ve specified (table_open_cache, sort_buffer_size, thread_cache_size, innodb_log_buffer_size, innodb_thread_concurrency..). A 25G buffer pool on a 32G server with some of your other buffers being quite large is something you may need to look into too. - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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: 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
Re: MySQL 5.7 Innodb performans issue
Hi Jørn, Wagner’s point about SHOW ENGINE INNODB STATUS is a good one. A couple of other questions about your workload: - The data collector system processing jobs, is it multi threaded? - Do you have a sample schema + set of queries we could look at? (We pay close attention to regressions.) In terms of your configuration: I would usually recommend assuming the default values for some of the settings you’ve specified (table_open_cache, sort_buffer_size, thread_cache_size, innodb_log_buffer_size, innodb_thread_concurrency..). A 25G buffer pool on a 32G server with some of your other buffers being quite large is something you may need to look into too. - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
On Wednesday, May 20, 2015, wagnerbianchi.com wrote: I'd like to add to the Morgan's note that if you want to restrict the number of transactions inside InnoDB kernel to 16, you need at least configure the tickets... = http://www.pythian.com/blog/once-again-about-innodb-concurrency-tickets/ BTW, leave it as its default, IMHO, innodb_thread_concurrency=0, is better... Changed it to 0 and restarted MySQL. Now the test will be running all night long... (a full test took 3+ weeks with 5.5 and I expect 12-14 weeks with the speed of 5.7). But as I wrote before, it seems like 5.7 causing a much higher disk load than 5.5 based on how the HD LED is flashing. It could be off for a long time (a second or even more) with 5.5 while now it is hardly off at all. On Wednesday, May 20, 2015, Morgan Tocker morgan.toc...@oracle.com wrote: It looks from show engine innodb status that your server is just starting up, and caches are empty, so versus a 5.5 server that has been running for a while it will likely be slower. It this is the case I have never experienced this behavior. The PHP scripts write output about everything that is happening. And the speed of the output tells me how fast things goes. With 5.5 the speed was much higher right from the start (after a reboot and starting up the test). -- 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
