PlanetMysql.ru — информация о СУБД MySQL

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. It is currently set on 128M on 5.5.

I wish to present some not-so-obvious parameters which, in my opinion, have poor defaults, for reasons I will explain.

• group_concat_max_len: This parameter limits the maximum text length of a GROUP_CONCAT concatenation result. It defaults to 1024. I think this is a very low value. I have been using GROUP_CONCAT more and more, recently, to solve otherwise difficult problems. And in most cases, 1024 was just too low, resulting in silent (Argh!) truncating of the result, thus returning incorrect results. It is interesting to learn that the maximum value for this parameter is limited by max_packet_size. I would suggest, then, that this parameter should be altogether removed, and have the max_packet_size limitation as the only limitation. Otherwise, I’d like it to have a very large default value, in the order of a few MB.
• wait_timeout: Here’s a parameter whose default value is over permissive. wait_timeout enjoys an 8 hour default. I usually go for 5-10 minutes. I don’t see a point in letting idle connections waste resources for 8 hours. Applications which hold up such connections should be aware that they’re doing something wrong, in the form of a forced disconnection. Connection pools work beautifully with low settings, and can themselves do keepalives, if they choose to.
• sql_mode: I’ve discussed this in length before. My opinion unchanged.
• open_files_limit: What with the fact connections, threads, table descriptors, table file descriptors (depending on how you use InnoDB), temporary file tables — all are files on unix-like systems, and considering this is an inexpensive payment, I think open_files_limit should default to a few thousands. Why risk the crash of “too many open files”?

No setting will ever be perfect for everyone, I know. But there are those parameters which you automatically set values for when you do a new install. These should be at focus and their defaults change.

PlanetMySQL Voting: Vote UP / Vote DOWN

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.

Essential

• log-bin: enable binary logs on the master. Replication is based on the master logging all modifying queries (INSERT/CREATE/ALTER/GRANT etc.), and the slaves being able to replicate them.
• server-id: each machine must have a unique server-id. A slave will not replay queries originating from a server with the same server-id as its own.
• GRANT: grant a user with REPLICATION SLAVE. The host list must include all replication slave hosts.
• expire-logs-days: automatically clean up master’s binary logs older than given value. By default, binary logs are never removed.

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.

Just setting up the log-bin will yield with warnings in the MySQL’s error log. The binary logs are named, by default, after the host’s name. If that should change – MySQL will not be able to find the binary logs anymore (expecting a name which does previous logs did not use). It is therefore recommended to use:

log-bin=mychachine-bin

or

log-bin=mysql-bin

Essential/Optional

• log-bin: enable on a slave, so that in case it is promoted to master, the rest of the slaves can replicate using its binary logs. Enabling binary logging cannot be done on a live server: this parameter requires MySQL restart.
• GRANT: include the master’s host, so that when a slave promotes to master, the master can become a slave and continue replicating.
• log-slave-updates: together with log-bin, enable on slave so that master’s binary logs are propagated and logged by the slave. This is required if the slave takes the role of a master in a chained replication setup.

Extra

• report-host, report-port: the host and port identifying the slave when looking at SHOW SLAVE HOSTS on master. Set this up on all hosts. See further discussion here.
• max-binlog-size: the maximum size for a binary log / relay log file, after which it is rotated.

Expert

• binlog-do-db, binlog-do-table, replicate-do-db, …: filter queries by either not writing them to binary log, or not reading them from the logs.

The reason I list the above as “Expert” is not because one must have a super-brain to set them up. That part is easy enough. But they lead to some dangerous situations, sometimes seemingly harmless. It takes great care to control the application and developers from creating those situations. See documentation here. See also discussion here and here.

PlanetMySQL Voting: Vote UP / Vote DOWN

Just a quick notice to let everyone know that there is a new version of Kontrollkit available. There are some required bug fixes to the formerly new python backup script and some Solaris compatible changes to the various my.cnf files. You can download the new version here: http://kontrollsoft.com/software-downloads, or here: http://code.google.com/p/kontrollkit/
PlanetMySQL Voting: Vote UP / Vote DOWN

Just a quick notice to let everyone know that there is a new version of Kontrollkit available. There are some required bug fixes to the formerly new python backup script and some Solaris compatible changes to the various my.cnf files. You can download the new version here: http://kontrollsoft.com/software-downloads, or here: http://code.google.com/p/kontrollkit/
PlanetMySQL Voting: Vote UP / Vote DOWN

The new ndbinfo interface in 7.1 is really useful to assist in tuning MySQL Cluster. Here is an example (more will follow):

I started with one test where I inserted two blobs (1KB + 1KB) in one table.
From 16 threads (colocated with one mysqld, two data nodes, separate computers) and one application driving the load I reached about 6960TPS, and the utilization of the redo buffers (controlled by the parameter RedoBuffer in config.ini) looked like:

mysql< select * from ndbinfo.logbuffers;
+---------+----------+--------+----------+----------+--------+
| node_id | log_type | log_id | log_part | total    | used   |
+---------+----------+--------+----------+----------+--------+
|       3 | REDO     |      0 |        1 | 50331648 | 196608 |
|       3 | REDO     |      0 |        2 | 50331648 | 294912 |
|       3 | REDO     |      0 |        3 | 50331648 | 131072 |
|       3 | REDO     |      0 |        4 | 50331648 | 229376 |
|       4 | REDO     |      0 |        1 | 50331648 | 229376 |
|       4 | REDO     |      0 |        2 | 50331648 | 262144 |
|       4 | REDO     |      0 |        3 | 50331648 | 163840 |
|       4 | REDO     |      0 |        4 | 50331648 | 229376 |
+---------+----------+--------+----------+----------+--------+
8 rows in set (0.01 sec)

Which is basically nothing.

I then increased the load and inserted 2 x 5120B BLOBs (from 16 threads one MySQL server), and run with an insert speed of 4320TPS:

mysql< select * from ndbinfo.logbuffers;
+---------+----------+--------+----------+----------+----------+
| node_id | log_type | log_id | log_part | total    | used     |
+---------+----------+--------+----------+----------+----------+
|       3 | REDO     |      0 |        1 | 50331648 | 11468800 |
|       3 | REDO     |      0 |        2 | 50331648 | 31522816 |
|       3 | REDO     |      0 |        3 | 50331648 | 42008576 |
|       3 | REDO     |      0 |        4 | 50331648 | 43057152 |
|       4 | REDO     |      0 |        1 | 50331648 | 14090240 |
|       4 | REDO     |      0 |        2 | 50331648 | 17432576 |
|       4 | REDO     |      0 |        3 | 50331648 | 10321920 |
|       4 | REDO     |      0 |        4 | 50331648 | 12615680 |
+---------+----------+--------+----------+----------+----------+

Above you can see that the redo buffers are used (the load will be spread around, and it is hard to catch a moment where the load is even on all buffers), and now the application started to throw the error "Got temporary error 1221 'REDO buffers overloaded (increase RedoBuffer)' from NDBCLUSTER (1297)"

I can now follow the instruction to increase the REDO buffer, but would it help in this case?
No, no and no.
The disk is too slow to keep up and cannot write out to disk in the same rate as the application writes out.

'iostat' gives:

< iostat -kx 1

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
cciss/c0d0        0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
cciss/c0d1        0.00 27796.00    0.00 1454.00     0.00 115196.00   158.45    12.03    8.25   0.66  95.30
dm-0              0.00     0.00    0.00 29270.00     0.00 117080.00     8.00   274.79    9.33   0.03  95.20
dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-3              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-4              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-5              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

And here you can see that the disks are quite utilized. This means that I have two options now if I want to be able to sustain the 4320TPS insert load:

• Increase the number of data nodes (computers) so instead of having two computers, I should have four so that I spread the load across more hardware
• Improve my disk subsystem (add better disks, e.g, to have 2-4 disk spindles to spread the load on), or by having the REDO log on device cciss/c0d1 and the the LCP on device cciss/c0d0.

The CPU, could that also been an bottleneck in this case? No, it was not the issue. The CMVMI thread (one of the data nodes threads) was spending 44.4% polling data from the other nodes, and it is reading in quite large packets so that is why it was the heaviest user of CPU of the data node threads.

5453 root      20   0 6594m 4.1g 6956 R 44.4 51.9   4:05.64 ndbmtd
5471 root      20   0 6594m 4.1g 6956 S 32.5 51.9   3:39.07 ndbmtd
5474 root      20   0 6594m 4.1g 6956 R 26.6 51.9   2:25.55 ndbmtd
5475 root      20   0 6594m 4.1g 6956 S 23.7 51.9   2:25.01 ndbmtd
5476 root      20   0 6594m 4.1g 6956 R 23.7 51.9   2:20.83 ndbmtd
5473 root      20   0 6594m 4.1g 6956 R 21.7 51.9   2:26.57 ndbmtd

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL is known for its willingness to accept invalid queries, data values. It can silently commit your transaction, truncate your data.

• Using GROUP_CONCAT with a small group_concat_max_len setting? Your result will be silently truncated (make sure to check the warnings though).
• Calling CREATE TEMPORARY TABLE? You get silent commit.
• Issuing a ROLLBACK on non-transactional involved engines? Have a warning; no error.
• Using LOCK IN SHARE MODE on non transactional tables? Not a problem. Nothing reported.
• Adding a FOREIGN KEY on a MyISAM table? Good for you; no action actually taken.
• Inserting 300 to a TINYINT column in a relaxed sql_mode? Give me 255, I’ll silently drop the remaining 45. I owe you.

Warnings and errors

It would be nice to:

• Have an auto_propagate_warning_to_error server variable (global/session/both) which, well, does what it says.
• Have an i_am_really_not_a_dummy server variable which implies stricter checks for all the above and prevents you from doing with anything that may be problematic (or rolls back your transactions on your invalid actions).

Connectors may be nice enough to propagate warnings to errors – that’s good. But not enough: since data is already committed in MySQL.

If I understand correctly, and maybe it’s just a myth, it all relates to the times where MySQL had interest in a widespread adoption across the internet, in such way that it does not interfere too much with the users (hence leading to the common myth that “MySQL just works out of the box and does not require me to configure or understand anything”).

MySQL is a database system, and is now widespread, and is used by serious companies and products. It is time to stop play nice to everyone and provide with strict integrity — or, be nice to everyone, just allow me to specify what “nice” means for me.

PlanetMySQL Voting: Vote UP / Vote DOWN

CMake is a cross-platform, open-source build system, maintained by Kitware, Inc.

From the CMake.org home page:

CMake is a family of tools designed to build, test and package software. CMake is used to control the software compilation process using simple platform and compiler independent configuration files. CMake generates native makefiles and workspaces that can be used in the compiler environment of your choice.

It has been used for building the MySQL Server on Windows since MySQL 5.0 – the initial CMake build support was added in August 2006.

For building MySQL on all other platforms, the GNU autotools (autoconf, automake and libtool) are currently being used.

CMake is used in some other MySQL projects as well, e.g.

• MySQL Connector/C
• MySQL Connector/C++
• MySQL Proxy

On February 22nd, Vladislav Vaintroub pushed the changes required to implement WorkLog#5161 "CMake-based unified build system" into the "mysql-next-mr" branch (aka the "Celosia" mile stone).

From this version on, CMake can also be used to build MySQL on Linux and other Unix platforms. For the time being, the autoconf/automake files are still available as well, but will be phased out once the CMake build enviroment has reached the desired level of maturity. The change was announced on February 28th on our "internals" developer discussion list.

The purpose of WL#5161 is to simplify the MySQL build system. It is much easier and less error-prone to maintain a unified build system for all platforms than two separate ones.

CMake has been chosen because of several reasons; the worklog description lists a few pro-CMake arguments (slightly rephrased):

• CMake works on Windows. The GNU buildsystem does not really work and likely never will work natively on Windows (Using Cygwin is not really an option).
• Traditionally, new MySQL features that required changes in the build environment (e.g. the plugin system, unit tests, most recently googletest integration) were always implemented on Unix first, leaving Windows behind (sometimes for years). This would not happen with a unified build system.
• MySQL already uses CMake since 2006 on Windows, so we do not need to start from scratch, only port what we have to Unix.
• CMake runs on every OS and compiler we support.
• It is simple to obtain and install on a wide range of platforms. It is available in all major Linux package repositories (e.g. Ubuntu, Fedora, OpenSUSE). It is also in the OpenSolaris repository, known as SUNWCmake. It's in FreeBSD ports and available for Mac OS X. It is also very simple to compile it from source, the single prerequisite is a working C++ compiler and make utility.
• CMake has support for features we need and might need, e.g. system checks or cross-compiling.
• CMake provides integrated support for packaging. It can handle both simple packages (tar.gz or zip archives) and more complex things like DEB and RPM without much extra coding.
• Good integration with the popular IDEs (Visual Studio, Xcode, Eclipse CDT, KDevelop). Developing in an IDE makes the development process more enjoyable, and potentially it lowers the barrier for external contributors. Of course, CMake can generate traditional Unix Makefiles, which appear to be are superior to the ones generated by GNU autotools (for example, they have progress indicators, colored output and working dependencies).
• The scripting language used by CMake is simpler than m4 used by autotools.
• CMake is a single small tool, not a bunch of different tools as in GNU system (autoconf, autoheader, automake, libtool)

I'd like to mention a few additional reasons:

• Out-of-source builds – CMake can separate the build directory from the source directory. This is convenient, as your working source tree is not cluttered with object files and other fragments of the build process.
• Build configuration using a GUI. The cmake-gui package (based on Nokia/TrollTech's Qt library) provides a convenient way of enabling and configuring the various available build options. This is much better than having to memorize all the required defines and configuration flags.
• Integrated support for creating a wide range of package formats.

The CMake Wiki lists a number of other "nice to have" features.

From a developer perspective, I hope that it will make it much easier to finally implement two things that many developers working with MySQL have been waiting for (now that the build code has been cleaned up):

• Compiling the embedded MySQL Server (libmysqld) as a shared library
• Better support for cross-compilation

Building MySQL with CMake is quite simple and straighforward – the process is outlined on the MySQL Forge Wiki. The document is still work in progress and we'd like to encourage you to take a look at it, try to follow the steps and update/improve the Wiki page, if needed! Your feedback on the build process is appreciated. Feel free to join our internals mailing list to discuss your impressions and observations or submit a bug report via the Bug Database. It's likely that the build still has a few rough edges that we'd like to fix quickly (e.g. BUG#51502 – a fix for this one is already commited to the mysql-next-mr-bugfixing source tree and will be merged into the mysql-next-mr trunk soon).

If you're new to CMake, you might want to take a look at the "Getting Started With CMake (An End-User's Perspective) For Cross-Platform Building" screencast or the "Running CMake" article.

Happy hacking!

PlanetMySQL Voting: Vote UP / Vote DOWN

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.

PlanetMySQL Voting: Vote UP / Vote DOWN

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).

Why?

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.

yum install package-which-depends-on-mysql-server will install MySQL server on your system if it hasn’t been installed with yum. Are you on CentOS 5.0? You’ll get MySQL 5.0.22. Oh, did you already have a RPM installation for MySQL 5.0.81? Sorry – it’s just been downgraded, plus it won’t work anymore since the error messages file has been changed since then.

Don’t press ‘Y‘ too soon!

Things are slightly better with apt-get. I’ve encountered less situations where mysql-server was on the dependency list. Many times it’s just the libmysqlclient package or the mysql-common one.

But wait! Did you install mysql-common? Bonus! You get the elusive /etc/mysql/my.cnf file created, and there goes your server configuration. Future spawns of the MySQL server / clients will read from the wrong configuration file, and will probably fail to load.

Not to mention neither will help you out with multiple instances installation.

My argument

A sys admin recently argued with me that it was wrong of me to have the entire machine set up with yum, but have MySQL installed with binary tarball. He argued that it broke the entire setup. I expressed my opinion: on a MySQL dedicated server, MySQL gets to be prioritized. It’s special. It is the reason for the existence of the machine. I would imagine that same would hold for Apache on an Apache dedicated machine, for Sendmail on a Sendmail dedicated machine, etc. As a DBA, I want to have best control of the MySQL installation; I want to be able to upgrade minor versions quickly: I often find newer versions to solve bugs I was concerned with; I want to be able to install multiple instances; I want to be able to downgrade without having to remove and uninstall the previous version.

I want to have control. World domination aside, that is.

PlanetMySQL Voting: Vote UP / Vote DOWN

Version 3.0 of the severalnines/Configurator has been released:

• Improved scripts (a lot of cleanup), prefixing the script output with the hostname:
  E.g,:
  Cluster: Cluster Start
Cluster: STARTING MANAGEMENT SERVERS
ps-ndb01: Starting management server (nodeid=1)
ps-ndb01: Copying ../config/config.ini to /etc/mysql
ps-ndb01: Started management server (nodeid=1, pid=28253)
...
  
• Reduced number of scripts
  start-cluster-initial.sh --> start-cluster.sh --initial
rolling-restart-initial.sh --> rollling-restart.sh --initial
  
• Better error handling during rolling restarts
• Supports MySQL Cluster 7.1.1

Here is an example of how to try out 7.1.1 using the Configurator

1. Generate a Configuration (make sure you select "MySQL Cluster 7.1.1")

• Currently 7.1.1 is only in source format so you need to have ncurses-devel/gcc/g++/make installed

2. Unpack the tarball you get and start the installation!
You are recommended to set up shared ssh keys between the "Frontend" (where you run the scripts) to the other nodes in the cluster in order to avoid typing passwords all the time.

tar xvfz mysqlcluster-71.tar.gz
cd mysqlcluster-71
cd cluster/scripts/install/
./shared-ssh-keys.sh ###WARNING - This script is experimental, let me know if it works!
./download-and-compile.sh
./install-cluster.sh
./bootstrap.sh
cd ..
./start-cluster.sh --initial

Voila!
PlanetMySQL Voting: Vote UP / Vote DOWN