I love our community.
Not long after posting my update on ps_helper, I had a couple of comments around the formatting of values within the output. Daniël van Eeden gave the suggestion that I could add a couple of Stored Functions, for formatting byte and time based values.
Of course, this was a great idea – not least for myself, because I no longer have to worry about how to format certain columns in the output.
I’ve added the following:
format_bytes()
format_time()
format_path()
…
MySQL Cluster partitioning key
I’ll be presenting a webinar covering MySQL Cluster performance on Thursday, July 26. As always, the webinar will be free but you’ll need to register here – you’ll then also receive a link to the charts and a recording of the session after the event.
The replay of this webinar is now available from here.
Here’s the agenda (hoping that I can squeeze it all in!):
Solving the Challenges of Big Databases with MySQL
When you’re using MySQL for big data (more than ten times as large as main memory), these challenges often arise: loading data fast; maintaining indexes under insertions deletions, and updates; adding and removing columns online; adding indexes online; preventing slave lag; and compressing data effectively.
This session shows why some of these challenges are difficult to solve with storage engines based on B-trees, how Fractal Tree® data structures work, and why they can help solve these problems. Tokutek sells a transaction-safe Fractal Tree storage engine for MySQL, but the presentation is primarily about the underlying technology. It includes a discussion of both the theoretical and practical aspects of Fractal Tree indexes.
I have the privilege of being able to give this talk at both conferences, so please stop by my presentation at …
[Read more]Here are the top reasons to take the authorized MySQL Cluster training course:
In this 3 day course, experienced database users learn the important details of clustering necessary to get started with MySQL Cluster, to properly configure and manage the cluster nodes to ensure high availability, to install the different nodes and provide a better understanding of the internals of the cluster.
To see the schedule for this course, go to the …
[Read more]Even though I have come late to the party of professional development, relatively speaking, I am acutely aware of the conflict that seems to pervade the developer-DBA relationship. This is what I gather about why this is: DBAs used to be paid better that developers, and often this was because they were able to reduce the overall license and hardware costs of large database installations. Both the size and proprietary nature of databases made them incredibly expensive, so paying an individual gobs of money to make sure they ran efficiently and that the data was preserved was worth it.
Several trends have changed the playing field. The first is the arrival of small, commodity server hardware that makes mainframes or large servers unnecessary, and thus the cost is pushed down dramatically for most installations, while salaries for trained database professionals remained the same.
The second is the explosion of databases that do not (in …
[Read more]The other day at PSCE I worked on a customer case of what turned out to be a problem with poor data locality or a data fragmentation problem if you will. I tought that it would make a good article as it was a great demonstration of how badly it can affect MySQL performance. And while the post is mostly around MyISAM tables, the problem is not really specific to any particular storage engine, it can affect a database that runs on InnoDB in a very similar way.
The problem
MyISAM lacks support for clustering keys or even anything remotely similar. Its data file format allows new information to be written anywhere inside a table. Anywhere can be either at the end of a file where it can be simply appended or an empty space somewhere in the middle left after previously deleted row(s). This implies no particular order in which rows are stored unless there are absolutely no …
[Read more]A customer opened an issue recently to ask why the query cache wasn't working after he upgraded to MySQL 5.5.25. The reason really ended up surprising me.
As of MySQL 5.5.23, the Query Cache is disabled for partitioned tables!
This is a "fix" for bug #53775.
At first I thought perhaps the fix for the bug had resulted in the query cache being inadvertently disabled for partitioned tables, but the comments that go along with the commit make it pretty clear that disabling the query cache was the intended "fix". You can review the commit message and the code changed at revision 2661.803.1 in the MySQL Server 5.5 repository.
…
[Read more]This MySql function will generate a link to your glossary when you are fetching text for a web page. All code is provided in a zip file.
In an earlier blog post, I showed how optimizer improvements
in MySQL 5.6 gave better performance for several of the queries
in the DBT-3 benchmark.
However, for one of the queries, Query 18, I was not able to give
exact numbers for the improvement since the query took very long
in MySQL 5.5. I decided to try to find out exactly how long the
query would take, but when the query had run for one month, I
gave up. How can a query take so long? Especially, when I had set
up InnoDB with a buffer pool that should be large enough to hold
the entire database. Let's have a look at the query:
select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)[Read more]
from customer, orders, lineitem
where o_orderkey in (
select l_orderkey
from lineitem
…
A short MySql function to generate a Lorum Ipsum text. You can download the code in the zip file below.
