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/

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.