Blog by Shlomi Noach
The standard documentation and tutorials on MMM for MySQL, for master-master replication setup, suggest one Virtual IP for the writer role, and two Virtual IPs for the reader role. It can be desired to only have a single virtual IP for the reader role, as explained below.
A simplified excerpt from the mmm_common.conf sample configuration file, as can be found on the project’s site and which is most quoted: Continue reading » “MMM for MySQL single reader role”
I have a case where I must know if group_concat_max_len is at its default value (1024), which means there are some operation I cannot work out. I’ve ranted on this here.
Normally, I would simply:
SELECT @@group_concat_max_len
However, I am using views, where session variables are not allowed. Using a stored function can do the trick, but I wanted to avoid stored routines. So here’s a very simple test case: is the current group_concat_max_len long enough or not? I’ll present the long version and the short version.
SELECT
CHAR_LENGTH(
GROUP_CONCAT(
COLLATION_NAME SEPARATOR ''
)
)
FROM
INFORMATION_SCHEMA.COLLATIONS;
If the result is 1024, we are in a bad shape. I happen to know that the total length of collation names is above 1800, and so it is trimmed down. Another variance of the above query would be: Continue reading » “Verifying GROUP_CONCAT limit without using variables”
Some mysqld parameters are far from having reasonable defaults. Most notable are the engine-specific values, and in particular the InnoDB parameters.
Some of these variables have different defaults as of MySQL 5.4. innodb_buffer_pool_size, for example, is 128M on 5.4. innodb_log_file_size, however, has changed back and forth, as far as I understand, and is down to 5M again. These settings are still the same on 5.5.
I wish to present some not-so-obvious parameters which, in my opinion, have poor defaults, for reasons I will explain.
Continue reading » “Those oversized, undersized variables defaults”
This post lists the essential and optional settings for a replication environment.
It does not explain how to create replicating slaves. See How To Setup Replication for that. However, not all configuration options are well understood, and their roles in varying architectures can change.
Here are the settings for a basic Master/Slave(s) replication architecturee.
When working with Master/Slaves replication, one should be prepared to master failure and slave promotion to master. It may be desirable to identify a particular slave as primary candidate for promotion.
MySQL is known for its willingness to accept invalid queries, data values. It can silently commit your transaction, truncate your data.
It would be nice to: Continue reading » “But I DO want MySQL to say “ERROR”!”
Nothing new about this warning; but it’s worth repeating:
Using binlog-do-db is dangerous to your replication. It means the master will not write to binary logs any statement not in the given database.
Ahem. Not exactly. It will not write to binary logs any statement which did not originate from the given database.
Which is why a customer, who was using Toad for MySQL as client interface to MySQL, and by default connected to the mysql schema, did not see his queries being replicated. In fact, he later on got replication errors. If you do:
USE test; INSERT INTO world.City VALUES (...)
Then the statement is assumed to be in the test database, not in the world database.
Slightly better is using replicate-do-db on the slave machines. At least we allow the master to write everything. But still, for the same reasons, slaves may fail to repeat a perfectly valid query, just because it has been issued in the context of the wrong database. replicate-ignore-db is somewhat safer yet, but the trap is still there.
My advice is that replication should replicate everything. Make sure you and everyone else you work with understand the implications of binlog-do-db and replicate-do-db before implementing it.
I’ve written shortly on this before. I like yum; I love apt-get; I prefer not to use them for MySQL installations. I consider a binary tarball to be the best MySQL installation format (source installations being a different case altogether).
I use yum and apt-get whenever I can and for almost all needs (sometimes preferring CPAN for Perl installations). But on a MySQL machine, I avoid doing so. The reason is either dependency hell or dependency mismatch.
Package managers are supposed to solve the dependency hell issue. But package managers will rarely have an up to date MySQL version.
I’ve had several experiences where a simple yum installation re-installed the MySQL version. I’ve had customers calling me up when, having installed something with yum, MySQL would not work anymore. Continue reading » “To not yum or to not apt-get?”
I’m proud to announce mycheckpoint, a monitoring utility for MySQL, with strong emphasis on user accessibility to monitored data.
mycheckpoint is a different kind of monitoring tool. It leaves the power in the user’s hand. It’s power is not with script-based calculations of recorded data. It’s with the creation of a view hierarchy, which allows the user to access computed metrics directly.
mycheckpoint is needed first, to deploy a monitoring schema. It may be needed next, so as to INSERT recorded data (GLOBAL STATUS, GLOBAL VARIABLES, MASTER STATUS, SLAVE STATUS) — but this is just a simple INSERT; anyone can do that, even another monitoring tool.
It is then that you do not need it anymore: everything is laid at your fingertips. Consider:
SELECT innodb_read_hit_percent, DML FROM sv_report_chart_hour;
mycheckpoint provides the views which take raw data (just innodb_buffer_pool_read_requests, com_select, innodb_buffer_pool_size, table_open_cache, seconds_behind_master etc.) and generate Google Charts URLs, HTML reports, human readable reports, or otherwise easily accessible data.
Continue reading » “Announcing mycheckpoint: lightweight, SQL oriented monitoring for MySQL”
Unless specified otherwise, latin1 is the default character set in MySQL.
What I usually find in schemes are columns which are either utf8 or latin1. The utf8 columns being those which need to contain multilingual characters (user names, addresses, articles etc.), and latin1 column being all the rest (passwords, digests, email addresses, hard-coded values etc.)
I find latin1 to be improper for such purposes and suggest that ascii be used instead. The reason being that latin1 implies a European text (with swedish collation). It is unclear for an outsider, when finding a latin1 column, whether it should actually contain West European characters, or is it just being used for ascii text, utilizing the fact that a character in latin1 only requires 1 byte of storage. Continue reading » “Character sets: latin1 vs. ascii”
When working with InnoDB, you have two ways for managing the tablespace storage:
I will discuss the advantages and disadvantages of the two options, and will strive to convince that innodb_file_per_table is preferable.
Having everything in one big file means all tables and indexes, from all schemes, are ‘mixed’ together in that file.
This allows for the following nice property: free space can be shared between different tables and different schemes. Thus, if I purge many rows from my log table, the now unused space can be occupied by new rows of any other table.
This same nice property also translates to a not so nice one: data can be greatly fragmented across the tablespace.
An annoying property of InnoDB’s tablespaces is that they never shrink. So after purging those rows from the log table, the tablespace file (usually ibdata1) still keeps the same storage. It does not release storage to the file system.
I’ve seen more than once how certain tables are left unwatched, growing until disk space reaches 90% and SMS notifications start beeping all around. Continue reading » “Reasons to use innodb_file_per_table”