Feed Aggregator

Hi Morgan,

Thanks!

I have planed such a test already a while ago but did not find the time to do it yet...

In my mind I have some numbers showing that since MySQL 4.0 single query performance has decreased significantly...

This leads to the question: Where are all the MySQL branches and forks heading to and is this in the interest of the majority of the MySQL users or just in the interest of a few ones like Facebook, LinkeId, Google, Booking.com etc...?

Regards,
Oli

In our MySQL trainings and consulting engagements we tell our customers always to use the smallest possible data type to get better query performance. Especially for the JOIN columns. This advice is supported as well by the MySQL documentation in the chapter Optimizing Data Types:

Use the most efficient (smallest) data types possible. MySQL has many specialized types that save disk space and memory. For example, use the smaller integer types if possible to get smaller tables. MEDIUMINT is often a better choice than INT because a MEDIUMINT column uses 25% less space.

I remember somewhere the JOIN columns where explicitly mentioned but I cannot find it any more.

Test set-up

To get numbers we have created a little test set-up:

CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT
, `data` varchar(64) DEFAULT NULL
, `ts` timestamp NOT NULL
, PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=latin1
 

CREATE TABLE `b` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT
, `data` varchar(64) DEFAULT NULL
, …

Hi! It’s an interesting (and well designed) test case.

In single threaded workloads 5.6 can be a little slower than 5.5, so it may have advantaged TokuDB just a little bit.

I would be interested to see if you are able to re-run just INT performance across MySQL 5.5 / 5.6 / 5.7.

FromDual has the pleasure to announce the release of the new version 1.0.2 of its popular multi-instance MySQL Environment MyEnv.

You can download MyEnv from here.

In the inconceivable case that you find a bug in MyEnv please report it to our Bugtracker.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

If you have questions about MyEnv we will answer them in the MyEnv forum.

Upgrade from 1.0.x to 1.0.2

# cd /home/mysql/product
# tar xf /tmp/myenv-1.0.2.tar.gz
# rm -f myenv
# ln -s myenv-1.0.2 myenv

Add the following line to your ~/.bash_profile:

# BEGIN MyEnv
# Written by the MyEnv installMyEnv.php script.
. /etc/myenv/MYENV_BASE
MYENV_PWD=`pwd`
cd $MYENV_BASE/bin
. myenv.profile
up
cd $MYENV_PWD
# END MyEnv

Changes in MyEnv 1.0.2

MyEnv

• Missing [client] section in my.cnf should not lead to error any more (Bug
  #95).
• profile.template removed because it is redundant to installer information.
• up removed from myenv.profile.
• Also root should be allowed to …

If you are not operating MySQL databases yourself, but you develop MySQL based software, our MySQL development support is exactly fitting to your needs!

Are your developers hitting technical MySQL problems from time to time or have they a technical MySQL question, our support team is pleased to answer your questions after having talked to the FromDual consulting team.

This support contract is ticket- and not server-based and thus ideally fitting to your software development team. Any number of your MySQL developers is entitled to open MySQL, Percona Server and MariaDB support requests for any of your MySQL systems.

More FromDual support offerings.

Features

Galera is a synchronous multi-master replication Cluster for MySQL/InnoDB databases, having features like:

• Synchronous replication
• Active-active multi-master topology
• Read and write to any cluster node
• Automatic membership control, failed nodes drop from the cluster
• Automatic node joining
• True parallel replication, on row level
• Direct client connections, native MySQL look & feel

Benefits

The benefits of these features are:

• No slave lag
• No lost transactions
• Read scalability and write improvement
• Smaller client latencies

Technology

Galera Cluster for MySQL uses the Galera library for the replication implementation. To interface with Galera replication, Codership has enhanced MySQL server to support replication API definition in the wsrep API project.

The implementation of the replication API in MySQL server, happens in open source project: MySQL-wsrep.

Download

Codership MySQL and the Galera plugin can be found on Launchpad.

Use Cases

Galera replication works for a wide variety of use cases, …

Hello Jon,

Great you are happy with MyEnv!

A little Information: We plan to release MyEnv 1.0.2 soon (this week?).

About License: This is a bug! MyEnv is licensed under GPL v2. I have filed a bug-report (#100) in our bug tracker and I hope it will make it into 1.0.2.

If you are interested in a subscription/support contract please let me know...

Please report all feature requests, bug reports and fixes to our bug tracker as well.
So: We are very interested to get your changes upstream! But we cannot guarantee that it will make it to the official release. We have to consider also the interests of our big customers...

If it is GPL you can do more or less whatever you want with it. But we would prefer to keep resources consolidated...

Thanks,

Oli

Hi there! I am working on a project where I will most likely be using MyEnv (oh how I wish I had known of this the last time I set up mysqld_multi). I have added one feature already and have a few bug and performance fixes as well. My questions: Is there a license for MyEnv? If no, can you include one? Also, would you like me to pass my changes upstream? If so, how would be the best way. Mantis? Related to the licensing questions, would it be acceptable for redistribution with my changes? (eg. a public GitHub repository). Thanks for writing MyEnv, it’s going to make this project far more polished without having to use mysqld_multi again.

There are many different possibilities how you can achieve inconsistencies between Master and Slave in a MySQL replication, intentional and non-intentional ones. How to achieve them is not the topic of this article.

What we want to know is how can we detect inconsistencies or differences between Master and Slave and how can we fix them.

To find those inconsistencies or differences we need 2 tools from the Percona Toolkit: pt-table-checksum and pt-table-sync.

Requirements

The following requirements have to be met to find the differences:

• We need a running Master/Slave replication.

• We need a user to do the checks and repairs. Run on Master the following command:

  GRANT ALL ON *.* to ptc@'%' identified by 'secret';
  

• If you use non default ports for MySQL (3306) the following variables have to be set on the Slaves:

  # my.cnf
  [mysqld]
  report_host = slave
  report_port = 3307
  

Introduction of differences

To test if and how the tools work we introduce some inconsistencies into a test table:

• INSERT a row on …

Hello csmanioto,

As mentioned above this is a PoC and should never be used on system with critical data because it is an evil hack!!!

If you would like to see this comming in production soon you should vote fore this feature request.

Hi,

Don’t work for me :(

file ibdata1 = 144539648 bytes (8822 pages)… checking pages in range 0 to 8821 page 0: log sequence number: first = 579975309; second = 579975309 page 0: old style: calculated = 1043303352; recorded = 1043303352 page 0: new style: calculated = 2013654343; recorded = 3190797841 page 0 invalid (fails new style checksum)

Question: The hex value A60C: A6 in 0x0030 and 0C in 0x0031 ?

Thanks,

Message 9704:2013-11-18 17:03:53.377 - WARN: Connection to zabbix server failed (rc=1305)! appears because of a bug in lib/FromDualMySQLagent.pm in function checkConnectionToZabbixServer.

In this function, FromDual agent tries to send a trapper to Zabbix as follow: /usr/bin/zabbix_sender -z XXXX -p 10051 -s ‘vbbcmdb1’ -k FromDual.server.check -o ‘1’

Unfortunatly, item ‘FromDual.server.check’ doesn’t exist in any provided template, which makes check fail.

Also, zabbix_sender options are OK here. You can use either short or long version

Table of Contents

• Introduction
• Prerequisites
• What is MyEnv?
• MySQL Workbench configuration.
  • Add MySQL connections to MySQL Workbench.
  • Start/Stop instance configurations.

Introduction

MySQL Workbench is a very good and free GUI tool provided by Oracle to manage MySQL administration and development tasks. Opening many MySQL connections (same or different instances, remote or local MySQL servers) at the same time is one of its main features. While it’s working fine to perform SQL statements on the different connections opened for multiple instances, but some people are asking if it is available as well to start and stop multiple MySQL instances using MySQL Workbench? if yes, how to configure it to perform such task? and also does that make any conflict with MyEnv tool - if it’s installed - or not? Yes, MySQL Workbench could be configured to start and stop multiple MySQL instances (local or remote) and it does not make any conflict with MyEnv tool.

In this article, I will describe how to configure …

Figured it out. Currently, FPMMM is sending the data as such:

[root@util-mysql1 zabbix]# /usr/bin/zabbix_sender –zabbix-server util-zabbix1.domain.pvt –port 10051 –host ‘util-mysql1.domain.pvt’ –key ‘FromDual.MySQL.innodb.Innodb_buffer_pool_pages_dirty’ –value ‘1384890233’ -vv zabbix_sender [5450]: DEBUG: answer [{ “response”:“success”, “info”:“processed: 0; failed: 1; total: 1; seconds spent: 0.000040”}] info from server: “processed: 0; failed: 1; total: 1; seconds spent: 0.000040” sent: 1; skipped: 0; total: 1

However, with 2.2, the syntax seems to be a bit different, with success:

[root@util-mysql1 zabbix]# /usr/bin/zabbix_sender -z util-zabbix1.domain.pvt -p 10051 -s ‘util-mysql1.domain.pvt’ -k ‘FromDual.MySQL.innodb.Innodb_buffer_pool_pages_dirty’ -o ‘1384890233’ -vv zabbix_sender [5246]: DEBUG: answer [{ “response”:“success”, …

I just want to mention here that Galera 3.1 GA is now released but the online upgrade from MySQL 5.5 to 5.6 has some problems and a workaround should be made (Bug #1251137) to get this process done.

Thank you very much.

One more time. Some debugging, and I’ve identified a possible source of the issue: checkConnectionToZabbixServer in sendData.pm

Commenting out the following fixes the issue: $rc = &FromDualMySQLagent::checkConnectionToZabbixServer($main::gParameter{‘ZabbixServer’}, $main::gParameter{‘ZabbixServerPort’}, $main::gParameter{‘Hostname’});

Hi,

The output you just provided could be gotten by having your database’s tables backed up individually and then restore them the same way.
For this purpose, the backup script should be something like:

#!/bin/bash
cd /backup/script/
for x in `cat tables.txt`
do 
echo $x
mysqldump --user=user_name --password='password' database_name $x  > /backup/$x.sql

echo 'Table '.$x.' completed ...'
done
echo 'Database backed up successfully ...'

Where the tables.txt file contains all tables names separated by new lines.

And the restore script should be something like:

#!/bin/bash
cd /backup/script/
for x in `cat tables.txt`
do 
echo $x
mysql --user=user_name --password='password' database_name < /backup/$x.sql

echo 'Table '.$x.' completed ...'
done
echo 'Database loaded successfully ...'

# pv /backup/database_backup_file.sql | …

Yes, it works in previous version of 1.8.x. I’m trying to migrate to 2.2 because of the VMware monitoring, but I can’t do that until I get the FromDual configuration working on it.

Thanks for pointing out that issue. I made sure the server and agent hostnames are exactly the same all around (in config files, as well as Zabbix server). However, it seems the issue still stands. I did bounce the services to make sure that I’m not running into a Zabbix 2.2 idiosyncrasy.

Has this ever worked before (with < 2.2)?

Error 1305 means, that the agent cannot connect to the zabbix server. We verify: ZabbixServer, ZabbixPort and Hostname

And for hostname the following rule applies: "This MUST match Hostname in Zabbix!"

This is a typical configuration mistake. So I am wondering if it is a config mistake or if something in 2.2 has changed...