I came across a scenario where we were concerned about the size of a table, a 134G table, its a pretty huge table and growing fast. The nature of the table – its a table which contains logs , sort of a audit table. Some fundamental questions around this table’s use: 1) would it have a lot of reads ? — Answer – No 2) is it always insert only? — Answer – Yes 3) current strategy to restrict size ? — Answer – delete data from table. 4) does one need backup of the table, i.e. is it very important ? — Answer – No Well based on the above, it would seem that logging is the theme. Schema of the table: CREATE TABLE `SystemLogs` ( `column1` varchar(255) DEFAULT NULL, `column2` varchar(255) DEFAULT NULL, `column3` varchar(255) DEFAULT NULL, `column4` varchar(255) DEFAULT NULL, `column5` text, `column6` text, `timestamp` datetime DEFAULT NULL, `id` int(11) …[Read more]
Well.. just wanted to share the more technical details I went into when preparing the “A MySQL Odyssey: A Blackhole crossover” for the OpenSUSE Conference in Thessaloniki, July 2013. There’s a youtube version as well, of the actual session, on the opensuse channel. http://youtu.be/Nt2w1UvKEp0. (Audio kicks in at minute 6:40 so enjoy my miming act).
5.6 Enterprise Edition -> edelivery.oracle.com
Win: mysql-5.6.11-MySQL Installer 188.8.131.52 Package-V38226-01.zip
Linux: mysql-5.6.11 TAR for Generic Linux (glibc2.5) x86 (32bit)-V38228-01.zip
Oracle Linux 192.168.56.102 …
Well.. just wanted to share the more technical details I went into when preparing the "A MySQL Odyssey: A Blackhole crossover" for the OpenSUSE Conference in Thessaloniki, July 2013. There's a youtube version as well, of the actual session, on the opensuse channel. http://youtu.be/Nt2w1UvKEp0. (Audio kicks in at minute 6:40 so enjoy my miming act).
5.6 Enterprise Edition ->
Blackhole tables are often used on a so-called “relay slave” where some operation needs to happen but no data needs to exist. This used to have a bug that prevented AUTO_INCREMENT columns from propagating the right values through replication, but that was fixed. It turns out there’s another bug, though, that has the same effect. This one is caused when there is an INSERT into a Blackhole table, where the source data is SELECT-ed from another Blackhole table.
I think it’s wise to keep it simple. MySQL has tons of cool little features that theoretically suit edge-case uses and make ninja tricks possible, but I really trust the core plain-Jane functionality so much more than these edge-case features. That’s precisely because they often have some edge-case bugs, especially with replication.…[Read more]
In our March DBJ article we talked about some of the storage engines to choose from with MySQL. With it’s plugin storage engine architecture, you have a range of options. In our April article we continue to discuss a further selection of storage engines, and what features they offer to the DBA and database architect.
I spent Friday examining the systems for a more traditional consulting gig (in case you did not know, Pythian has offered more traditional consulting and emergency services in addition to our remote DBA offering for our entire 12 year history). It is a familiar story to many people — the database performance was unacceptable.
The company had a few log tables that had huge amounts of inserts
and foreign keys, so they used InnoDB. Unfortunately, they also
used a GUID as a primary key (which is getting more and more
common these days, but long primary keys with InnoDB slow down
commands a great deal) —
varchar(32) and utf8.
That’s right — their primary key for many of these tables was 96 bytes long (32 characters * 3 bytes per character), and as an InnoDB table, the primary key is clustered with …[Read more]
It turns out that there is a bug that causes replication on a 5.1
slave to fail when reading LOAD DATA statements from a
About a month ago, I set up a data warehouse for an old 4.1.21 system that can't be upgraded. I decided to use the latest 5.1 beta to take advantage of partitioning, but within the first day replication stopped rather suddenly and with a nondescript error:
[ERROR] Error running query, slave SQL thread aborted.
Fix the problem, and restart the slave SQL thread with
"SLAVE START". We stopped at log 'xxx' position xxx
Using mysqlbinlog, I compared the relay log and the masters
binary log around that position, and noticed the relay log
appeared corrupt! I don't recall the exact steps I took, but I
isolated the problem to a LOAD DATA INFILE statement, and filed
my first issue with MySQL Enterprise support.
The great folks at MySQL Support had a …