List:General Discussion« Previous MessageNext Message »
From:Jørn Dahl-Stamnes Date:January 22 2014 9:00pm
Subject:Re: Crash after shutdown/restart
View as plain text  
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/
Thread
Crash after shutdown/restartJørn Dahl-Stamnes14 Jan 2014
  • Re: Crash after shutdown/restartJesper Wisborg Krogh14 Jan 2014
    • Re: Crash after shutdown/restartJørn Dahl-Stamnes22 Jan 2014
      • Re: Crash after shutdown/restartMorgan Tocker22 Jan 2014
        • Re: Crash after shutdown/restartJørn Dahl-Stamnes18 Feb 2014
          • Re: Crash after shutdown/restartMorgan Tocker18 Feb 2014