While working with different clients I happen to run across very large slow log files from time to time.  While several opinions on how they should be rotated exist. Many of these opinions use log rotate and the flush logs command,  I prefer not to flush my binary logs though. This is why I agree with Ronald Bradford's blog post from years ago on  how to do this.

• http://ronaldbradford.com/blog/the-correct-approach-to-rolling-mysql-logs-2010-02-22/

I have taken it a little further and scripted the steps. The bash script is built with MySQL 5.6 and the mysql_config_editor in mind it can be used on older versions of MySQL as well.

The script …

For some reason, we’ve been getting a lot of issues with ORDER BY optimization recently. The fixes have passed Elena Stepanova’s scrutiny and I’ve pushed them to MariaDB 10.1. Now, MariaDB’s ORDER BY ... LIMIT optimizer:

• Doesn’t make stupid choices when several multi-part keys and potential range accesses are present (MDEV-6402)
• Always uses “range” and (not full “index” scan) when it switches to an index to satisfy ORDER BY … LIMIT (MDEV-6657)
• Tries hard to be smart and use cost/number of records estimates from other parts of the optimizer (MDEV-6384, MDEV-465, MySQL …

MariaDB 5.5.40 was recently released (it is the latest MariaDB 5.5), and is available for download here:

https://downloads.mariadb.org/mariadb/5.5.40/

This is a maintenance release, and so there are not too many big changes of note, just a number of normal bug fixes. However, there are a few items worth mentioning:

• Includes all bugfixes and updates from MySQL 5.5.40 (MySQL 5.5.40 Overview and Highlights)
• XtraDB updated to the version from …

MariaDB 5.5 and then MySQL 5.6 got Index Condition Pushdown (ICP) optimization (initially coded by yours truly). The idea of ICP is simple: after reading the index record, check the part of WHERE condition that can be computed using index columns, and only then read the table record. That way, we avoid reading table rows that don’t satisfy index condition:

It seems apparent that ICP can never make things slower. The WHERE clause has to be checked anyway, and not reading certain records can only make things faster.

That was what I thought, too, until recently Joffrey Michaie observed the contrary “in the wild”: we’ve got a real-world case where using Index Condition Pushdown was slower than not using it: …

In a previous blog post (Importing related MySQL tables into an Excel Data Model using MySQL for Excel) we covered in detail how an Excel Data Model can be created containing tables and their relationships so the data can be analyzed in Excel via a PivotTable. In this blog post we are going to talk about one of the features included since MySQL for Excel 1.3.0 that allows you to create PivotTables for data imported from MySQL tables, views or stored procedures, or more importantly for the whole Excel Data Model if it is created.

Remember you can install the latest GA or maintenance version using the MySQL Installer or optionally you can download directly any GA or non-GA version from the MySQL Developer …

MySQL has information_schema.tables that contain information such as “data_length” or “avg_row_length.” Documentation on this table however is quite poor, making an assumption that those fields are self explanatory – they are not when it comes to tables that employ compression. And this is where inconsistency is born. Lets take a look at the same table containing some highly compressible data using different storage engines that support MySQL compression:

TokuDB:

mysql> select * from information_schema.tables where table_schema='test' G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: comp
TABLE_TYPE: BASE TABLE
ENGINE: TokuDB
VERSION: 10
ROW_FORMAT: tokudb_zlib
TABLE_ROWS: 40960
AVG_ROW_LENGTH: 10003
DATA_LENGTH: 409722880
MAX_DATA_LENGTH: …

It seems its all about cloud these days. Even the hardware is being marketed with cloud in perspective. Databases like Oracle, SQL Server and MySQL are ahead in the cloud game and this Log Buffer Edition covers that all.

Oracle:

Oracle Database 12c was launched over a year ago delivering the next-generation of the #1 database, designed to meet modern business needs, providing a new multitenant architecture on top of a fast, scalable, reliable, and secure database platform.

Oracle OpenWorld 2014 Session Presentations Now Available.

Today, Oracle is using big data technology and concepts to …

Fri, 2014-10-10 10:08jensbollmann

Installing MariaDB became very easy on the latest CentOS /RHEL Linux version 7. During initial setup MariaDB has a tick in the graphical setup environment which enables MariaDB and replaces MySQL.

During installation the section

allows enabling a MariaDB server.

If you prefer the command line the installation can be performed with:

shell> yum install mariadb-server

MariaDB is shipped untuned and not protected with MariaDB version 5.5.35. No basic optimizations in the configuration file have been set and the root user has no password. An update will install the version 5.5.37. (The latest version of MariaDB October 2014 is Version 5.5.39).

shell> yum update

It is important to secure the server. MariaDB is shipped with a script to harden the system accordingly using a user dialog. It is highly recommended to use this script before …

Fri, 2014-10-10 09:36benstillman

For several years, I have hosted a medium sized Invision Power Board (IPB) site. The site has roughly 9,000+ users and 1,000,000+ posts. I work with MariaDB on a daily basis, and often find myself recommending it to customers. It's only appropriate I put my money where my mouth is, so to speak. This blog post has no benchmarks. It is just a very simple to follow set of steps I took to migrate my site from MySQL 5.1 to MariaDB 10 on CentOS 6.5 with very little downtime for the end users.

To do this, I setup a new virtual server for MariaDB, use mysqldump to export the data with master info, restored this backup to MariaDB, setup replication, set the site to offline, switched the conf_global.php database values, then set the site to online. Sounds easy, right? It was. Very easy. Here are the steps I took with more detail.

For these steps, we will call the original MySQL 5.1 server DB1 and …

October 9, 2014 By Severalnines

Installing, configuring, deploying databases and performing repetitive administrative tasks are all part of a DBA’s or sysadmin’s job. This can get pretty repetitive and overwhelming if you are part of a centralized IT team, running multiple databases for your organization’s different departments, or a managed hosting provider responsible for setting up and operating databases for external clients. One way to get out of this ‘manual, repetitive task’ business is through a Database as a Service (DBaaS).

DBaaS is a way of delivering database functionality as a service to one or more consumers. A DBaaS platform would provide automated procedures for database deployment, monitoring, backups, recovery/repair, scaling, security/multi-tenancy, etc. This type of automation is especially useful where agility is needed, e.g. for systems that require elasticity by scaling out or scaling back at short …