sorted by: recent | see :
popular
Content Tagged with Technical + MySQL
Data load speed test , Mysql vs Firebird
Public draft of DRBD Users? Guide released
A public draft version of the all-new DRBD Users’ Guide has just been released. Feedback is more than welcome.
You may want to read the announcement as well.
DRBD 8.2.3 released; boasts online device verification, CPU affinity optimization
DRBD 8.2.3 was released today. Even though just a micro release in terms of version numbering, it comes with a couple of very handy brand new features: on-line device verification, and tunable processor affinity.
(more…)
Open Source Code Contains Security Holes - Firebird moribund ??
New challenges, new synthax
Get ready for the MySQL University lecture on Lua
On December 13 (it means today for most of the readers) at 15:00 CET (14:00 UTC), the MySQL University lecture on Lua will start.
The topic is quite extensive. Even though the lecture is limited to using Lua with MySQL Proxy, yet there is a lot of ground to cover. During a rehearsal session last week, I realized that the whole matter would need much more than one hour if I describe in detail all the introductory material that I originally planned.
So I will reduce the time dedicated to MySQL Proxy architecture, which you can look on your own by reading Getting started with MySQL Proxy. I will cover this matter only briefly during the lecture, so if you know some background, you’ll enjoy the lecture even more.
If you are new to MySQL University lessons, please read the Instructions for attendees and get ready a few minutes before the allotted time.
The slides and the examples are available on the lecture wiki page.
Falcon serendipitous performance findings
While researching partitioning performance (expect an article about this topic soon) I come across the news that the Falcon team has released a Falcon Feature Preview with the latest implementation.
The test I was running was based on 9 server instances , using different combinations of MyISAM, InnoDB, and Archive, with and without partitions.
Since I was at it, I quickly added three instances of MySQL 6.0.4 with Falcon (MySQL Sandbox is really handy in these cases) and added Falcon to the test bench.
As I expected, partitioned Falcon is not particularly impressive, but there was a serendipitous result. In addition to large data warehouse oriented queries, the test fires also 180 OLTP queries, with warm indexes, i.e. after the indexes have been cached in memory thanks to previous queries. The warm queries look like the following:
SELECT * FROM flightstats WHERE AirlineID = '19386' AND UniqueCarrier = 'NW' AND Carrier = 'NW' AND FlightDate = '2002-10-01' AND FlightNum = '1017' AND DepTime = '2002-10-01 06:03:00' AND Origin = 'EWR' AND Dest = 'DTW' -- (all WHERE conditions belong to the PK)
Well, in these queries, Falcon outshines both MyISAM and InnoDB, as shown by the average times.
+----------+----------+----------+----------+ | count(*) | falcon | myisam | innodb | +----------+----------+----------+----------+ | 180 | 0.000254 | 0.002847 | 0.000302 | +----------+----------+----------+----------+
Out of 180 OLTP queries, falcon was faster than MyISAM 143 times, and faster than InnoDB 176 times. The table holds 17 million recs (2 GB)
This is not a complete test. Just a result that I got by chance, but it’s nice to see that Falcon is advancing towards its maturity.
Step-by-step upgrade from DRBD 0.7 to DRBD 8
I’ve been asked by a number of people on how to do an upgrade from DRBD version 0.7 to DRBD 8. This upgrade does necessitate some minimal service down time, but it’s really not rocket science. And no, it does not force you to sync all of your data all over again.
Here’s my quick write-up.
An underrated cluster admin?s companion: dopd
Split brain, with DRBD, is much less of a disaster than in conventional cluster setups employing shared storage. But, you ask, how can I protect my DRBD cluster against split brain in the first place? Here’s how.
Performance tuning DRBD setups
These days, we seem to be getting a lot of inquiries from new or would-be DRBD adopters, especially MySQL and PostgreSQL DBAs wanting to add high availability to their master database servers. And these, unsurprisingly, turn out to be two of their most popular questions:
How will using DRBD affect my write performance?
… and …
What are DRBD’s most important tunables with regard to write performance?
Let’s take a look at both of these issues. Ready? Let’s go.
Why DRBD won?t let you mount the Secondary
As I’m sure you’re aware, DRBD disallows access (any access, including read-only) to a DRBD device in Secondary mode. This always raises questions like the one I’ve taken the liberty to quote here. It came up in a MySQL webinar on replication and HA:
Because of the asynchronous nature of [MySQL] replication we end up with a dilemma when looking at using slaves as read nodes in that the only time we go to the database for information is to build a local cache file, and that local cache file is ONLY removed when information related to that cache file changes, it is NOT based on time. If we had a synchronous method of replication we would then know the cache files were always getting the right information, but because of the asynchronous nature we are prone to old data that never gets invalidated.
One thought I had was to see if using the “backup” DRBD node as a read only type filesystem might accomplish this. I haven’t looked into it much but I know we have DRBD running on a shared storage system now and it only seems that we can mount one of the volumes at a time.
The short version of this, and related questions, is I have a really good reason to mount the Secondary read-only, but DRBD won’t let me, why is that so?
Let’s get into this briefly.
DRBD and kernel upgrades
A question I recently got from our friends at MySQL:
When speaking about DRBD, we mention that if you upgrade your Linux kernel, it is important that you also upgrade the version of DRBD appropriately.
Question: When upgrading the kernel via yum/yast, does it automatically detect that you should also upgrade your DRBD module?
The answer is, as always, a clear and resounding “it depends.” 
Let’s break this down by distribution.
- If you’re on Debian and updating your
linux-imagepackage, dpkg will complain about an unresolved dependency from the DRBD kernel module package, forcing you to update that as well. - If you’re on Debian and adding a more recent
linux-imagepackage, it’s up to you to remember to install a new DRBD kernel module package as well. - If you’re on Debian using the
drbd8-module-sourcepackage,module-assistant a-i drbd8is your friend. - If you’re on SLES, due to extra
Requiresflags in the SLES RPM package, updating your kernel image will fail unless yourdrbd-kmpackage is updated as well. - For other RPM-based distributions such as RHEL or Fedora, again, it’s up to you to install a new
drbd-kmpackage.
We’re currently working on getting sensible kernel dependency into our non-SLES RPMs as well.
Bottom line, in any event: when you upgrade your kernel, it’s generally a good idea to manually double-check your DRBD kernel module package dependencies.
Using DRBD directly (without a file system)
Some applications require direct access to a block device, without an intermediate file system. Some Oracle and MySQL configurations are an example, as are some Xen setups, or IET. Can you do this with DRBD? Sure you can.
However, you need to fulfill two prerequisites:
- Your application can’t access DRBD while it’s in the
Secondaryrole. So, you must make sure DRBD isPrimarybefore your application attempts using that device. - The user in whose context your application runs needs read/write access to that device.
Your cluster manager, when configured properly, normally takes care of item #1 for you. #2 is a little trickier:
Normally, DRBD’s device nodes are owned by root:disk, with permission bits set to 0660 (rw-rw----). So in order to allow your application to use the device, you have two options:
- Add your application user to the disk group (or whatever group owns the
/dev/drbd*devices in your preferred distribution) - Change your permissions as set by udev. There are a number of ways to do this, but my preferred way is to add a file in
/etc/udev/rules.d, containing a rule that matchesKERNEL=="drbd*"and setsOWNER,GROUP, and/orMODEas required.
When you’ve implemented this, as always, don’t forget to test, test, and test. No, one more time.