Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 33491 10 Older Entries
Full table scans and MySQL performance
+0 Vote Up -0Vote Down

High season is coming, how do you make sure that MySQL will handle the increased load? Stress tests could help with that, but it’s not a good idea to run them in a production environment. In this case Select_scan, Select_full_join and other MySQL counters could quickly give you an idea of how many queries are not performing well and could cause a performance degradation as the load goes up.

Select_scan from SHOW GLOBAL STATUS indicates how many full table scans were done since last MySQL restart. Scanning the entire table is a resource intensive operation. It also …

  [Read more...]
MySQL benchmarking: Know your baseline variance!
+0 Vote Up -0Vote Down

Often enough I find MySQL benchmark results where the difference between results is 1% or even less and some conclusions are drawn. Now it is not that 1% is not important – especially when you’re developing the product you should care about those 1% improvements or regressions because they tend to add up. However with such a small difference it is very important to understand whenever this is for real or it is just the natural variance for your baseline test.

Take a look at this graph:

This is the result for a simple in-memory, read-only “select by primary key” SysBench benchmark on dedicated physical hardware that …

  [Read more...]
Nasty MySQL Replication Bugs that Affect Upgrade to 5.6
+0 Vote Up -0Vote Down

There were two nasty MySQL replication bugs in two different 5.6 releases that would make it difficult to upgrade slaves to MySQL 5.6 while still connected to MySQL 5.5 master. The first of those bugs is MySQL bug 72610 which affects 5.6.19. Essentially this bug is triggered when the table structure on the slave is different from the table structure on the master which leads to unnecessarily large amount of RAM usage while replicating events that affect that table. The amount of RAM used would generally be more noticeable when the replicated transaction consists of …

  [Read more...]
Write Yourself a Query Rewrite Plugin: Part 1
Employee_Team +0 Vote Up -0Vote Down

With the query rewrite framework in the latest MySQL (Optimizer/InnoDB/Replication) labs release, you get the opportunity to author plugins that can rewrite queries. You can choose whether to rewrite the queries before and/or after parsing. Today I am going to walk you through how to write a pre-parse query rewrite plugin.

When would you want to use a pre-parse query rewrite plugin? The greatest benefit compared to post-parse rewrites — which I will cover in a separate post — is the efficiency, especially the lack of overhead for those queries that are …

  [Read more...]
(More) Secure local passwords in MySQL 5.6 and up
+1 Vote Up -0Vote Down

I log into a lot of different servers running MySQL and one of the first things I do is create a file in my home directory called ‘.my.cnf’ with my credentials to that local mysql instance:

[client]
user=root
password=secret

This means I don’t have to type my password in every time, nor am I tempted to include it on the command line with -p and get the dreaded (but completely accurate):

Warning: Using a password on the command line interface can be insecure.

MySQL 5.6 introduces a utility to make this …

  [Read more...]
Alternatives for chunking bulk deletes in common_schema
+0 Vote Up -0Vote Down

I've blogged about common_schema multiple times in the past, and it's a tool I use frequently. Last week I had a project to delete millions of rows from multiple rollup tables in a star schema. Since the tables are not partitioned I needed to use DELETE instead of DROP PARTITION, but I didn't want to delete millions of rows in a single transaction. My first instinct was to use …

  [Read more...]
Announcing iiBench for MySQL in Java
+0 Vote Up -0Vote Down

I just pushed the new Java based iiBench for MySQL (and Percona Server and MariaDB), the code and documentation are available now in the iibench-mysql Github repo. Pull request are welcome!

The history of iiBench goes back to the early days of Tokutek. Since "indexed insertion" is a strength of Fractal Tree indexes, the first iiBench was created …

  [Read more...]
Log Buffer #398, A Carnival of the Vanities for DBAs
+0 Vote Up -0Vote Down

This Log Buffer Edition covers some informative and interesting posts from Oracle, SQL Server and the MySQL.

Oracle:

If you’re not using Toad DBA Suite, it’s sometimes hard to find solutions. Somebody wanted to know how to find indexes that aren’t that aren’t indirect. Indirect indexes are those created for a primary key because a primary key column or set of columns are both not null and uniquely constrained. You can’t drop a unique index for a primary key without dropping the primary key constraint that …

  [Read more...]
Schema changes in MySQL for OpenStack Trove users
+0 Vote Up -0Vote Down

People using OpenStack Trove instances can hit a common issue in the MySQL world: how to perform schema change operations while minimizing the impact on the database server? Let’s explore the options that can allow online schema changes.

Summary

With MySQL 5.5, pt-online-schema-change from Percona Toolkit is your best option for large tables while regular ALTER TABLE statements are only acceptable for small tables. Also beware of metadata locks.

With MySQL 5.6, almost all types of …

  [Read more...]
Creating JSON documents with MariaDB
+0 Vote Up -0Vote Down

You probably know about two MariaDB great features: Dynamic Columns and Virtual Colums. I don’t think that we already found all possible use cases for them: more ideas will come in the future, and hopefully someone will write about them. Here, I will describe a use case which involves both Virtual Columns and Dynamic Columns.

Now, suppose that we have relational data in MariaDB, and we want to …

  [Read more...]
Showing entries 1 to 10 of 33491 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.