Showing entries 11 to 20 of 47
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: index (reset)
Understanding Bulk Index Creation in InnoDB (and innodb_sort_buffer_size)

In a previous post, I presented an Unexpected Memory Consumption for Bulk Index Creation in InnoDB.  This was triggered by an increased innodb_sort_buffer_size and as stated in another post: "the sorting algorithm does not scale well with large sort buffers".  In this post, I will present why it does not scale well and I will suggest solutions.

This post also answers feedback request for the

Unexpected Memory Consumption for Bulk Index Creation in InnoDB (MySQL)

In my last Booking.com Hackathon, I worked on MyISAM vs InnoDB for data loading (LOAD DATA IN FILE) and bulk index creation.  My motivation was the following: knowing that some are still using MyISAM for this particular use-case, I wanted to verify/understand if/why InnoDB is slower than MyISAM.  I do not yet have complete results on this specific subject but I found some interesting things that

Percona Live Presentation: Improving Performance With Better Indexes

The slides for my Improving Performance With Better Indexes presentation at Percona Live 2015 MySQL Conference and Expo are now available.

In this presentation I discuss how to identify, review and analyze SQL statements in order to create better indexes for your queries. This includes understanding the EXPLAIN syntax and how to create and identify covering and partial column indexes.


Download PDF Presentation

This presentation is based on the work with a customer showing the 95% improvement of a key 15 table join query running 15,000 QPS in a 25 billion SQL statements per day …

[Read more]
JSON Labs Release: Effective Functional Indexes in InnoDB

In MySQL 5.7.6, we added a new feature called Generated Columns. In the initial work all Generated Columns were materialized, even virtual ones. This not only resulted in unnecessary disk space being used and disk I/O being done, but it also meant that any table alteration required that the full table be rebuilt. In the new MySQL 5.7.7 JSON Lab release, we have resolved all of these issues by implementing new features that not only allow users to create non-materialized virtual …

[Read more]
EXPLAIN minor wishlist

While we always want better performance and more and larger features for MySQL, those cannot just “magically appear” from one version to another, requiring deep architecture changes and lots of lines of code. However, there are sometimes smaller features and fixes that could be implemented by an intern or an external contributor, mainly at SQL layer, and that could make the MySQL ecosystem friendlier to newbies and non-experts. Making a piece of software easier to use is sometimes overlooked, but it is incredibly important -not everybody using MySQL is a DBA, and the more people adopting it, more people will be able to live from it, both upstream and as third party providers.

Here it is my own personal list of fixes for EXPLAIN messages. If you are an experienced MySQL user you are probably aware of their meaning, but that doesn’t solve the problem for beginners. The reason why I am writing a blog post is to gather …

[Read more]
Advanced JSON for MySQL

What is JSON

JSON is an text based, human readable format for transmitting data between systems, for serializing objects and for storing document store data for documents that have different attributes/schema for each document. Popular document store databases use JSON (and the related BSON) for storing and transmitting data.

Problems with JSON in MySQL

It is difficult to inter-operate between MySQL and MongoDB (or other document databases) because JSON has traditionally been very difficult to work with. Up until recently, JSON is just a TEXT document. I said up until recently, so what has changed? The biggest thing is that there are new JSON UDF by Sveta Smirnova, which are part of the MySQL 5.7 Labs releases. Currently the JSON UDF are up to version 0.0.4. While these new UDF are a welcome edition to the MySQL database, they don’t solve the really tough …

[Read more]
‘Indexing’ JSON documents for efficient MySQL queries over JSON data

MySQL meets NoSQL with JSON UDF

I recently got back from FOSDEM, in Brussels, Belgium. While I was there I got to see a great talk by Sveta Smirnova, about her MySQL 5.7 Labs release JSON UDF functions. It is important to note that while the UDF come in a 5.7 release it is absolutely possible to compile and use the UDF with earlier versions of MySQL because the UDF interface has not changed for a long time. However, the UDF should still be considered alpha/preview level of quality and should not be used in production yet! For this example I am using Percona Server 5.6 with the UDF.

That being said, the proof-of-concept that I’m about to present here uses only one JSON function (JSON_EXTRACT) and it has worked well enough in my testing to present my idea here. The JSON functions will probably be GA sometime soon anyway, and this is a useful test of the JSON_EXTRACT function. …

[Read more]
Indexing Talk Online

and the video can be directly played in your browser at http://technocation.org/node/697/play or downloaded (170Mb) at http://technocation.org/node/697/download.

and the video can be directly played in your browser at http://technocation.org/node/697/play or downloaded (170Mb) at http://technocation.org/node/697/download.

I do not recall seeing an announcement about it, pancreatitis but I went looking for the videos today and lo and behold, they were up! Forgive me if I missed a post about it….but if you also missed it, here they are:

<A …

[Read more]
The small improvements of MySQL 5.6: Duplicate Index Detection

Here at the MySQL Performance Blog, we’ve been discussing the several new features that MySQL 5.6 brought: GTID-based replication, InnoDB Fulltext, Memcached integration, a more complete performance schema, online DDL and …

[Read more]
Speedup mysql index using ssd disk

You probably ask yourself sometimes how you can boost MySQL indexes when you are working with BIG databases/tables.

Really the solution is here for a long time already. You can use SSD disks (also known as flash disks). These disks are not that big that traditional SATA hard drives but they provide a superior performance. If your database is rather big to be placed on SSD disk you can still

Showing entries 11 to 20 of 47
« 10 Newer Entries | 10 Older Entries »