You are here

Shinguz's blog

MyEnv (MySQL and MariaDB BasEnv)

What is MyEnv?

MyEnv is a tool to run several MySQL or MariaDB database instances on ONE host. You can even run multiple database instances with different binary versions. We call this multi-instance set-ups.

With MyEnv a multi-instance set-up is more comfortable to handle than with mysqld_multi (old) or Systemd services (new) and it provides more useful functionality.

MySQL Active - Active Clustering

It is possible to use an active - active shared-disk cluster in MySQL in some cases. For doing this you have to fulfill the following requirements:

Transaction performance

Transaction performance relates among other things from I/O performance. This means hard disk performance.

Hard disk performance

When you select a hard disk, an important feature to consider is the performance (speed) of the drive. Hard disks come in a wide range of performance capabilities. As is true of many things, one of the best indicators of a drive’s relative performance is its price. An old saying from the automobile-racing industry is appropriate here: "Speed costs money. How fast do you want to go?"

Round-Robin Database Storage Engine (RRD)

In a round-robin database (RRD) usually time-series data like network bandwidth, temperatures, CPU load etc. is stored. The data is stored in the way that system storage footprint remains constant over time. This avoids resource expensive purge jobs and reduces complexity.

RRD

MySQL does NOT yet provide this kind of storage engine. Although some people were thinking about and some prototypes exists.

SATA Flash Solid State Disk up to 160 Gbyte announced!

The price for a 160 Gbyte disk will be around USD 15'000. This is still a bit expensive. But the access time is around 0.5 ms (both for reading and writing) which is around 10 times faster than a normal 15'000 rpm SCSI disk! The disk has NO cache because it is a cache itself (according to the supplier. Maybe this will change in the future). And the lifetime of a cell is > 5 mio writes. For the same performance one needs usually an array of around 10 disks. If your database is heavily write-I/O bound you should consider this solution.

MySQL Multi-Master - Single-Slave - Replication

MySQL provides its replication for High Availability (HA) and for read Scale-out. Generally it is known that in a MySQL replication you can only replicate from one Master to many slaves. In this paper it is shown how a set-up can look like to replicate from two masters to one slave.

Caution: Handle this information with care!!!

Profiling MySQL with oprofile

Taxonomy upgrade extras: 

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

Materialized Views (MV) with MySQL

Materialised View (MV) is the pre-calculated (materialised) result of a query. Unlike a simple VIEW the result of a Materialised View is stored somewhere, generally in a table. Materialised Views are used when immediate response is needed and the query where the Materialised View bases on would take to long to produce a result. Materialised Views have to be refreshed once in a while. It depends on the requirements how often a Materialised View is refreshed and how actual its content is.

Pittfalls with Federated Tables

A Federated Table is a table which points to a table in an other MySQL database instance (mostly on an other server). It can be seen as a view to this remote database table. Other RDBMS have similar concepts for example database links.

Read more about the pittfalls with MySQL Federated Tables...

Some more MySQL tools added

We have added some more tools to our MySQL consulting tools collection.

Some more benchmarks added

Taxonomy upgrade extras: 

We have added some more database benchmarks to our collection.

More details you can find on our Benchmarking page.

MySQL storage engines

Taxonomy upgrade extras: 

One of the big advantages of MySQL is its concept of pluggable Storage Engines (SE). This means you can choose the most optimal storage engine for your needs. This also has a disadvantage: You have to know what you are doing...

More details you can find here: MySQL pluggable Storage Engines (SE).

Full-Text Search Engines

MySQL has also a Full-Text Search Engine built in. But this Search Engine is not as fast a you probably want to. Thus there are some alternative Full-Text Search Engines which might be working together with MySQL.

Stealthy migrating MySQL tables and MySQL data access interfaces using enlarged updateable VIEW functionality

Applications occasionally require redesign. However, redesigning an application cannot be done in one step because the application is distributed or several versions of applications must be supported. MySQL 5.0 provides the necessary means to stealthy migrate your data. In a short overview let's look at what we plan to do: Stealthy Migration (PDF 98.7 kByte).

Pages

Subscribe to RSS - Shinguz's blog