maria-discuss team mailing list archive

[Jonathan Gazeley <Jonathan.Gazeley@xxxxxxxxxxxxx>]

Hi folks,

I'm reasonably experienced with MySQL and MMM for cluster management but 
now I am working on a project to replace this with a MariaDB Galera cluster.

Basic system info:

[jg4461@db3 ~]$ uname -a
Linux db3.resnet.bris.ac.uk 2.6.32-431.11.2.el6.x86_64 #1 SMP Tue Mar 25 
19:59:55 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux
[jg4461@db3 ~]$ cat /etc/redhat-release
CentOS release 6.5 (Final)
[jg4461@db3 ~]$ mysql -V
mysql  Ver 15.1 Distrib 5.5.36-MariaDB, for Linux (x86_64) using 
readline 5.1

I've set up a Galera prototype with two nodes and I got the clustering 
working. Then I decided to add a load of config options from my old 
MySQL install to the MariaDB install, and now it fails to start up, but 
doesn't give an obvious error message. The options were mostly to do 
with InnoDB buffers etc. This is the output in the log file 
/var/lib/mysql/db3.resnet.bris.ac.uk.err, the init script hangs for ages 
after this without giving any further output.

140331 15:07:49 mysqld_safe Starting mysqld daemon with databases from 
/var/lib/mysql
140331 15:07:49 mysqld_safe WSREP: Running position recovery with 
--log_error='/var/lib/mysql/wsrep_recovery.mlbT89' 
--pid-file='/var/lib/mysql/db3.resnet.bris.ac.uk-recover.pid'
140331 15:07:51 mysqld_safe WSREP: Failed to recover position:
'140331 15:07:49 InnoDB: The InnoDB memory heap is disabled
140331 15:07:49 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140331 15:07:49 InnoDB: Compressed tables use zlib 1.2.3
140331 15:07:49 InnoDB: Using Linux native AIO
140331 15:07:49 InnoDB: Initializing buffer pool, size = 1.5G
140331 15:07:49 InnoDB: Completed initialization of buffer pool
140331 15:07:49 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140331 15:07:49  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
140331 15:07:49  InnoDB: Waiting for the background threads to start
140331 15:07:50 Percona XtraDB (http://www.percona.com) 
5.5.36-MariaDB-33.0 started; log sequence number 1607692
140331 15:07:50 [Note] Plugin 'FEEDBACK' is disabled.'

My MariaDB config file is attached. Please could someone point me in the 
right direction of where to go with debugging this. I can't find any 
other error message so I'm struggling to track down why the server can't 
start. Any advice gratefully accepted!

Thanks,
Jonathan

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

[mysqld]

# Mandatory settings: these settings are REQUIRED for proper galera cluster operation
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
# innodb_doublewrite=1 - this is the default and it should stay this way

# wsrep provider configuration: basic wsrep options
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=2G"
wsrep_cluster_name='resnet'

# Only print nodes if we are NOT db3
wsrep_cluster_address=gcomm://

# Become master if we ARE db3
wsrep_provider_options="pc.bootstrap=1"

wsrep_node_address='172.16.67.7'
wsrep_node_name='db3'

# http://www.percona.com/doc/percona-xtradb-cluster/5.5/wsrep-system-index.html#wsrep_sst_method
# http://www.codership.com/wiki/doku.php?id=sst_mysql
wsrep_sst_method=xtrabackup
#wsrep_sst_method=rsync
#wsrep_sst_method=mysqldump

wsrep_sst_auth=repl:******

# additional "frequently used" wsrep settings
#wsrep_node_incoming_address='192.168.10.2'

# This variable controls the number of threads that can apply replication transactions
# in parallel. Galera supports true parallel replication, replication that applies transactions
# in parallel only when it is safe to do so.
wsrep_slave_threads=2

# back_log is the number of connections the operating system can keep in
# the listen queue, before the MySQL connection manager thread has
# processed them. If you have a very high connection rate and experience
# "connection refused" errors, you might need to increase this value.
# Check your OS documentation for the maximum value of this parameter.
# Attempting to set back_log higher than your operating system limit
# will have no effect.
back_log = 100

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections = 120

# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_cache = 1024

# The maximum size of a query packet the server can handle as well as
# maximum query size server can process (Important when working with
# large BLOBs).  enlarged dynamically, for each connection.
max_allowed_packet = 16M

# The size of the cache to hold the SQL statements for the binary log
# during a transaction. If you often use big, multi-statement
# transactions you can increase this value to get more performance. All
# statements from transactions are buffered in the binary log cache and
# are being written to the binary log at once after the COMMIT.  If the
# transaction is larger than this value, temporary file on disk is used
# instead.  This buffer is allocated per connection on first update
# statement in transaction
binlog_cache_size = 16M

# Maximum allowed size for a single HEAP (in memory) table. This option
# is a protection against the accidential creation of a very large HEAP
# table which could otherwise use up all memory resources.
max_heap_table_size = 64M

# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY
# queries. If sorted data does not fit into the sort buffer, a disk
# based merge sort is used instead - See the "Sort_merge_passes"
# status variable. Allocated per thread if sort is needed.
sort_buffer_size = 8M

# This buffer is used for the optimization of full JOINs (JOINs without
# indexes). Such JOINs are very bad for performance in most cases
# anyway, but setting this variable to a large value reduces the
# performance impact. See the "Select_full_join" status variable for a
# count of full JOINs. Allocated per thread if full join is found
join_buffer_size = 64M

# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client's threads are put in the cache if there aren't
# more than thread_cache_size threads from before.  This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn't give a notable performance
# improvement if you have a good thread implementation.)
thread_cache_size = 8

# If your system supports the memlock() function call, you might want to
# enable this option while running MySQL to keep it locked in memory and
# to avoid potential swapping out in case of high memory pressure. Good
# for performance.
memlock

# Thread stack size to use. This amount of memory is always reserved at
# connection time. MySQL itself usually needs no more than 64K of
# memory, while if you use your own stack hungry UDF functions or your
# OS requires more stack for some operations, you might need to set this
# to a higher value.
thread_stack = 192K

# Set the default transaction isolation level. Levels available are:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = REPEATABLE-READ

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size = 64M

# Enable binary logging. This is required for acting as a MASTER in a
# replication configuration. You also need the binary log if you need
# the ability to do point in time recovery from your latest backup.
#log-bin=mysql-bin

# Log slow queries. Slow queries are queries which take more than the
# amount of time defined in "long_query_time" or which do not use
# indexes well, if log_long_format is enabled. It is normally good idea
# to have this turned on if you frequently add new queries to the
# system.
slow_query_log
#slow_query_log_file = /var/log/mysql-slow-queries.log

# All queries taking more than this amount of time (in seconds) will be
# trated as slow. Do not use "1" as a value here, as this will result in
# even very fast queries being logged from time to time (as MySQL
# currently measures time with second accuracy only).
long_query_time = 2

#*** MyISAM Specific options


# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size = 8M

# Size of the buffer used for doing full table scans of MyISAM tables.
# Allocated per thread, if a full scan is needed.
read_buffer_size = 4M

# When reading rows in sorted order after a sort, the rows are read
# through this buffer to avoid disk seeks. You can improve ORDER BY
# performance a lot, if set this to a high value.
# Allocated per thread, when needed.
read_rnd_buffer_size = 4M

# MyISAM uses special tree-like cache to make bulk inserts (that is,
# INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA
# INFILE) faster. This variable limits the size of the cache tree in
# bytes per thread. Setting it to 0 will disable this optimisation.  Do
# not set it larger than "key_buffer_size" for optimal performance.
# This buffer is allocated when a bulk insert is detected.
bulk_insert_buffer_size = 4M

# This buffer is allocated when MySQL needs to rebuild the index in
# REPAIR, OPTIMIZE, ALTER table statements as well as in LOAD DATA INFILE
# into an empty table. It is allocated per thread so be careful with
# large settings.
myisam_sort_buffer_size = 4M

# The maximum size of the temporary file MySQL is allowed to use while
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
# If the file-size would be bigger than this, the index will be created
# through the key cache (which is slower).
myisam_max_sort_file_size = 256M

# If a table has more than one index, MyISAM can use more than one
# thread to repair them by sorting in parallel. This makes sense if you
# have multiple CPUs and plenty of memory.
myisam_repair_threads = 4

# Automatically check and repair not properly closed MyISAM tables.
myisam_recover

# *** INNODB Specific options ***

# Additional memory pool that is used by InnoDB to store metadata
# information.  If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS.  As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size = 16M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size = 1498M

# InnoDB stores data in one or more data files forming the tablespace.
# If you have a single logical drive for your data, a single
# autoextending file would be good enough. In other cases, a single file
# per device is often a good choice. You can configure InnoDB to use raw
# disk partitions as well - please refer to the manual for more info
# about this.
#innodb_data_file_path = ibdata1:10M:autoextend

# Make one innodb file per table
innodb_file_per_table

# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency = 16

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit = 1

# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size = 8M

# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size = 256M

# Total number of files in the log group. A value of 2-3 is usually good
# enough.
innodb_log_files_in_group = 3

# Maximum allowed percentage of dirty pages in the InnoDB buffer pool.
# If it is reached, InnoDB will start flushing them out agressively to
# not run out of clean pages at all. This is a soft limit, not
# guaranteed to be held.
innodb_max_dirty_pages_pct = 90

# How long an InnoDB transaction should wait for a lock to be granted
# before being rolled back. InnoDB automatically detects transaction
# deadlocks in its own lock table and rolls back the transaction. If you
# use the LOCK TABLES command, or other transaction-safe storage engines
# than InnoDB in the same transaction, then a deadlock may arise which
# InnoDB cannot notice. In cases like this the timeout is useful to
# resolve the situation.
innodb_lock_wait_timeout = 120


# These two groups are only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

[mariadb-5.5]

[sst]
# Galera State Snapshot Transfer

# Format used by XtraBackup to copy data
# valid options: xbstream, tar
# http://www.percona.com/doc/percona-xtradb-cluster/5.5/manual/xtrabackup_sst.html#streamfmt
streamfmt=xbstream

# Transport container for the above
# valid options: socat, nc
# http://www.percona.com/doc/percona-xtradb-cluster/5.5/manual/xtrabackup_sst.html#transferfmt
transferfmt=socat

[xtrabackup]
datadir=/var/lib/mysql