You are here

mysql

The handler_read_* status variables

Because I do a lot of Performance Tuning gigs I get often in contact with these status variables. In the beginning I had a problem to understand them and now I have a problem to memorize the relation of the name and the meaning. Therefore I wrote this little summary:

Prepare the example

To show you the effect I have worked out a little example:

English

Performance Tuning Key for MySQL

This MySQL Performance Tuning Key should give you a guide how to best tune you MySQL database systematically... It should also work similar for other RDBMS.

Also check our MySQL Performance Monitor

For a database configuration tuning only please look first at our MySQL database health check.

Undefined

MySQL Monitoring solutions

Basic solutions (CLI)

Those solutions are run from the command line (CLI):

English

Profiling MySQL with oprofile

Why is is data load with LOAD DATA INFILE so much faster?

Probably the answer to this question is already known. But we want to prove it and by the way learn to deal with oprofile.

For the test MySQL 5.0.28 was used and 100k rows were loaded into a table sales which looks as follows:

English

FromDual - The MySQL consulting company goes operational today!

Taxonomy upgrade extras: 

Hello everybody,

One month earlier than planned we have the great pleasure to announce you that the company called FromDual goes operational today!

We are excited about this step and it is an new era in our personal evolution to get back in full-contact with customers and solve their real life day-to-day MySQL problems.

So we are happy hearing from you and to help you solving your individual MySQL problems...

Logging users to the MySQL error log

Problem

A customer recently showed up with the following problem:

With your guidelines [ 1 ] I am now able to send the MySQL error log to the syslog and in particular to an external log server.
But I cannot see which user connects to the database in the error log.

How can I achieve this?

Idea

During night when I slept my brain worked independently on this problem and in the morning he had prepared a possible solution for it.

Can you trust your MySQL backup?

Today a customer with corrupted data files showed up. When we enquired a bit more he told us that he had a broken I/O controller. This is one of the worst things which can happen to you!

The reason is the following: When a I/O controller starts to die it often does not happen immediately. The controller dies slowly producing more and more corrupt data. When you just write data without checking or reading them it can take days or even weeks until you discover the problem.

What is CHECK TABLE doing with InnoDB tables?

Recently we had a case where a customer got some corrupted blocks in his InnoDB tables. His largest tables where quite big, about 30 to 100 Gbyte. Why he got this corrupted blocks we did not find out yet (disk broken?).

When you have corrupted blocks in InnoDB, it is mandatory to get rid of them again. Otherwise your database can crash suddenly.

MySQL on VMware Workstation/DRBD vs. VMWare ESX Server/SAN

Taxonomy upgrade extras: 

Or an active-active failover cluster à la VMware.

Today I have learned about a totally crazy/cool looking architecture where the expensive VMware ESX server was replace by a free/cheap VMware Workstation version in combination with DRBD.

Basically DRBD we name the poor man's SAN and that is exactly what this customer is doing. He replaced the SAN with DRBD and now he can easily move one VMware instance to the other host. Possibly it is not that flexible and powerful as an ESX Server but also not so expensive...

The architecture looks as follows:

The battle against Oracle is probably over but has the real war begun yet?

According to different sources from the web the decision about the Oracle - Sun merger has been approved by the European commission soon. So at least in the West it is clear what is going on. Let us see what the East decides... [ 1 ], [ 2 ].

MySQL reporting to syslog

Taxonomy upgrade extras: 

There are 2 different possible situations you can face when you have to deal with MySQL and syslog:

  • MySQL is used as back-end for syslog to store the logging information. [ 6 ]
  • MySQL itself should report to the syslog.

In this blog article we look at the second situation: How can you make MySQL reporting to the syslog.

My wish for the New Year: MySQL DBA's, please install iostat on your servers!

Taxonomy upgrade extras: 

iostat is a very handy tool to help you investigating what kind of performance problems you have. Especially your databases can cause a lot of troubles to your I/O system and thus it would be very nice if every DBA has installed iostat on all of his MySQL database servers.

MariaDB and MySQL Consulting

FromDual delivers neutral and vendor independent on-site consulting services on MariaDB, MySQL, Galera Cluster and Percona Server. Our experienced consultants recommend you the best solutions fitting to your needs.

We help you in delicate matters as:

English

FromDual Tools for MySQL and MariaDB

Performance Monitor for MySQL (mpm)

The FromDual Performance Monitor for MySQL (mpm) is a monitoring solution for MySQL, MySQL Cluster, Galera Cluster, Percona Server and MariaDB databases. The mpm can be downloaded from here.
It is based on the monitoring solution Zabbix.

The Performance Monitor for MySQL (mpm) supports the following Storage Engines:

Undefined

MySQL Database Health Check

Release v0.28

  • On this page you can check the health of your MySQL/MariaDB database.
  • If you want to find out how the MySQL Health Check works try out the examples.
  • If you are logged in, our CMS remembers your entry for your next visit.
  • If we should send you some advices about the findings please fill in the following form:
Contact
Name: Undefined

MySQL useful add-on collection using UDF

I really like this new toy (for me) called UDF. So I try to provide some more, hopefully useful, functionality.

The newest extension I like is the possibility to write to the MySQL error log through the application. Oracle can do that since long. Now we can do this as well...

A list of what I have done up to now you can find here:

Using MySQL User-Defined Functions (UDF) to get MySQL internal informations

In one of my previous posts I was writing about how to read other processes memory [ 1 ]. As an example I tried to get the value of the hard coded MySQL internal InnoDB variable spin_wait_delay (srv_spin_wait_delay).

In this example we were using gdb or the operating system ptrace function to retrieve this value. This method has the disadvantage that it is pretty invasive.

Determine in MySQL if we are in summer time or winter time (daylight saving time, DST)

Recently a colleague at Sun was asking me if MySQL can tell him to determine if we are currently in summer time or winter time. He was doing some data analysis of his house where he has installed solar panels.

I am not aware of what he wants to do exactly, but possibly he wants all the data in solar time. So UTC could help him because UTC does not change much over time.

Next thing which came to my mind is, that possibly the good place to do such mathematical calculations is the application code and not the database.

MySQL licenses for dummies

Taxonomy upgrade extras: 

The following summary shows my personal understanding of MySQL 5.1 licenses, packages and products. It does not necessarily reflect 100% the way MySQL understands it. But after all the discussions I hope it is as close as possible to the reality:

MySQL Embedded Database Server

(Download -> OEM Software)

Test application for MySQL high availability (HA) set-up

When I set-up a MySQL HA environment for customers I usually do some final failover tests after configuring the whole beast.

To check if the application behaves like expected I always run my little test application (test.sh) from the server(s) where the customers application runs. It displays "graphically" how the application behaves and you can show to the customer immediately what is going on...

Make sure, that you point it to the virtual IP (VIP) or the load balancer (LB).

Citation of the week

Taxonomy upgrade extras: 

Das dreieckige Rad hat gegenüber dem viereckigen einen gewaltigen Vorteil: Ein Rumms weniger pro Umdrehung!

Translation:

The triangular wheel has one enormous advantage over the quadrangular: One knock less per revolution!

Maybe not new, but I have not heard it yet and I love it. It was about reinventing functionality in a well known product...

Active/active failover cluster with MySQL Replication

Electing a slave as new master and aligning the other slaves to the new master

In a simple MySQL Replication set-up you have high-availability (HA) on the read side (r). But for the master which covers all the writes (w) and the time critical read (rt) there is no HA implemented. For some situations this can be OK. For example if you have rarely writes or if you can wait until a new Master is set up.

But in other cases you need a fast failover to a new master.

Pages

Subscribe to RSS - mysql