Received: by 10.52.88.174 with SMTP id bh14mr16623160vdb.6.1338215530627;
Mon, 28 May 2012 07:32:10 -0700 (PDT)
X-BeenThere: codership-team@googlegroups.com
Received: by 10.52.93.230 with SMTP id cx6ls2435176vdb.6.gmail; Mon, 28 May
2012 07:32:09 -0700 (PDT)
Received: by 10.52.155.207 with SMTP id vy15mr363425vdb.13.1338215529389;
Mon, 28 May 2012 07:32:09 -0700 (PDT)
Date: Mon, 28 May 2012 07:32:08 -0700 (PDT)
From: DRivard <dominick.riv...@gmail.com>
To: codership-team@googlegroups.com
Message-Id: <22500078-2d53-4427-ba3c-bbcfcf70e954@googlegroups.com>
Subject: Galera configuration tips required for best usage
MIME-Version: 1.0
Content-Type: multipart/mixed;
boundary="----=_Part_94_17048907.1338215528995"
------=_Part_94_17048907.1338215528995
Content-Type: multipart/alternative;
boundary="----=_Part_95_19698890.1338215528995"
------=_Part_95_19698890.1338215528995
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Hi,
I finally got the new hardware for my cluster so now we are running a full
3 nodes cluster using HP servers with Intel Xeon CPU X5690@ 3.47GHz, 156GB
of RAM and a RAID-5 array of 7.4TB and the file system is XFS.
I set up the cluster using Ubuntu 12.04 and now I am looking for the best
configuration of my.cnf and wsrep.cnf.
my my.cnf file looks like :
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
skip-external-locking
bind-address = 0.0.0.0
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 512M
query_cache_size = 512M
expire_logs_days = 10
max_binlog_size = 100M
event-scheduler = OFF
max_connections = 500
default-storage-engine = InnoDB
log-error = /var/log/mysql/error.log
innodb_file_per_table
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 128G
innodb_log_buffer_size = 256M
innodb_log_file_size = 512M
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
my wsrep.cnf looks like:
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="datavalet_cluster"
wsrep_cluster_address="gcomm://*.*.*.*:4567"
wsrep_node_address=*.*.*.*
wsrep_slave_threads=1
wsrep_provider_options="gcache.size=25G"
wsrep_certify_nonPK=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_debug=0
wsrep_convert_LOCK_to_trx=0
wsrep_retry_autocommit=1
wsrep_auto_increment_control=1
wsrep_drupal_282555_workaround=0
wsrep_causal_reads=0
wsrep_notify_cmd=
wsrep_sst_method=mysqldump
wsrep_sst_auth=root:********
How can I make this more efficient for production (most of the the
activities are insert in DB and some times per month it is about reporting
on about 15GB tables)?
Thank you for your help.
Dominick
------=_Part_95_19698890.1338215528995
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Hi,<div><br></div><div>I finally got the new hardware for my cluster so now=
we are running a full 3 nodes cluster using HP servers with Intel Xeo=
n CPU X5690@ 3.47GHz, 156GB of RAM and a RAID-5 array of 7.4TB and the file=
system is XFS.</div><div>I set up the cluster using Ubuntu 12.04 and now I=
am looking for the best configuration of my.cnf and wsrep.cnf.</div><div><=
br></div><div>my my.cnf file looks like :</div><div><br></div><div><div>[my=
sqld_safe]</div><div>socket =3D /var/run/=
mysqld/mysqld.sock</div><div>nice =
=3D 0</div><div><br></div><div>[mysqld]</div><div>skip-external-locking</di=
v><div>bind-address =3D 0.0.0.0</d=
iv><div>key_buffer =3D 16M<=
/div><div>max_allowed_packet =3D 16M</div><div>thread_s=
tack =3D 192K</div><div>thread_cac=
he_size =3D 8</div><div>myisam-recover &=
nbsp; =3D BACKUP</div><div>query_cache_limit =
=3D 512M</div><div>query_cache_size =3D 512M</di=
v><div>expire_logs_days =3D 10</div><div>max_bin=
log_size =3D 100M</div><div>event-scheduler &nb=
sp; =3D OFF</div><div>max_connections &n=
bsp; =3D 500</div><div>default-storage-engine =3D InnoDB</div>=
<div>log-error =3D /var/lo=
g/mysql/error.log</div><div>innodb_file_per_table</div><div>innodb_flush_lo=
g_at_trx_commit =3D 0</div><div>innodb_buffer_pool_size =
=3D 128G</div><div>innodb_log_buffer_size &nbs=
p; =3D 256M</div><div>innodb_log_file_size  =
; =3D 512M</div><div>innodb_thread_concurrency &=
nbsp; =3D 8</div><div>innodb_flush_method  =
; =3D O_DIRECT</div><div>[mysqldump]</div><div>quick</div><di=
v>quote-names</div><div>max_allowed_packet =3D 16M</div=
><div>[mysql]</div><div>#no-auto-rehash # faster start of mysql but no tab =
completition</div><div>[isamchk]</div><div>key_buffer =
=3D 16M</div><div>!includedir /etc/mysql/conf.d/=
</div></div><div><br></div><div>my wsrep.cnf looks like:</div><div><br></di=
v><div><div>[mysqld]</div><div>binlog_format=3DROW</div><div>default-storag=
e-engine=3Dinnodb</div><div>innodb_autoinc_lock_mode=3D2</div><div>innodb_l=
ocks_unsafe_for_binlog=3D1</div><div>query_cache_size=3D0</div><div>query_c=
ache_type=3D0</div><div>bind-address=3D0.0.0.0</div><div><br></div><div>wsr=
ep_provider=3D/usr/lib/galera/libgalera_smm.so</div><div>wsrep_cluster_name=
=3D"datavalet_cluster"</div><div>wsrep_cluster_address=3D"gcomm://*.*.*.*:4=
567"</div><div>wsrep_node_address=3D*.*.*.*</div><div>wsrep_slave_threads=
=3D1</div><div>wsrep_provider_options=3D"gcache.size=3D25G"</div><div>wsrep=
_certify_nonPK=3D1</div><div>wsrep_max_ws_rows=3D131072</div><div>wsrep_max=
_ws_size=3D1073741824</div><div>wsrep_debug=3D0</div><div>wsrep_convert_LOC=
K_to_trx=3D0</div><div>wsrep_retry_autocommit=3D1</div><div>wsrep_auto_incr=
ement_control=3D1</div><div>wsrep_drupal_282555_workaround=3D0</div><div>ws=
rep_causal_reads=3D0</div><div>wsrep_notify_cmd=3D</div><div>wsrep_sst_meth=
od=3Dmysqldump</div><div>wsrep_sst_auth=3Droot:********</div></div><div><br=
></div><div>How can I make this more efficient for production (mo=
st of the the activities are insert in DB and some times per month it is ab=
out reporting on about 15GB tables)?</div><div><br></div><div>Thank you for=
your help.</div><div>Dominick</div>
------=_Part_95_19698890.1338215528995--
------=_Part_94_17048907.1338215528995--
Message from discussion Galera configuration tips required for best usage
| Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy |
| ©2013 Google |