Feed Aggregator

Introduction

Recently a customer claimed that his queries were slow some times and sometimes they were fast.

First idea: Flipping query execution plan caused by InnoDB could be skipped because it affected mainly MyISAM tables.

Second idea: Caching effects by either the file system cache caching MyISAM data or the MyISAM key buffer caching MyISAM indexes were examined: File system cache was huge and MyISAM key buffer was only used up to 25%.

I was a bit puzzled…

Then we checked the table_open_cache and the table_definition_cache which were still set to their default values. Their according status information Opened_tables and Opened_table_definitions were clearly indicating that those caches are much too small…

The typical reaction is: increase open_files_limit (after increasing the operating system user limits) to a higher value. Unfortunately most of the Linux distributions have a default values for open files of 1024 which is far to low for typical database systems.

Too many open files

But …

Did you test with or without innodb_file_per table?

Did you know about MySQL Bug #62224?

Hello Vineet,

Great if it was a help for you…

I did it from source because of several reasons:

1. I have a complicated environment (up to 50 MySQL instances running potentially in parallel) and deb/rpm packages would not help here. So I have to use binary tar balls. By the way: our 17-node Galera Cluster was built on such a set-up.
2. Version 5.5 is definitely the way to go.
3. The binary tar ball I got did not contain everything I needed (garbd, rsync SST script).
4. I just want to know how it works and do it on my own... :)

So your way is OK as well. Except that you should go for a more contemporary release… MySQL 5.1 is going to die sooner or later…

About the garbd: This is the next but one topic (first I want to have a look about rsync SST (and potentially ssh and LVM snapshot SST)…

If you cannot wait for the blog I suggest you to subscribe to the Forum and ask questions there…

Oli

Thanks Shinguz for such a good post, its very hard to find any blog for Galera patch & implementation.

What i am doing to implement Galera:

I have downloaded galera from

http://launchpad.net/codership-mysql/5.1/5.1.58-21.1/+download/mysql-5.1.58-galera-21.0.8.3-x86_64.tgz

and ran command:

./mysql-galera -g gcomm:// start ./mysql-galera -g gcomm://{IPADDR} start

its working very fine.

But i am confused about you have downloaded mysql source and then complied it. How its different from this implementation.

Another thing is i am not getting any help on Galera Arbitrator. How its work and its required any other implementation steps.

http://www.codership.com/wiki/doku.php?id=galera_arbitrator

Thanks in advance.

Introduction

MySQL/Galera synchronous Multi-Master Replication consists of 2 parts:

• The wsrep patches for MySQL (codership-mysql) and
• the Galera Replication Plugin (galera).

If you do not want to download the prepared binaries you can build it on you own.
First you have to download the native MySQL sources, then patch it with the Galera wsrep patches and compile it. In a second step you have to build the Galera Plugin.

This is especially useful because in the standard Galera binary tar balls the garbd (and possibly other tools) is not provided.

The following steps describe how to do it:

Prepare a patched MySQL

Download MySQL Sources

Download the normal MySQL source code:

wget http://mirror.switch.ch/ftp/mirror/mysql/Downloads/MySQL-5.5/mysql-5.5.15.tar.gz

Download wsrep Patch

Download the wsrep Patch for MySQL:

wget http://launchpad.net/codership-mysql/5.5/5.5.15-21.2/+download/mysql-5.5.15-wsrep_21.2.patch

Patch MySQL

Patch MySQL as follows:

cd /tmp
tar xf /download/mysql-5.5.15.tar.gz
cd mysql-5.5.15 …

Angemeldet haben sich: Volker, Erkan, Mario, Oli, Ronny, (Norbert), …

Es ist um 18 Uhr im El Encanto unter meinem Namen reserviert…

Hallo zusammen,

Einige von Euch haben sicher vor, an die DOAG Conference 2011 nach Nürnberg zu kommen, welche vom 15. bis 17. November statt findet.

Neben zahlreichen interessanten Vorträgen über MySQL soll auch das Gesellschaftliche nicht zu kurz kommen. Daher planen wir am Dienstag, 15. November einen MySQL Community Abend mit gemeinsamem Nachtessen in einer netten Lokalität in Nürnberg. Ca. 18:30 - 19:00

Ihr seid alle herzlich eingeladen, daran teilzunehmen (auch wenn Ihr es nicht auf die DOAG Conference 2011 schafft).

Wer kennt ein nettes Restaurant wo wir hin gehen könnten (ich wüsste noch ein peruanisches Restaurant (nein, es gibt KEINE Meerschweinchen zu essen!) unterhalb der Burg!)?

Wer kommen möchte, bitte asap bei uns melden (contact@fromdual.com), damit wir genügend Plätze reservieren können.

Wenn die Speaker das Event während Ihres Vortrags ebenfalls ankündigen würden, wäre das sehr nett! Und wenn Ihr noch alle Leute, welche ebenfalls Interesse daran haben, informieren würdet wäre das toll!

Liebe …

Michal Hrušecký did a Survey of the usage of different MySQL branches on OpenSuSE Linux. The results can be found in his article: MySQL survey results (Oct 17, 2011).

We have seen a customer running a MySQL instance with about 1 Mio InnoDB tables. He had some problems (hick-ups) but basically it worked…

This depends a bit on version and distribution. Debian/Ubuntu for example sets bind-address to localhost/127.0.0.1 afaik.

Look for my.cnf in /etc/ or /etc/mysqld.

Other locations you can find with:

mysqld --help --verbose | grep my.cnf

MySQL was connected with localhost by default, am I right? I would like to make it not only localhost, so that my other computer can get access or connected to the database on my PC. I read through some website and they suggest to change the bind address in my.cnf. Unfortunately, I can’t locate the file and failed to find the line to change.

Hello BBlue,

Can you please elaborate a bit more what the problem is? It is not clear to me where you stuck.

Regards, Oli

I had read your article and it does lighten my mind up. However, all my efforts on surfing for information on how to set up the shared server wasn’t enough. I can’t find a clear picture on how to set it up, even i just want to connect my virtual machine with database in my own pc. Hope can get some help.

Galera features

Galera provides the following features:

• 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 row level replication
• Direct client connections
• Drop-in replacement for native MySQL

Galera benefits

Benefits using Galera Replication:

• High Availability
• No slave lag
• No lost transactions
• No more data inconsistency
• Smaller client latencies
• Read scalability and write throughput improvement (3 times and more, depending on your workload)

Notes about Galera

Some notes about Galera the synchronous Replication for MySQL:

• Synchronous true multi-master MySQL/Galera Replication can substitute MySQL Cluster, asynchronous MySQL Master-Master Replication and Schooner.

• Galera is sometimes seen as competitor of MySQL Cluster with the big advantage of using the general purpose …

Table of Contents

• Reasons to Upgrade MariaDB or MySQL
• How to Upgrade MariaDB or MySQL
• MariaDB and MySQL Reserved Keywords
• MariaDB and MySQL Upgrade Problems we hit in real life
  • Upgrade MySQL 4.1 to MySQL 5.1
  • Upgrade MySQL 4.1 to MySQL 5.6
  • Upgrade MySQL 5.0 to MySQL 5.1
  • Upgrade MySQL 5.0 to MySQL 5.6
  • Upgrade MySQL 5.0 to MariaDB 10.3
  • Upgrade MySQL 5.1 to MySQL 5.5
  • Upgrade MySQL 5.5 to MySQL 5.6
  • Upgrade MySQL 5.6 to MySQL 5.7
  • Upgrade MySQL 5.7.12 to MySQL 5.7.21
  • Upgrade MySQL 5.7 to MySQL 8.0
  • Upgrade MariaDB 5.5 to MariaDB 10.5
  • Upgrade MariaDB 10.2 to MariaDB 10.3
  • Upgrade MariaDB 10.2 to MariaDB 10.4
  • Upgrade MariaDB 10.2 to MariaDB 10.5
  • Upgrade MariaDB 10.3 to MariaDB 10.5
  • Upgrade MariaDB 10.3 to MariaDB 10.6
  • Upgrade MariaDB 10.4 to MariaDB 10.5
  • Upgrade MariaDB 10.5 to MariaDB 10.6
  • Upgrade MariaDB 10.11 to MariaDB 11.4
  • Upgrade MariaDB 11.4 to MariaDB 11.8
• Migration between MySQL, MariaDB and Percona Server
• Migration from MyISAM to InnoDB
• Migration from MyISAM to Aria
• Switching from Statement Based …

Summary: In this article we have a short look at a simple MySQL example program written in Vala.

Recently a customer pointed me to a programming language called Vala. Vala is a C-style programming language generating C code which afterwards can be compiled and linked with the normal gcc.
This I found pretty useful to not mess around with pointers and all this stuff in C and to be capable anyway to write C programs for some projects I had in mind in my head since long.
Vala is mostly used around the Gnome Project.

Vala

Vala declares itself as:

Vala is a new programming language that allows modern programming techniques to be used to write applications … Before Vala, the only ways to program for the platform were with the machine native C API, which exposes a lot of often unwanted detail, with a high level language that has an attendant virtual machine, such as Python or the Mono C# language, or alternatively, with C++ through a wrapper library.
Vala is different from all these other techniques, as it …

Alternatively, there is this story about a guy looking for a fast compression algorithm for backup. It’s intention is to have compression “inline”, which means it should never become a bottleneck slowing down file transfer. It seems he found a solution : http://www.mail-archive.com/fsck@truman.edu/msg00123.html

Our Advanced MySQL Developer Workshop is over now and IMHO it was quite a success.
During the workshop it is planned to have some exercises. If you are curious and if you want to test or train your MySQL skills, find the exercises here: Advanced MySQL Developer Workshop Exercises.

The solutions are available on request as well.

If you like those exercises we could also provide the exercises of our Advanced MySQL DBA Workshop. Please let us know if you are interested in…

Have fun.

With a partner we are planning and Advanced MySQL DBA workshop. As the name says it should be a workshop. Its planned duration is 2 days. So within 2 days we can cover 4 to 8 topics more deeply. Requirements: VirtualBox, VMware, own Laptop?

Possible exercises during the workshop

• Set-up a Master-Master replication with 2 Slaves
• Load balance on master with MySQL Proxy and on Slaves with LVS.
• Design a little schema and load with data from foodmart
• Do a backup with XtraDB and LVM
• Do a PITR and and InnoDB crash recovery (from some samples)
• Find some discrepancies between master/slave
• Run some synthetic benchmark and monitor

Planned contents:

Contents

Introduction
  Admin
  Who we are?
  Tasks of a DBA
  Discussion for focus

Planning and evaluating
  Collecting information
    Business plan, amount of users, expected traffic,
    peak and average traffic, requirements, amount of data,
    in what time range
    Information Life Cycle Management
  Plan architecture
    DB vs. NO-SQL vs. no database
    Storage …

Workshop topics

Duration: 3 days, Exercises

• Partitioning
  • Partitioning by time (temporal data)
  • Optimizer and Partitioning
  • Limitations
  • New 5.5 and 5.6 features of Partitioning
• InnoDB Architecture
  • Clustered PK
  • InnoDB Internals
  • InnoDB Online features
  • New Performance Features in 5.5 and 5.6
  • Operations of InnoDB
• Performance Tuning and Benchmarking
  • Basics, Latency and Throughput
  • Profiling
  • Benchmarking
  • Benchmarking Tools
• Memcached
  • Caching strategies
  • Operations
  • ding
• HA and replication solutions
  • Replication
  • Scale-Out
  • DRBD
• Blob streaming engine
• Handler Socket and Memcached-API