Feed Aggregator

What are the pros vs cons of using replication rather than the drbd method in your howto?

Dear MySQL and MariaDB User,

With this Newsletter you receive the News about FromDual for Spring 2011:

Topics

• Technical Information
• Support and Remote-DBA services for MySQL and MariaDB
• Open dates
• News about FromDual
• News about MySQL

Technical Information

We have compiled the following technical information for you:

• Configuration of MySQL for Shared Hosting: Operating MySQL in a Shared Hosting environment is one of the trickiest things you can do with MySQL. Some hints and information about it you can find in the blog post Configuration of MySQL for Shared Hosting.
• How to make MySQL high available (HA) we have summarized in the blog post MySQL HA (high availability) cookbook.
• In the same time we have extended our MySQL Performance Monitor with DRBD Monitoring capabilities: MySQL Performance Monitor with DRBD monitoring capabilities.

Who has missed our talks about HandlerSocket and similar Architectures, NoSQL for MySQL and MySQL Replication - Scale-Out, Master-Master, Backup at the DOAG SIG in München, …

We do not currently operate any newsletter.

Old Newsletter

• Summer 2019
• Spring 2015
• Summer 2014
• Winter 2013
• Spring 2013
• Spring 2012
• Winter 2012
• Autumn 2011
• Summer 2011
• Spring 2011

MySQL Proxy is not full multithreaded, the network layer is multithreaded, but not the LUA layer, what does it mean? As soon as you use LUA, you are again using just a single thread.

We are planning on converting the LUA layer to multithread, but it is not there yet.

Regards,

Diego

A customer who is handling digital certificates had a problem with one of those. So we had to investigate.

Because the certificate is in binary form it is stored in a BLOB and we had to extract it from the database to do some verifications.

What first came to my mind was to extract the certificate with the SELECT INTO OUTFILE command. But the verification tool complained and told us that the certificate has a wrong format.

Luckily I found in the documentation the following statement: If you use INTO DUMPFILE instead of INTO OUTFILE, MySQL writes only one row into the file, without any column or line termination and without performing any escape processing. This is useful if you want to store a BLOB value in a file.

We tried it out with:

mysql> SELECT certificate
  INTO DUMPFILE '/tmp/certificate.bin'
  FROM identity
 WHERE id = 42;

and it perfectly worked. The certificate verification tool did not complain anymore and we could continue investigating, why this certificate had a problem at all…

With …

Percona Server has an option to limit amount of memory consumed by dictionary entries. This might be similar to what is in 5.6 from Oracle; I have not looked at the 5.6 code yet. We created this feature specifically for hosting providers :-)

There are a number of other improvements for shared hosting providers, too, such as “don’t crash the whole server when a single table gets a corruption” or “allow import/export of individual tables to another server”.

If you ask around about shared hosting setups with MySQL everybody is frightened. In fact it looks like shared hosting is one of the most difficult setups you can get.

The number of users is big, the number of tables huge and the load pattern is completely unpredictable and the queries often very, let us say: non-optimal.

Here one of the DBA wisdoms come into play: Controlling developers is like herding cats.

If you talk to the Shared MySQL Hoster they confirm that this setups are very demanding!

Why is shared hosting of MySQL databases so difficult to operate?

Several different problems come into play:

• The number of database users are typically hundreds or even thousands.
• There are many databases (schemata). Typically 1 to 10 per user.
• There is a huge number of tables (a mixture of InnoDB and MyISAM).
• The application and user behavior is more or less unpredictable and completely non-controllable.

The number of tables and users is a problem because it is out of the range of typical administrators experience …

One of challenges with shared hosting is monitoring and controlling single user/db usage. For this I think is useful Percona Xtra DB with detailed per-user/index/host/table statistics and counters.

P.

Hi vishnuraj

Simple examples are always good to explain or to try to understand. So I created the following table:

In my database I have 3 fields as id,medicinename,quantity.
In medicinename I have many different names. Many names comes twice or thrice.

+------+----------------------+---------+
| id   | medicinename         | quantiy |
+------+----------------------+---------+
|    1 | Trifolium arvense L. |      25 |
|    2 | Agave americana      |     120 |
|    3 | Hypericum perforatum |      12 |
|    4 | Trifolium arvense L. |      35 |
|    5 | Agave americana      |     120 |
|    6 | Trifolium arvense L. |     120 |
+------+----------------------+---------+

Now I need to fetch the data from database for particular medicine name only (this medicine name comes nearly 10 times) say for example if the medicinename is “A” it comes in id 1,4,9,12,25.

SELECT * FROM medicine WHERE medicinename = 'Trifolium arvense L.';
+------+----------------------+---------+
| id   | medicinename …

Has anyone tried to compile the code under Windows?

Hi everybody Please rectify my doubts. In my database i have three fields as id,medicinename,quantity. In medicine name i have many different names.Many names comes twice or thrice.Now i need to fetch the data from database for particular medicine name only(this medicine name comes nearly 10 times)say for example if the medicinename is “A” it comes in id 1,4,9,12,25.Now i want to fetch the data from id 1 only.Whent the quantity comes to “0” in id “1”,i want to fetch the datas from next id.Also when the quantity comes to “0” the row shold be deleted automatically.Please help me to do this.

FromDual has released today the next version v0.6 of its FromDual Performance Monitor for MySQL.

The most important improvement of the new release is the new monitoring module for DRBD devices which are often used in MySQL High Availability (HA) set-ups.

More information about the new functionality added you can find in the article MySQL Performance Monitor with DRBD monitoring capabilities, MySQL Performance Monitor and in the MySQL HA (high availability) cookbook.

The FromDual Performance Monitor for MySQL is now available on our download page.

Follow us on Twitter…

The FromDual Performance Monitor for MySQL is now available on our download page.

An other customer was asking the following question related to this blog post:

Does this mean that the other node cannot even respond to read only queries?

Yes this is exactly what it means. It has nothing to do with Master-Slave Replication where you have for example a read-only Slave. In simple set-ups on the passive node there runs nothing except the Heartbeat and the DRBD process.

More details you can find on Wikipedia.

Some people who have their set-ups really under control run on the “passive” node some development or testing systems which are shut down in case of a failover.

This is to avoid the situation stated in Wikipedia: “This configuration typically requires the most extra hardware.”

But this I would not really call a passive node any more.

One other extreme case I have seen is that a customer runs several different production instances on both nodes and when one node goes down the other node takes over ALL instances. But you have to make sure, that both nodes in all …

A friend made some private comments and allowed me to post them. IMHO they are worth considering. translate.google.com may help:

We configured Heartbeat to use the version 1 mode because of 3 reasons:

HA ist über Verfügbarkeit. Einfachheit der Konfiguration und eine geringe Anzahl zu testender Systemzustände sind ein wichtiger Punkt. CRM, und alles was danach kommt lösen keine Probleme sondern schaffen sie. Die Konfiguration wird so komplex, daß man sie nicht mehr ganz testen kann. Es geht darum, die simpelste Lösung zu bauen, die man finden kann und die dann gesamt durchzutesten. V1 Config ist genau das - active/passive und dann einmal sauber testen, fertig. Schon alleine deswegen will man den Rest nicht, selbst wenn er presumably correct wäre. Ja und Human Readable ist auch wichtig.

In practices you should avoid virtual systems because of performance reasons.

ja, auch hier ist performance wieder nicht vorhersagbar. und damit ist das ganze nicht sauber testbar. für einen demo aufbau, test der logik ist …

In the following article I have summarized some steps and hints to set-up a MySQL active/passive failover Cluster also sometimes called MySQL HA.

With such a set-up you can achieve a 99.99% (4x9) HA set-up for MySQL (52 minutes downtime per year). The same procedure also works for PostgreSQL, Oracle and other database systems running on Linux (DRBD runs on Linux only).

The concept

The concept of an active/passive failover Cluster is the following:

• You have 2 servers (also called nodes).
• They communicate over a cluster software (Heartbeat, Corosync, OpenAIS, Red Hat Cluster Suite).
• They are running on DRBD or have a shared storage (SAN, NAS) connected to both nodes.
• MySQL is only running on ONE node (active), the other node does nothing (passive).
• You reach MySQL over a Virtual IP (VIP)
• In case of a problem the cluster software should detect those and failover the resources including the VIP to the passive node. There your database should continue working a few minutes (from
  < 1 Min up to
  > 60 Min) …

The FromDual Performance Monitor for MySQL has now a new module for monitoring DRBD. Since more an more of our customers want to run MySQL High Availability solutions based on DRBD we have implemented this module.

We gather all information which are reported with the command:

# cat /proc/drbd

version: 8.3.8 (api:88/proto:86-94)
 0: cs:Connected ro:Secondary/Primary ds:UpToDate/UpToDate C r----
    ns:1241240 nr:14311680 dw:15033532 dr:1241281 al:198 bm:52 lo:0 pe:0 ua:0
    ap:0 ep:1 wo:b oos:0

Now you can easily find the DRBD version which is installed on your systems, the connection state, the role, the disk state and the used protocol of your DRBD device.

Further we have added graphs for network and disk traffic and for internal data structure activity.

When DRBD is down we will rise an alert:

The new DRBD module is included in the Version v0.6 of our MySQL Performance Monitoring which is planned to be released next week. The FromDual Performance Monitor for MySQL is based on Zabbix.

If you have …

Be careful when using SAN (Storage Area Networks) or similar shared storage solutions (and any other virtualization, consolidation or cloud solutions).

This week it happened again: A customer called us because he had some troubles with his on-line shop (note the date!). Everybody in his company complained that the databases are answering slowly.

When looking on the box (with iostat) we have seen some I/O load and some pending reads in InnoDB (SHOW ENGINE INNODB STATUS and SHOW GLOBAL STATUS LIKE ‘InnoDB%’) and a very bad InnoDB buffer pool hit ratio (about 80%, yes I know hit ratios are bad, but sometimes they are helpful).

Customer affirmed that he has nothing changed on his box since a few days. And the day before everything was working fine but this day in the afternoon the system became suddenly slow. He further told me that they are producing some monthly reports. But only on the slaves.

We found one query running since 25 minutes so I assumed first this was the evil thing. After we killed …

Hi Henrik,

Yes great idea!

Basically the differences are: Zabbix is an integrated Monitoring and Alerting solution where Nagios is an Alerting Solution with Monitoring modules. Zabbix is technologically newer than Nagios. I have heard from some customers that they are not happy with how Fixes and Changes are included into Nagios. Incinga is a fork of Nagios and seems to address these problems.

More information you can find in the discussion: Can some one recommend some good monitoring tools for administration and checking server status?

And in our MySQL Monitoring Solution summary.

Our modules for Aria, Master, MyISAM, MySQL, PBXT, Processes, Linux Server, Slave and XtraDB are under the GPL and free of Charge. The modules for InnoDB and NDB are free of Charge for FromDual customers or they can be purchased for a small amount of money. If you do not like to spend money you can spend in time and build your own modules. Zabbix is a great architecture because you are very flexible and can build whatever you like …

Due to lack of customer interest our Emergency Intervention service for MySQL, MariaDB and Percona Server has been withdrawn.

Please refer to our remote-DBA Services for MySQL, Galera Cluster for MySQL, MariaDB, and Percona Server for general MySQL DBA work OR our Support Services for MySQL, Galera Cluster for MySQL, MariaDB and Percona Server in case you expect and Emergency and require Emergency Intervention.