nixgeek's questions

In a similar line to...

http://serverfault.com/questions/35899/mysql-5-1-replication-topologies-multi-master

What I'd like to know is whether there are any really good proxies for MySQL out there? Part of my platform is a "shared hosting" environment and customers typically configure (as an example) their application to talk to 'mysql4-7.db.domain.tld' at the moment.

Unfortunately this makes upgrades an, "All or nothing" approach and testing is tricky.

What I'd like to do is to point everyone at a highly available set of proxies, have those handle authentication and pass on queries to the appropriate database server.

Features I'd love to see;

• configurable from a database
• some kind of shared state, so we can do seamless failover
• not a total drag on performance
• additional logging/metrics to identify heavy users/databases, should be possible given the proxy will see all queries going through?
• ability to listen upon multiple IP addresses and ports

The end goal is to be able to move users over a single database at a time, so we can handle upgrades in stages and mostly without customers noticing or caring. Please bear in mind we don't write the applications for these guys, nor provide "in depth" technical support (to the level of application configuration) and some of our users are pretty clueless. Hence this train of thought ;)

I have considered creating DNS records for each database similar to:

databasename.db.domain.tld

Then pointing those at the correct database server, we do have a constraint that database names (across all DB platforms) must be unique... Is there any better solution?

Happy to answer questions. Thanks for your time!

Hallo :-)

Once again I'm on the hunt for new knowledge, I'm a part-time DBA as part of my current job, and am particularly interested in any documentation or reading around doing bulletproof multi-master replication topologies - does anyone have any pointers?

In past roles I've implemented and supported such topologies but there are always 'catches' (auto_increment_increment, auto_increment_offset being two big ones) that you only spot at the last minute and which have serious potential to ruin your day.

With a largely InnoDB workload, what are the big problems with multi-master replication and how do you, as a skilled DBA, go about solving them? How does that picture change if you're storing things with MyISAM, or indeed, other storage engines now they're nice and pluggable, perhaps someone has experience with Infobright or another data warehouse?

Emphasis should be placed on recovery techniques for any proposed solution. How does a DBA effectively backup that topology, and how easy is the restore process? Is it bulletproof enough that you can stick a TCP-aware load balancer (hashing on source IP or similar) in front and have zero downtime (or damn close to...) in the event a MySQL master goes pop?

I have read and would highly recommend High Performance MySQL by Baron Schwartz, however, what I'm really after is a couple of really quality websites with all the points covered and links to more in-depth reading material as required. Who's got one of those handy? :)

Bonus brownie points to any solution which can have 'pools' of slaves hung off it for the odd application which has a particularly thrashy read workload.

Thank you very much.

Greetings,

I'd like to ask the collectives opinion and view on distributed monitoring systems, what do you use and what are you aware of which might tick my boxes?

The requirements are quite complex;

• No single point of failure. Really. I'm dead serious! Needs to be able to tolerate single/multiple node failure, both 'master' and 'worker' and you may assume that no monitoring location ("site") has multiple nodes in it, or are on the same network. Therefore this probably rules out traditional HA techniques such as DRBD or Keepalive.

• Distributed logic, I would like to be deploying 5+ nodes across multiple networks, within multiple datacentres and on multiple continents. I want the "Birds Eye" view of my network and applications from the perspective of my customers, bonus points for the monitoring logic not getting bogged down when you have 50+ nodes, or even 500+ nodes.

• Needs to be able to handle a fairly reasonable number of host/service checks, a la Nagios, for ballpark figures assume 1500-2500 hosts and 30 services per host. It'd be really nice if adding more monitoring nodes allowed you to scale relatively linearly, perhaps in 5 years time I might be looking to monitor 5000 hosts and 40 services per host! Adding on from my note above about 'distributed logic' it'd be nice to say:

  • In normal circumstances, these checks must run on $n or n% of monitoring nodes.
  • If a failure is detected, run checks on another $n or n% of nodes, correlate the results and then use them to decide whether criteria has been met to issue an alert.

• Graphs and management friendly features. We need to track our SLAs and knowing whether our 'highly available' applications are up 24x7 is somewhat useful. Ideally your proposed solution should do reporting "out of the box" with minimal faff.

• Must have a solid API or plugin system for developing of bespoke checks.

• Needs to be sensible about alerts. I don't want to necessarily know (via SMS, at 3am!) that one monitoring node reckons my core router is down. I do want to know if a defined percentage of them agree that something funky is going on ;) Essentially what I'm talking about here is "quorum" logic, or the application of sanity to distributed madness!

I'm willing to consider both commercial and open source options, although I'd prefer to steer clear of software costing millions of pounds :-) I'm also willing to accept there may be nothing out there which ticks all those boxes, but wanted to ask the collective that.

When thinking about monitoring nodes and their placement, bear in mind most of these will be dedicated servers on random ISPs networks and thus largely out of my sphere of control. Solutions which rely on BGP feeds and other complex networking antics likely won't suit.

I should also point out that I've either evaluated, deployed or heavily used/customized most of the open source flavours in the past including Nagios, Zabbix and friends -- they're really not bad tools but they fall flat on the whole "distributed" aspect, particularly with regards to the logic discussed in my question and 'intelligent' alerts.

Happy to clarify any points required. Cheers guys and gals :-)