Re: Crash after shutdown/restart
On Wednesday 22 January 2014 22:56, you wrote: Hi Jørn, But I must say I'm not very impressed by the speed. I'm running a test on an application that do a lot of reads and writes queries and the general performance has dropped to 50% of the what I had in 5.5.20. I would say that this sort of performance drop is not typical. Some users have reported a smaller performance loss in single threaded workloads in 5.6. But dropping from an average of 1800 jobs per minute down to 300? I don't think that should be expected. A few weeks ago I stopped the test and restored the initial database starting the test over again. Now the performance was back to 1700 jobs per minute, but it slowly went down as the test ran. Yesterday it was down to 300 per minutes and still (but very slowly) dropped. Yesterday I did the following: * stopped the test * dumped all databases * stopped the mysql server 5.6 * Downloaded 5.5.33-log source and installed it * Removed all inodb* and ib_log* files * Removed all databases * Started and initialized mysql * Restored all databases * Started the test where I left it. After a few hours I could see that the performance was back to normal - 1800 - 2000 jobs per minute. There is no sign of drop in performace so far. Please explain. -- 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: Crash after shutdown/restart
Hi Jørn, I would say that this sort of performance drop is not typical. Some users have reported a smaller performance loss in single threaded workloads in 5.6. But dropping from an average of 1800 jobs per minute down to 300? I don't think that should be expected. I would agree with you here. A few weeks ago I stopped the test and restored the initial database starting the test over again. Now the performance was back to 1700 jobs per minute, but it slowly went down as the test ran. Yesterday it was down to 300 per minutes and still (but very slowly) dropped. Yesterday I did the following: * stopped the test * dumped all databases * stopped the mysql server 5.6 * Downloaded 5.5.33-log source and installed it * Removed all inodb* and ib_log* files * Removed all databases * Started and initialized mysql * Restored all databases * Started the test where I left it. After a few hours I could see that the performance was back to normal - 1800 - 2000 jobs per minute. There is no sign of drop in performace so far. Please explain. I want to suspect that there might be a specific query regression (where 5.6 has introduced a new feature, and you fall in an edge case where it is being optimized incorrectly). The way to deduce this is to run EXPLAIN for key queries in MySQL 5.6 and 5.5, and compare for differences: https://dev.mysql.com/doc/refman/5.6/en/explain.html When you have one, there are a lot of people on the list that would be happy to pair this down to a test case, and file a bug. There is also a switch to disable specific optimizations, so you may have an easy work around that would allow you to restore back on 5.6: https://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Crash after shutdown/restart
On Tuesday 14 January 2014 21:51, Jesper Wisborg Krogh wrote: Hi Jørn, On 15/01/2014 04:36, Jørn Dahl-Stamnes wrote: 140114 18:20:08 InnoDB: Error: data file /data/mysql/data/ibdata3 uses page size 1024, 140114 18:20:08 InnoDB: but the only supported page size in this release is=16384 140114 18:20:08 InnoDB: Could not open or create data files. That error is typical for bug http://bugs.mysql.com/bug.php?id=64160 which was present in 5.5.20 and 5.5.21 (see also http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-22.html). So try to upgrade to 5.5.22 or later (I'll recommend 5.5.35) and see if that fixes the issue. Thanks a lot :) I installed 5.6.15 from source and things seems to work OK after a restore. But I must say I'm not very impressed by the speed. I'm running a test on an application that do a lot of reads and writes queries and the general performance has dropped to 50% of the what I had in 5.5.20. I have tried misc combination of innodb_xxx settings but without much luck. 5.6.15 is just slow compared 5.5.20. A short description of the application being tested: The application read a lot of data from files with misc formats. The files are read, parsed (based on the format in each file) and then data is written to the database (raw data). Based on the content of each file, computation jobs are created in a queue (implemented as a table in the database). And then a different process will start doing calculation on the raw and create new data which is written to other tables. After eacn calculation job is done, a record is added in the queue log table. All tables involved are innodb. It's the queue log table that I use to find out how many jobs the system is able to process each minute. A full test takes 2 weeks creating over 15 million jobs. Before each test a initial database is restored and then a set of files are feed to the application. With 5.5.20 the application was able to process an average of 1800 jobs per minute (with peeks up to 2000/min). With 5.6.15 it's around 700-800 jobs per minute and never over 1000/min. Except for the database version everything are the same - the same initial database, the same datafiles and the same order of processing (eventually the result after a full test will be the same). The setup show below gave me 677 jobs per minute in average. I later changed innodb_flush_log_at_trx_commit to 2. Thag gave me 753 jobs per minute. Setting it to 1 gave me 695 jobs per minute. Still long way to go to reach the 1800 jobs per minute. So my question is: What's wrong? Is 5.6.15 slower or? The test machine: - Fedora Core 16 (no X-windows) 8 core AMD (FX-8120) at 3100 Mhz. 32 Gb memory 120 Gb SSD disk for the database (mounted with ext4 and defaults) (*) 1 Tb disk for datafiles and bin log files. *: I'm going to change this later to noatime,data=writeback,barrier=0,nobh and test again. Initial my.cnf: y.cnf: [mysqld] port= 3306 socket = /tmp/mysql.sock explicit_defaults_for_timestamp = TRUE 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 query_cache_size = 32M thread_concurrency = 14 max_connections = 50 log-bin=/var/mysql/mysql-bin server-id = 1 binlog_format=mixed # Open files. 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_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 = 8M innodb_flush_log_at_trx_commit = 0 innodb_support_xa = 0 innodb_flush_method = O_DIRECT innodb_lock_wait_timeout= 50 innodb_thread_concurrency = 14 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
Re: Crash after shutdown/restart
Hi Jørn, But I must say I'm not very impressed by the speed. I'm running a test on an application that do a lot of reads and writes queries and the general performance has dropped to 50% of the what I had in 5.5.20. I would say that this sort of performance drop is not typical. Some users have reported a smaller performance loss in single threaded workloads in 5.6. It's possible to redeem some of this performance loss by tuning Performance Schema, with the caveat that you will lose some visibility into diagnostics: http://dev.mysql.com/doc/refman/5.6/en/performance-schema-startup-configuration.html It's not clear from your description if the test is run in parallel-threads, but this is something you may want to research. May I also suggest commenting out/assuming some defaults for some of your config settings. 5.6 has much better defaults, and it is always better to 'maintain less customization' yourself: key_buffer_size = 384M 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 query_cache_size = 32M thread_concurrency = 14 I also don't typically recommend these settings, but there will be some cases when they are warranted: innodb_thread_concurrency = 14 innodb_support_xa = 0 innodb_fast_shutdown= 0 - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Crash after shutdown/restart
Hello, Got a test server with version 5.5.20. I wanted to unmount/mount the disk where the innodb files was located, so I did a shutdown followed by unmount, then a mount before I tried to start the MySQL server. But it did not work as shown in the log below. I wanted to unmount the disk since I wanted to change the 'defaults' in /etc/fstab with 'defaults,noatime,data=writeback,barrier=0,nobh,errors=remount-ro'. What could cause this? Guess I have to recreate the files and start all over again? # Older message from the error file showing the version. Version: '5.5.20-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution # Shutdown messages 140114 18:14:53 [Note] Event Scheduler: Purging the queue. 0 events 140114 18:14:53 InnoDB: Starting shutdown... 140114 18:17:58 InnoDB: Shutdown completed; log sequence number 2230197670580 140114 18:17:58 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete # Restart after umount/mount. 140114 18:17:59 mysqld_safe mysqld from pid file /usr/local/mysql/data/hostname.pid ended 140114 18:20:05 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 140114 18:20:05 InnoDB: The InnoDB memory heap is disabled 140114 18:20:05 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140114 18:20:05 InnoDB: Compressed tables use zlib 1.2.5 140114 18:20:05 InnoDB: Initializing buffer pool, size = 25.0G 140114 18:20:08 InnoDB: Completed initialization of buffer pool 140114 18:20:08 InnoDB: Error: data file /data/mysql/data/ibdata3 uses page size 1024, 140114 18:20:08 InnoDB: but the only supported page size in this release is=16384 140114 18:20:08 InnoDB: Could not open or create data files. 140114 18:20:08 InnoDB: If you tried to add new data files, and it failed here, 140114 18:20:08 InnoDB: you should now edit innodb_data_file_path in my.cnf back 140114 18:20:08 InnoDB: to what it was, and remove the new ibdata files InnoDB created 140114 18:20:08 InnoDB: in this failed attempt. InnoDB only wrote those files full of 140114 18:20:08 InnoDB: zeros, but did not yet use them in any way. But be careful: do not 140114 18:20:08 InnoDB: remove old data files which contain your precious data! 140114 18:20:08 [ERROR] Plugin 'InnoDB' init function returned error. 140114 18:20:08 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140114 18:20:08 [ERROR] Unknown/unsupported storage engine: InnoDB 140114 18:20:08 [ERROR] Aborting -- 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: Crash after shutdown/restart
Hi Jørn, On 15/01/2014 04:36, Jørn Dahl-Stamnes wrote: 140114 18:20:08 InnoDB: Error: data file /data/mysql/data/ibdata3 uses page size 1024, 140114 18:20:08 InnoDB: but the only supported page size in this release is=16384 140114 18:20:08 InnoDB: Could not open or create data files. That error is typical for bug http://bugs.mysql.com/bug.php?id=64160 which was present in 5.5.20 and 5.5.21 (see also http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-22.html). So try to upgrade to 5.5.22 or later (I'll recommend 5.5.35) and see if that fixes the issue. Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
