|
|
| Author |
Message |
oxo-oxo
Cacti User
Joined: 30 Aug 2007
Posts: 106
Location: Silkeborg, Denmark
|
 Posted: Wed Nov 21, 2007 6:42 am Post subject: MySQL - there and back again |
 |
|
Hi
At the last European Cacti meeting, the question about MySQL and reliability came up.
So, as the forum died because of failure to connect to MySQL server, I thought it would be nice to "talk" about MySQL "reliability" in general and it's use in Cacti.
This may have been talked about before, but let's see if this "Not a feature request" thread will give something intresting ...
The next couple of posts get edited "as time goes by"
Small conclussion ...
I like circular replication contra cluster
- application needs fewer changes...?
- not fully clear on what might need to be changed i Cacti as yet Edit: nothing!
Last edited by oxo-oxo on Wed Oct 22, 2008 3:45 pm; edited 4 times in total |
|
| Back to top |
|
 |
oxo-oxo
Cacti User
Joined: 30 Aug 2007
Posts: 106
Location: Silkeborg, Denmark
|
| Posted: Wed Nov 21, 2007 6:42 am Post subject: |
|
|
MySQL
From Internet links ...:
Replicaction
- Offsite backup
- warm spare
- "make backups easier" - stop slave - make backup - start slave
- load balancing scaling (hmm: check this -> circular replication ...?)
- put data closer to the user
- Master R/W Slave R/O
Cluster
"One primary node and a bunch of slaves"
- requires application aware of cluster ...?
- the "usual" benifits of a cluster
(Thanks to evil_steve for starter input on irc )
Link list:
http://en.wikipedia.org/wiki/MySQL_Cluster
wiki rules ok
http://dev.mysql.com/tech-resources/articles/mysql-cluster-for-two-servers.html
| Quote: | | This HOWTO was designed for a classic setup of two servers behind a loadbalancer. The aim is to have true redundancy - either server can be unplugged and yet the site will remain up. |
http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-limitations.html
| Quote: | | In the sections that follow, we discuss known limitations in MySQL 5.0 Cluster releases as compared with the features available when using the MyISAM and InnoDB storage engines. |
http://dev.mysql.com/doc/refman/5.0/en/replication.html
http://dev.mysql.com/doc/refman/5.0/en/replication-faq.html#qandaitem-16-3-4-5
http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
A really good link "Circular replication"
| Quote: | You may know about the MySQL Cluster, which is a complex architecture to achieve high availability and performance. One of the advantages of MySQL Cluster is that each node is a peer to the others, whereas in a normal replicating system you have a master and many slaves, and applications must be careful to write only to the master.
The main disadvantages of MySQL Cluster are (as of MySQL 5.0):
The database is in memory only, thus requiring more resources than a normal MySQL database. (MySQL 5.1 introduces table spaces, with the capability of storing nonindexed data on disk.)
Some normal features are not available, such as full-text searches, referential integrity, and transaction isolation levels higher than READ COMMITTED.
There are some cases where the MySQL Cluster is the perfect solution, but for the vast majority, replication is still the best choice.
Replication, too, has its problems, though:
There is a fastidious distinction between master and slaves. Your applications must be replication-aware, so that they will write on the master and read from the slaves. It would be so nice to have a replication array where you could use all the nodes in the same way, and every node could be at the same time master and slave.
There is the fail-over problem. When the master fails, it's true that you have the slaves ready to replace it, but the process of detecting the failure and acting upon it requires the administrator's intervention.
Fixing these two misfeatures is exactly the purpose of this article. Using features introduced in MySQL 5.0 and 5.1, it is possible to build a replication system where all nodes act as master and slave at the same time, with a built-in fail-over mechanism.
|
http://capttofu.livejournal.com/1752.html
A sticky from MySQL forum: circular (multi-master) replication
- nice walk thru
http://www.databasejournal.com/features/mysql/article.php/3355201
| Quote: | | Replication is not an answer to all performance problems. Although updates on the slave are more optimized than if you ran the updates normally, if you use MyISAM tables, table-locking will still occur, and databases under high-load could still struggle. |
http://dev.mysql.com/doc/refman/5.0/en/table-locking.html
Table locking
http://dev.mysql.com/tech-resources/articles/application_partitioning_wp.pdf
| Quote: | Eventually every database system hit its limits. Especially
on the Internet, where you have millions of users
which theoretically access your database simultaneously,
eventually your IO system will be a bottleneck. |
http://jeremy.zawodny.com/mysql/managing-mysql-replication.html
From an open source convention, old but nicely put
http://www.xaprb.com/blog/2007/01/20/how-to-make-mysql-replication-reliable/
| Quote: | | This article explains how to avoid problems, help your slaves stay in sync with the master, and recover from disasters more quickly |
http://www.onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html
| Quote: | | One of the difficulties with a large and active MySQL database is making clean backups without having to bring the server down. Otherwise, a backup may slow down the system and there may be inconsistency with data, since related tables may be changed while another is being backed up. Taking the server down will ensure consistency of data, but it means interruption of service to users. Sometimes this is necessary and unavoidable, but daily server outages for backing up data may be unacceptable. A simple alternative method to ensure reliable backups without having to shut down the server daily is to set up replication for MySQL. |
Last edited by oxo-oxo on Fri Nov 23, 2007 4:02 pm; edited 26 times in total |
|
| Back to top |
|
|
oxo-oxo
Cacti User
Joined: 30 Aug 2007
Posts: 106
Location: Silkeborg, Denmark
|
| Posted: Wed Nov 21, 2007 6:43 am Post subject: |
|
|
Cacti
How does Cacti fit into a reliable mysql?
-cluster/replication
But I did a google for "mysql replication gotchas"
- there are some: do any hit the cacti....
---
Cacti uses (from cacti.sql)
- TYPE=MyISAM
- AUTO_INCREMENT
---
"Standard" Master -> Slave(s) replication
-OK helps backup
-However not much help with performace (unless some stat programs use DB in R/O and the RRD's)
-Poller wants to W to DB, if multi-pollers, all want to use W to Master...
-"Hot Standby" ok if the Master dies or what...
-Can an apache have R/O to a replicer
"Circular" replicaction
-If there are multi-pollers, could this help with performance ...
-Poller reads what it is to poll and writes result back to DB: propergation
-An apache has connection to one of the MySQL's with R/W (table locks)
Cluster
(Not going there) |
|
| Back to top |
|
|
oxo-oxo
Cacti User
Joined: 30 Aug 2007
Posts: 106
Location: Silkeborg, Denmark
|
| Posted: Wed Oct 22, 2008 5:53 am Post subject: Circular replication |
|
|
So far I have cacti doing circular replication:
- machine1: poller, mysql, apache
- machine2: mysql, apache
So now I have a working DB on 2 machines.
- machine2 can mount rrd from machine2, reducing load on machine1 for user access (I did it with a simple nfs mount)
- machine 1 doen't even need a apache if all user access is via machine2's apache
Tests:
- stop mysql on machine2 and add device. start mysql and see device on machine2: done/works
Ref
http://www.onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html
http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
/etc/my.cnf
| Code: |
log-bin=log-bin.log
log-bin-index=log-bin-index.index
relay-log=relay-log.log
relay-log-info-file=relay-log-info-file.info
relay-log-index=relay-log-index.index
server-id = 20 # 10 on other machine
auto_increment_increment = 10
auto_increment_offset = 2 # 1 on other machine
slave-net-timeout = 30
master-connect-retry = 30
master-host = machine2 #machine1 on other machine
master-port = 3306
master-user = root # probably want to use another user in production
master-password = password
|
mysql -u root -p
| Code: |
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
|
Edit: Found another link http://www.howtoforge.com/mysql_master_master_replication
Last edited by oxo-oxo on Thu Oct 23, 2008 11:18 pm; edited 3 times in total |
|
| Back to top |
|
|
oxo-oxo
Cacti User
Joined: 30 Aug 2007
Posts: 106
Location: Silkeborg, Denmark
|
| Posted: Wed Oct 22, 2008 4:06 pm Post subject: |
|
|
the next step is to see if I can make a multi poller
- machine 1 polls some hosts
- machine 2 polls some other hosts
Looking at poller_id
Found in
cacti.sql
cmd.php
poller_commands.php
poller.php
script_server.php |
|
| Back to top |
|
|
gandalf
Developer
Joined: 02 Dec 2004
Posts: 12295
Location: Muenster, Germany
|
| Posted: Thu Oct 23, 2008 1:48 pm Post subject: |
|
|
Yep. You may want to add a column "poller_id" to associate a host (or each data source???) to a specific poller. Then, make the poller of a specific host fetch only "his" hosts (poller commands).
The remaining issue is: Make the "central" poller.php wait for the last update of any remote poller to perform rrdtool updates
Just a few thoughts
Reinhard |
|
| Back to top |
|
|
oxo-oxo
Cacti User
Joined: 30 Aug 2007
Posts: 106
Location: Silkeborg, Denmark
|
| Posted: Thu Oct 23, 2008 1:55 pm Post subject: |
|
|
Yep: unfortunatly I have found dreaded TRUNCATE in connection with poller_output
- so I am analysing the SQL/Tables just now ...
Work in progress 
| Code: |
owen@oxo-l:~/Documents/cacti/cacti/cacti/tags/0.8.7b> svn diff poller.php
Index: poller.php
===================================================================
--- poller.php (revision 4777)
+++ poller.php (working copy)
@@ -40,6 +40,7 @@
/* initialize some variables */
$force = FALSE;
$debug = FALSE;
+$poller_nr = 0;
/* process calling arguments */
$parms = $_SERVER["argv"];
@@ -50,6 +51,11 @@
@list($arg, $value) = @explode("=", $parameter);
switch ($arg) {
+ case "-p":
+ case "--poller":
+ $poller_nr = $value;
+
+ break;
case "-d":
case "--debug":
$debug = TRUE;
@@ -60,7 +66,9 @@
break;
case "--version":
+ case "-v":
case "-V":
+ case "-h":
case "-H":
case "--help":
display_help();
@@ -278,7 +286,7 @@
$rrds_processed = 0;
while (1) {
- $polling_items = db_fetch_assoc("select poller_id,end_time from poller_time where poller_id=0");
+ $polling_items = db_fetch_assoc("select poller_id,end_time from poller_time where poller_id=$poller_nr");
if (sizeof($polling_items) >= $process_file_number) {
$rrds_processed = $rrds_processed + process_poller_output($rrdtool_pipe, TRUE);
@@ -414,8 +422,9 @@
function display_help() {
echo "Cacti Poller Version " . db_fetch_cell("SELECT cacti FROM version") . ", Copyright 2007 - The Cacti Group\n\n";
echo "A simple command line utility to run the Cacti Poller.\n\n";
- echo "usage: poller.php [--force] [--debug|-d]\n\n";
+ echo "usage: poller.php [--poller=[0..9]] [--force] [--debug|-d]\n\n";
echo "Options:\n";
+ echo " --poller Override default poller,0, with nr.\n";
echo " --force Override poller overrun detection and force a poller run\n";
echo " --debug|-d Output debug information. Similar to cacti's DEBUG logging level.\n\n";
}
|
| Code: | owen@oxo-l:~/Documents/cacti/cacti/cacti/tags/0.8.7b> grep -i db poller.php
$num_polling_items = db_fetch_cell("select count(*) from poller_item where rrd_next_step<=0");
$num_polling_items = db_fetch_cell("select count(*) from poller_item");
db_execute("replace into settings (name,value) values ('poller_lastrun'," . $seconds . ')');
$polling_hosts = array_merge(array(0 => array("id" => "0")), db_fetch_assoc("select id from host where disabled = '' order by id"));
db_execute("replace into settings (name,value) values ('path_webroot','" . addslashes(($config["cacti_server_os"] == "win32") ? strtr(strtolower(substr(dirname(__FILE__), 0, 1)) . substr(dirname(__FILE__), 1),"\\", "/") : dirname(__FILE__)) . "')");
db_execute("TRUNCATE TABLE poller_time");
$issues = db_fetch_assoc("SELECT local_data_id, rrd_name FROM poller_output");
db_execute("TRUNCATE TABLE poller_output");
db_execute("replace into settings (name,value) values ('date',NOW())");
$polling_items = db_fetch_assoc("select poller_id,end_time from poller_time where poller_id=$poller_nr");
db_execute("replace into settings (name,value) values ('stats_poller','$cacti_stats')");
db_execute("replace into settings (name,value) values ('stats_poller','$cacti_stats')");
if (db_fetch_cell("select count(*) from poller_command") > 0) {
db_close();
db_connect_real($database_hostname, $database_username, $database_password, $database_default, $database_type, $database_port);
echo "Cacti Poller Version " . db_fetch_cell("SELECT cacti FROM version") . ", Copyright 2007 - The Cacti Group\n\n";
|
| Code: |
owen@oxo-l:~/Documents/cacti/cacti/cacti/tags/0.8.7b> grep include poller.php
include(dirname(__FILE__) . "/include/global.php");
include_once($config["base_path"] . "/lib/poller.php");
include_once($config["base_path"] . "/lib/data_query.php");
include_once($config["base_path"] . "/lib/graph_export.php");
include_once($config["base_path"] . "/lib/rrd.php");
|
| Code: |
owen@oxo-l:~/Documents/cacti/cacti/cacti/tags/0.8.7b/lib> grep include poller.php
include_once($config["library_path"] . "/data_query.php");
include_once($config["library_path"] . "/snmp.php");
include_once($config["library_path"] . "/rrd.php");
owen@oxo-l:~/Documents/cacti/cacti/cacti/tags/0.8.7b/lib> grep -i db poller.php
$host = db_fetch_row("select hostname, snmp_community, snmp_version, snmp_username, snmp_password, snmp_auth_protocol, snmp_priv_passphrase, snmp_priv_protocol, snmp_context, snmp_port, snmp_timeout from host where id=$host_id");
$data_query = db_fetch_row("select reindex_method, sort_field from host_snmp_query where host_id=$host_id and snmp_query_id=$data_query_id");
$data_query_type = db_fetch_cell("select data_input.type_id from (data_input,snmp_query) where data_input.id=snmp_query.data_input_id and snmp_query.id=$data_query_id");
$assert_value = sizeof(db_fetch_assoc("select snmp_index from host_snmp_cache where host_id=$host_id and snmp_query_id=$data_query_id group by snmp_index"));
$primary_indexes = db_fetch_assoc("select snmp_index,oid,field_value from host_snmp_cache where host_id=$host_id and snmp_query_id=$data_query_id and field_name='" . $data_query["sort_field"] . "'");
db_execute("delete from poller_reindex where host_id=$host_id and data_query_id=$data_query_id");
db_execute($recache_stack[$i]);
$results = db_fetch_assoc("select
$rrd_field_names = array_rekey(db_fetch_assoc("select
db_execute("delete from poller_output where local_data_id='" . $item["local_data_id"] . "' and rrd_name='" . $item["rrd_name"] . "' and time='" . $item["time"] . "'");
|
tables with poller_id
poller_item
poller_time
poller_command
tables without poller_id
settings
poller_output
host_snmp_query
data_input
host_snmp_cache
poller_reindex
host
Last edited by oxo-oxo on Thu Oct 23, 2008 2:55 pm; edited 2 times in total |
|
| Back to top |
|
|
oxo-oxo
Cacti User
Joined: 30 Aug 2007
Posts: 106
Location: Silkeborg, Denmark
|
| Posted: Thu Oct 23, 2008 2:14 pm Post subject: |
|
|
| gandalf wrote: |
The remaining issue is: Make the "central" poller.php wait for the last update of any remote poller to perform rrdtool updates
Reinhard |
I am thinking another way: one poller at one data center, the other at another datacenter
Both use the same mysql cacti db (circular replication) and their own web server.
The pollers save to a rra/poller_nr/host etc and mount the others directory...
For n+1 for other poller machines ....
However, there are now quite a few choises:
- all pollers poll the same
- some pollers poll some things, others others
- a central poller gets polls from decentral pollers
- ...... |
|
| Back to top |
|
|
gandalf
Developer
Joined: 02 Dec 2004
Posts: 12295
Location: Muenster, Germany
|
| Posted: Thu Oct 23, 2008 2:20 pm Post subject: |
|
|
Ok, fine. But mounting over nfs will yield bad performance in general. Worse compared to remote MySQL updates. But if there are not too many data source, this may be fine.
Reinhard |
|
| Back to top |
|
|
oxo-oxo
Cacti User
Joined: 30 Aug 2007
Posts: 106
Location: Silkeborg, Denmark
|
| Posted: Thu Oct 23, 2008 3:03 pm Post subject: |
|
|
| gandalf wrote: | Ok, fine. But mounting over nfs will yield bad performance in general. Worse compared to remote MySQL updates. But if there are not too many data source, this may be fine.
Reinhard |
I think there are many views on the mount of data:
- all decentral pollers have central dir's mounted for rra/poller_nr
- central mounts all decentral dir for rra/poller_nr
May be cases for both...
Or does one copy the rrds from decentral to central after each poll cycle...
At the present time, it is poller.php that makes rrd.
If one plays around with the tables enough, maybe one can make a system that is open to as many possibilities as possible. |
|
| Back to top |
|
|
gandalf
Developer
Joined: 02 Dec 2004
Posts: 12295
Location: Muenster, Germany
|
| Posted: Fri Oct 24, 2008 6:30 am Post subject: |
|
|
| oxo-oxo wrote: | At the present time, it is poller.php that makes rrd.
If one plays around with the tables enough, maybe one can make a system that is open to as many possibilities as possible. |
That would indeed be a good goal.
As we discussed at 3.CCC.eu, I currently see 3 different goals, which may interact:
- scalability (be it local or distributed)
- distribution (e.g. polling in different global regions)
- high availability
So you may want to have a scalable, local solution by setting up many pollers, but all of them in one datacenter.
This may of course differ from a globally distributed solution that uses multiple pollers as well, but perhaps differs from the first example due to huge latency.
Personally, I'd like to discuss those three topics independantly even most users will have a combination of two or all three of them. My hope is, that providing different solutions for each of those three, there will be enough room to combine those to match virtually every single situation.
Thinking this way explains, why I'm interested in different MySQL approaches as they surely will be a building block of such an approach.
Reinhard |
|
| Back to top |
|
|
oxo-oxo
Cacti User
Joined: 30 Aug 2007
Posts: 106
Location: Silkeborg, Denmark
|
| Posted: Fri Oct 24, 2008 8:03 am Post subject: |
|
|
The answer lies in how the MySQL tables interact
- still working on it. |
|
| Back to top |
|
|
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
