Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 91 to 100 of 33367 10 Older Entries
MariaDB 10.1: Better query optimization for ORDER BY … LIMIT
+1 Vote Up -0Vote Down

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
  [Read more...]
MariaDB 5.5.40 Overview and Highlights
+1 Vote Up -0Vote Down

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

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:

  [Read more...]
A discovery - Index Condition Pushdown can cause a slowdown after all
+2 Vote Up -0Vote Down

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

  [Read more...]
Creating PivotTables when importing MySQL data using MySQL for Excel
Employee +0 Vote Up -0Vote Down

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 Zone.

Low-concurrency performance for updates with InnoDB: MySQL 5.7 vs previous releases
+1 Vote Up -0Vote Down
First I tested updates with the Heap engine as a base case and now I report on InnoDB to find regressions that are specific to it. The workload is described in the previous blog post. Response time is measured at 1, 4 and 32 threads for two types of updates. The unindexed update increments an unindexed column. The indexed update updates a column that is in a secondary index. Tests are run with the binlog enabled and disabled. Tests are done for MySQL versions 5.0.85, 5.1.63, 5.5.40, 5.6.21 and 5.7.5. In some cases tests are also done with the adaptive hash index disabled. The test database has 64,000 rows but all updates are to one row (lots of contention). The client is mysqlslap running on the same host as mysqld. The test server has 40 hyperthread  [Read more...]
MySQL compression: Compressed and Uncompressed data size
+1 Vote Up -0Vote Down

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:


mysql> select * from information_schema.tables where table_schema='test' G
*************************** 1. row ***************************
  [Read more...]
Log Buffer #392, A Carnival of the Vanities for DBAs
+0 Vote Up -0Vote Down

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 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.


  [Read more...]
Installing MariaDB 10 on CentOS 7 / RHEL 7
+0 Vote Up -0Vote Down
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

  [Read more...]
How I Converted My Invision Power Board Site from MySQL 5.1 to MariaDB 10 With Minimal Downtime
+0 Vote Up -0Vote Down
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

  [Read more...]
Low-concurrency performance for updates and the Heap engine: MySQL 5.7 vs previous releases
+0 Vote Up -0Vote Down
The low-concurrency performance regression evaluation continues after evaluating range queries. This post covers updates with the Heap engine. The Heap engine is used as the base case and the next post has results for InnoDB. The database has 64,000 rows but all updates were for the same row. Tests were done for updates to an indexed and then an unindexed column at 1, 4 and 32 threads. Tests were also repeated with the binlog disabled and then enabled. There are regressions at 1, 4 and 32 threads. The summary is:
  • Response time at 1 thread for 5.7.5 is between 1.55X and 1.67X worse than 5.0.85 
  • Response time at 32 threads for 5.7.5 is between 1.19X and 1.49X worse than 5.0.85 
  • In all cases it is worse in 5.7 than in

  [Read more...]
10 Newer Entries Showing entries 91 to 100 of 33367 10 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.