You are here: Home / 2008 / Archives for December 2008

Dependency error installing mylvmbackup on Ubuntu 8.04

by

I’ve started an investigation of MySQL Backups using LVM. I’m working with Lenz’s mylvmbackup but I found it both used Perl and needed a number of dependencies installed.

Installing dependencies failed on my test system, yet I found it actually worked when I went back to my dev system (but it is not configured with LVM for full testing).

$ sudo cpan Config::IniFiles Sys::Syslog Date::Format Getopt::Long  DBI

Details of error:

....
 CPAN.pm: Going to build S/SA/SAPER/Sys-Syslog-0.27.tar.gz

WARNING: LICENSE is not a known parameter.
Checking if your kit is complete...
Looks good
'LICENSE' is not a known MakeMaker parameter name.
Writing Makefile for Sys::Syslog
cp Syslog.pm blib/lib/Sys/Syslog.pm
/usr/bin/perl /usr/share/perl/5.8/ExtUtils/xsubpp -noprototypes -typemap /usr/share/perl/5.8/ExtUtils/typemap  Syslog.xs > Syslog.xsc && mv Syslog.xsc Syslog.c
cc -c   -D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBIAN -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O2   -DVERSION="0.27" -DXS_VERSION="0.27" -fPIC "-I/usr/lib/perl/5.8/CORE"  -DUSE_PPPORT_H Syslog.c
In file included from Syslog.xs:6:
/usr/lib/perl/5.8/CORE/perl.h:420:24: error: sys/types.h: No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:451:19: error: ctype.h: No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:463:23: error: locale.h: No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:480:20: error: setjmp.h: No such file or directory

Some searching was necessary to find this thread and confirm that my prod server did not have a correct dev package.

apt-get install libc6-dev

NOTE: While the doc refers to the module File::Basename, trying to install this throws an error which when you investigate further is a false positive. The README does refer to this being normally part of the default perl installation.

The size of memory tables

by

I was doing some database sizing in MySQL 5.1.30 GA for memory tables. Generally I have used INFORMATION_SCHEMA.TABLES data_length,index_length as a reasonable guide.

However working with a MEMORY table, after deleting rows, the size did not decrease as expected. I deleted 10% of rows, and saw 0% reduction. This was confirmed by doing a subsequent ALTER where I saw the 10% reduction in memory size.

It requires more investigation, however I found these results unexpected and worthy of publishing.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.30    |
+-----------+


+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| table_name      | engine | row_format | table_rows | avg_row_length | total_mb    | data_mb     | index_mb   |
+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| location_ex4    | MEMORY | Fixed      |    1111000 |             45 | 59.68744659 | 51.16348267 | 8.52396393 |


mysql> delete from location_ex4 limit 111000;
Query OK, 111000 rows affected (0.16 sec)


+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| table_name      | engine | row_format | table_rows | avg_row_length | total_mb    | data_mb     | index_mb   |
+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| location_ex4    | MEMORY | Fixed      |    1000000 |             45 | 59.68744659 | 51.16348267 | 8.52396393 |


mysql> alter table location_ex4 engine=memory;
Query OK, 1000000 rows affected (2.95 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| table_name      | engine | row_format | table_rows | avg_row_length | total_mb    | data_mb     | index_mb   |
+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| location_ex4    | MEMORY | Fixed      |    1000000 |             45 | 53.75530243 | 45.97259521 | 7.78270721

Using Flipper to manage MySQL Pairs

by

As discussed previously in Options using MySQL pairs I have started evaluating the strengths and weaknesses of various open source options. This is an evaluation of Flipper, a product from Proven Scaling a MySQL consulting organization.

Overall

  • Pros When correctly configured and with a working installation it just works, simple and functional, which is good design.
  • Cons The functionality is incomplete especially when it comes to edge cases, additional manual scripting especially for MySQL specifics is necessary and could have be easily added.

The Flipper documentation is detailed, but I found the implementation could have been easier without reading most of the documentation first. The software comes in RPM packages, but as I’m using Ubuntu, installation is via source.

The documentation however assumes your Master/Fail Over master MySQL environment is already correctly configured, and running with Virtual IP’s and the correct read_only status. There is no information for configuration here, so you need to be comfortable with MySQL Replication before starting.

The default notification of IP addresses is managed by arping. Under Ubuntu 8.04 this actually throws an error for a virtual IP on the same host and then Flipper fails to operate as designed. I spent some time to diagnose the problem first before submitting to the flipper-devel list. The response was prompt, the recommendation was to actually use Linux Heatbeat for the purposes of the address notification. The installation of this was easy, via apt-get and the configuration change to Flipper a single row meta data change in one table, which showed good design in this flexibility.

Overall however, Flipper is only a partial solution. It lacked some functionality I just expected would be included in the initial version. The ability to set read_only on a server, Flipper handles this for a controlled failover, but not for just setting against the read only host. There is no means of starting a MySQL slave using the Flipper CLI, you need to do this again manually with additional scripts.

Overall, while a level of information feedback is available, and controlled failover of a correctly working and configured environment works great, manual steps are necessary in the “not ideal” case, when the tool could offer more.

Some points in addition to the supplied documentation.

  • The ‘flipper’ user may only need SELECT privileges to the necessary meta data tables, but it requires ‘SUPER’ privilege for SLAVE management.
  • Installation of arping necessary with ‘sudo apt-get install arping’
  • The arping command syntax needs to be updated for Ubunutu to ”/usr/sbin/arping -I $sendarp_interface -c 5 $sendarp_ip’ ‘. The path and options change. See 2.5.2 ARP sending command. You also need to adjust the Sudo Privileges for the command

Using Heatbeat

The solution to the ‘arping’ problem was to actually use a different command, send_arp which is part of Heatbeat. It’s ironic that Heatbeat is an entire product that could be used for managing pairs. However the following did work.

sudo apt-get install heartbeat
# Install fails consistently on 8.04, following needed
sudo apt-get update
sudo apt-get install heatbeat
# Weird but necessary

INSERT INTO masterpair (masterpair, name, value) VALUES
  ('pairname', 'broadcast', '192.168.2.255');
UPDATE masterpair SET value="/usr/lib/heartbeat/send_arp -p /tmp/send_arp.pid -i 100 -r 5 $sendarp_interface $sendarp_ip auto $sendarp_broadcast $sendarp_netmask" WHERE masterpair="pairname" AND name="send_arp_command";

Bugs

As a result of this, I found at least one bug. With the send_arp_command you can specifiy $sendarp_broadcast as an argument in the value, however when you do, if the variable is not set, there should be a configuration error in Flipper, rather then it attempting to execute a remote SSH command with the variable undefined which causes an error, but could if not support the write variable protection cause other issues depending on syntax used.

Annoyances

1. One annoying thing was unnecessary stderr for SSH connections under Ubuntu, you can fix by doing a 2>/dev/null to address it. It was however useful in debugging to see the number of SSH connections, and then it help find the ‘arping’ issue, but in general it’s annoying, for example.

./flipper developer swap
Connection to 192.168.2.181 closed.
Connection to 192.168.2.181 closed.
Connection to 192.168.2.187 closed.
Connection to 192.168.2.187 closed.
Connection to 192.168.2.187 closed.
Connection to 192.168.2.187 closed.
Connection to 192.168.2.187 closed.
Connection to 192.168.2.187 closed.
Connection to 192.168.2.181 closed.
Connection to 192.168.2.181 closed.
Connection to 192.168.2.181 closed.
Connection to 192.168.2.181 closed.

2. The slave is listed first, you just automatically think master/slave, the output should be formatted in this though.

./flipper developer status 2>/dev/null
MASTERPAIR: developer
NODE: beta181 has read IP, is read-only, replication running, 0s delay
NODE: alpha187 has write IP, is writable, replication running, 0s delay

3. No information after swap. When you do a swap, it would be good for the status to be shown. You are only going to run this command anyway to confirm.

Enhancements

I started working on modifying the ‘flipper’ script to support a read_only command, but I only had 1 day and ran out of time to finish.

Some MySQL pairs terminology

by

In response to a number of comments, I thought I would clarify the scope of my discussion regarding Options using MySQL pairs before I begin. As mentioned their is no one way or type of configuration for MySQL in a HA solution, however the simplest progression from a single Master/Slave environment is the concept of a pair of servers, configured to support a fail over and fail back via MySQL Replication.

The concept of a MySQL Pair in this context is to have a “hot” MySQL standby ready for controlled and hopefully! automated fail over. I say hopefully because with MySQL Replication as an asynchronous solution there is no guarantee for no loss of data.

I consider DRBD/Heatbeat for example a “cold” standby, as MySQL on the slave server is not actually running. DRBD does provide a guarantee of consistency in data (a synchronous solution) that is written at a disk level, which is a significant advantage over asynchronous replication. I consider Red Hat Cluster suite, simply a management process, and definitely “cold”.

A Shared disk solution, for example a SAN, and a failover server that uses the shared storage, is also a “cold” standby.

There are advantages and disadvantages to each option. These relative merits of the strengths and weaknesses should be considered carefully when you are making a design decision.

Options using MySQL Pairs

by

Configuring a production environment using a pair of MySQL servers in a Master/Fail Over Master situation is a common process to provide many benefits including supporting failover, backup/recovery, higher availability for software & database upgrades. This is also a common method for database shards. One of the key hidden benefits is by performing regular controlled failovers for example with software upgrades you are actively testing your disaster recovery procedures. Most organizations have a partial plan, some don’t have any, but rarely do people test their disaster recovery.

There is no one way to configure and manage such an environment. There are a number of options including:

  • Develop your own home grown scripts
  • Flipper by Proven Scaling
  • MMM by Percona
  • Heatbeat by the Linux High Availability Project

I have started a detailed review of a number of these technologies and will be providing my findings for review.

This is not the only way to solve the problems of course. Google for example have provided MySQL Patches that include features such as semi-sync replication and mirrored binary logs. Red Hat Cluster suite, and MySQL/DRBD are other technologies but less idea for various reasons specifically the “cold” nature of the failover environment.