M K
asked on
MySQL Cluster with HA-Proxy over Master-Master Replication for Loadbalancing
dear experts,
i have a question: i must setup a mysql database loadbalancer.
now i have found much tutorials with master-master replication an ha-proxy as balancer.
but then i have found another tutorials with master-master galera cluster soltuion.
now my question:
what i should use?
on our servers there are now 30.000 peoples on the day. the system was is running is drupal.
my mainsystem is debian wheezy.
now i have 4 servers (2 active)
1. intel xeon 3,4 ghz, 32 gb ram, 4 x 1 tb enterprise hdd with raid 10
2. intel xeon 3,4 ghz, 32 gb ram, 4 x 1 tb enterprise hdd with raid 10
3. intel xeon 3,4 ghz, 32 gb ram, 4 x 400 ssd with raid 10
4. intel xeon 3,4 ghz, 32 gb ram, 4 x 400 ssd with raid 10
Currently only server 1 and 2 are active (the servers 3 and 4 was ordered).
the 1 is a webserver, and the 2 is a db server.
the load from the webserver is currently about 30%, the dbserver is heavy loaded...
the website access is very slow (10-15 seconds).
now that is my solution:
server 1 + 2 = webserver loadbalancer
main question: how i can replicate website data throw the both server,
how i can do a fix for session sickness?
second question, db: what is now better as cluster (for speed) a real cluster solution,
or master-master replication with ha proxy?
my msql database is currently 1 gigabyte (each month the database grows up about 100 mb).
how i can optimize my my.cnf configuration file for mysql? how i can find out what my db does?
i mean write, read... and where i must do changes in the configuration?
do you have some infos (any possible tutorials) for me how i can do that to get the website working fine?
thank you
many greets
mk
i have a question: i must setup a mysql database loadbalancer.
now i have found much tutorials with master-master replication an ha-proxy as balancer.
but then i have found another tutorials with master-master galera cluster soltuion.
now my question:
what i should use?
on our servers there are now 30.000 peoples on the day. the system was is running is drupal.
my mainsystem is debian wheezy.
now i have 4 servers (2 active)
1. intel xeon 3,4 ghz, 32 gb ram, 4 x 1 tb enterprise hdd with raid 10
2. intel xeon 3,4 ghz, 32 gb ram, 4 x 1 tb enterprise hdd with raid 10
3. intel xeon 3,4 ghz, 32 gb ram, 4 x 400 ssd with raid 10
4. intel xeon 3,4 ghz, 32 gb ram, 4 x 400 ssd with raid 10
Currently only server 1 and 2 are active (the servers 3 and 4 was ordered).
the 1 is a webserver, and the 2 is a db server.
the load from the webserver is currently about 30%, the dbserver is heavy loaded...
the website access is very slow (10-15 seconds).
now that is my solution:
server 1 + 2 = webserver loadbalancer
main question: how i can replicate website data throw the both server,
how i can do a fix for session sickness?
second question, db: what is now better as cluster (for speed) a real cluster solution,
or master-master replication with ha proxy?
my msql database is currently 1 gigabyte (each month the database grows up about 100 mb).
how i can optimize my my.cnf configuration file for mysql? how i can find out what my db does?
i mean write, read... and where i must do changes in the configuration?
do you have some infos (any possible tutorials) for me how i can do that to get the website working fine?
thank you
many greets
mk
ASKER
Hello Tomas Helgi,
thanks for you reply!
okay, i will activate the slow query log.
so i have done a run with mysqltuner:
mysqltuner says:
-------- General Statistics --------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-0+wheezy1
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics --------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 106M (Tables: 269)
[--] Data in InnoDB tables: 7G (Tables: 3443)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 3468
-------- Security Recommendations --------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics --------------------------
[--] Up for: 4d 18h 39m 48s (1B q [2K qps], 1M conn, TX: 302B, RX: 196B)
[--] Reads / Writes: 37% / 63%
[--] Total buffers: 12.4G global + 2.7M per thread (500 max threads)
[!!] Maximum possible memory usage: 13.7G (87% of installed RAM)
[OK] Slow queries: 0% (187K/1B)
[OK] Highest usage of available connections: 73% (365/500)
[OK] Key buffer size / total MyISAM indexes: 32.0M/152.6M
[OK] Key buffer hit rate: 99.9% (3B cached / 1M reads)
[OK] Query cache efficiency: 85.8% (442M cached / 515M selects)
[!!] Query cache prunes per day: 4241452
[OK] Sorts requiring temporary tables: 0% (76K temp sorts / 33M sorts)
[!!] Joins performed without indexes: 10255
[OK] Temporary tables created on disk: 0% (78K on disk / 8M total)
[OK] Thread cache hit rate: 98% (13K created / 1M connections)
[!!] Table cache hit rate: 0% (400 open / 379K opened)
[OK] Open file limit used: 3% (97/2K)
[OK] Table locks acquired immediately: 99% (205M immediate / 205M locks)
[OK] InnoDB data size / buffer pool: 7.3G/12.0G
-------- Recommendations --------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 100M)
join_buffer_size (> 128.0K, or always use indexes with joins)
table_cache (> 400)
currently the Server load is:
top - 10:29:29 up 174 days, 23:05, 1 user, load average: 31,24, 17,56, 17,01
Tasks: 220 total, 1 running, 219 sleeping, 0 stopped, 0 zombie
%Cpu(s): 97,4 us, 1,5 sy, 0,0 ni, 0,6 id, 0,2 wa, 0,0 hi, 0,4 si, 0,0 st
KiB Mem: 16471916 total, 14058328 used, 2413588 free, 146844 buffers
KiB Swap: 21229564 total, 115880 used, 21113684 free, 2973840 cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
17546 mysql 20 0 14,1g 9,5g 5496 S 2377 60,6 81515:19 mysqld
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
17546 mysql 20 0 14,1g 9,5g 5496 S 254 60,6 81447:26 mysqld
what do you mean?
thank you!
thanks for you reply!
okay, i will activate the slow query log.
so i have done a run with mysqltuner:
mysqltuner says:
-------- General Statistics --------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-0+wheezy1
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics --------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 106M (Tables: 269)
[--] Data in InnoDB tables: 7G (Tables: 3443)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 3468
-------- Security Recommendations --------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics --------------------------
[--] Up for: 4d 18h 39m 48s (1B q [2K qps], 1M conn, TX: 302B, RX: 196B)
[--] Reads / Writes: 37% / 63%
[--] Total buffers: 12.4G global + 2.7M per thread (500 max threads)
[!!] Maximum possible memory usage: 13.7G (87% of installed RAM)
[OK] Slow queries: 0% (187K/1B)
[OK] Highest usage of available connections: 73% (365/500)
[OK] Key buffer size / total MyISAM indexes: 32.0M/152.6M
[OK] Key buffer hit rate: 99.9% (3B cached / 1M reads)
[OK] Query cache efficiency: 85.8% (442M cached / 515M selects)
[!!] Query cache prunes per day: 4241452
[OK] Sorts requiring temporary tables: 0% (76K temp sorts / 33M sorts)
[!!] Joins performed without indexes: 10255
[OK] Temporary tables created on disk: 0% (78K on disk / 8M total)
[OK] Thread cache hit rate: 98% (13K created / 1M connections)
[!!] Table cache hit rate: 0% (400 open / 379K opened)
[OK] Open file limit used: 3% (97/2K)
[OK] Table locks acquired immediately: 99% (205M immediate / 205M locks)
[OK] InnoDB data size / buffer pool: 7.3G/12.0G
-------- Recommendations --------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 100M)
join_buffer_size (> 128.0K, or always use indexes with joins)
table_cache (> 400)
currently the Server load is:
top - 10:29:29 up 174 days, 23:05, 1 user, load average: 31,24, 17,56, 17,01
Tasks: 220 total, 1 running, 219 sleeping, 0 stopped, 0 zombie
%Cpu(s): 97,4 us, 1,5 sy, 0,0 ni, 0,6 id, 0,2 wa, 0,0 hi, 0,4 si, 0,0 st
KiB Mem: 16471916 total, 14058328 used, 2413588 free, 146844 buffers
KiB Swap: 21229564 total, 115880 used, 21113684 free, 2973840 cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
17546 mysql 20 0 14,1g 9,5g 5496 S 2377 60,6 81515:19 mysqld
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
17546 mysql 20 0 14,1g 9,5g 5496 S 254 60,6 81447:26 mysqld
what do you mean?
thank you!
Hi!
You should run OPTIMIZE TABLE on all those 3468 tables that needs to be optimized (or the whole database ).
Adjust these variables that mysqltuner mentions
query_cache_size (> 100M)
join_buffer_size (> 128.0K, or always use indexes with joins)
table_cache (> 400)
to some values slightly above the recommended values.
[!!] Joins performed without indexes: 10255
Investigate the slow queries and see if you can add missing indexes to the tables involved to speed up the queries.
Regards,
Tomas Helgi
You should run OPTIMIZE TABLE on all those 3468 tables that needs to be optimized (or the whole database ).
Adjust these variables that mysqltuner mentions
query_cache_size (> 100M)
join_buffer_size (> 128.0K, or always use indexes with joins)
table_cache (> 400)
to some values slightly above the recommended values.
[!!] Joins performed without indexes: 10255
Investigate the slow queries and see if you can add missing indexes to the tables involved to speed up the queries.
Regards,
Tomas Helgi
ASKER
Thank You!
And what i should set variable now?
currently set:
query_cache_size = 100M
join_buffer_size is not set
table_cache = 400
Thanks
And what i should set variable now?
currently set:
query_cache_size = 100M
join_buffer_size is not set
table_cache = 400
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Tomas!!!
You are facing two problems. A need for load balancing due to many users and heavy workload on the db due to forenamed reason and query-perfomance issues.
For tuning mysql I recommend ( as you are on Linux/unix system) to use the mysqltuner utility tool.
It will give you some idea where to tune. http://mysqltuner.com/
Also you should enable slow-query log to capture queries that are running slow. That is mostly due to missing indexes or
poorly written queries.
In newer versions of MySQL and MariaDB you have the Performance Schema that aids you in your tuning
http://dev.mysql.com/doc/refman/5.6/en/performance-schema-statement-digests.html
http://mysqlintheenterprise.com/2013/03/21/a-visual-guide-to-the-mysql-performance-schema/
MariaDB Galera Cluster with HAProxy looks to me as a very good solution although I haven't tested it thoroughly yet. It is a master-master replication and recommended setup is a 3 node cluster as minimum.
Note that InnoDB engine is the engine used in the cluster replication.
https://blog.mariadb.org/installing-mariadb-galera-cluster-on-debian-ubuntu/
http://blog.networkpresence.co/?p=4297
Regards,
Tomas Helgi