Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 9

Displaying posts with tag: fragmentation (reset)

Data fragmentation problem in MySQL & MyISAM
+2 Vote Up -0Vote Down

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

  [Read more...]
Improving InnoDB memory usage
+4 Vote Up -0Vote Down

Last month we did a few improvements in InnoDB memory usage. We solved a challenging issue about how InnoDB uses memory in certain places of the code.

The symptom of the issue was that under a certain workloads the memory used by InnoDB kept growing infinitely, until OOM killer kicked in. It looked like a memory leak, but Valgrind wasn’t reporting any leaks and the issue was not reproducible on FreeBSD – it only happened on Linux (see Bug#57480). Especially the latest fact lead us to think that there is something in the InnoDB memory usage pattern that reveals a nasty side of the otherwise good-natured Linux’s memory manager.

It turned out to be an interesting

  [Read more...]
Indexing: The Director’s Cut
+0 Vote Up -0Vote Down

Thanks again to Erin O’Neill and Mike Tougeron for having me at the SF MySQL Meetup last month for the talk on “Understanding Indexing.” The crowd was very interactive, and I appreciated that over 100 people signed up for the event and left some very positive comments and reviews.

Thanks to Mike, a video of the talk is now available:

As a brief overview – Application performance often depends on how fast a query can respond and query performance almost always depends on good indexing. So one of the quickest and least expensive ways to increase application performance is to optimize the indexes. This talk presents three simple and effective rules on how to construct

  [Read more...]
Shinguz: Regularly flushing the MySQL Query Cache
+0 Vote Up -0Vote Down

When we analyze our customers systems we see typically a high fragmentation of the query cache after a while. This leads to a less optimal use of the Query Cache than possible.

With the following Query you can see the values for your Query Cache:

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 11328    |
| Qcache_free_memory      | 89442000 |
| Qcache_hits             | 6595644  |
| Qcache_inserts          | 1041831  |
| Qcache_lowmem_prunes    | 717896   |
| Qcache_not_cached       | 1040936  |
| Qcache_queries_in_cache | 17775    |
| Qcache_total_blocks     | 46990    |
+-------------------------+----------+

Watch out for the value of Qcache_free_blocks and

  [Read more...]
MySQL Partitioning: A Flow Chart
+2 Vote Up -4Vote Down

In Part 1, and Part 2 of this series, I presented some thoughts on partitioning. I heard some great feedback on why people use partitioning. Here, I present a flow chart that summarizes what I’ve learned. In summary: with TokuDB in the picture there’s almost no reason to use partitioning. Or I should say, there are almost always better (higher performing, more robust, lower maintenance) alternatives to partitioning.

Here goes:

  • Spindle contention? In other words, are you partitioning in order to spread your query work load across many disks? I’ve yet to see a compelling
  •   [Read more...]
    Log Buffer #195, A Carnival of The Vanities for DBAs
    +0 Vote Up -0Vote Down

    A short post marks Pythian’s 195th edition of Log Buffer, a blog of blogs encapsulating what’s going on in the world of database administration.

    Remember if you find a link or interesting blog post that you think Log Buffer should mention, send a note to the editor at Log Buffer and be sure to include the link, and a short note on why you think that others will want to read it too.

    Now on to Log Buffer #195. Alex Gorbachev starts us off with his suggested readings and funnily enough,

      [Read more...]
    Attempting to Quantify Fragmentation Effects
    +2 Vote Up -0Vote Down

    We often hear from customers and MySQL experts that fragmentation causes problems such as wasting disk space, increasing backup times, and degrading performance. Typical remedies include periodic "optimize table" or dump and re-load (for example, see Project Golden Gate). Unfortunately, these techniques impact database availability and/or require additional administrative cost and complexity. Tokutek's Fractal Tree algorithms do not not cause fragmentation, and we're looking for ways to measure the effects of fragmentation to quantify TokuDB's benefits.

    I ran some tests using the iiBench benchmark as an experiment to try and quantify the impact of fragmentation,

      [Read more...]
    InnoDB Table and Tablespace Monitors
    Employee +0 Vote Up -0Vote Down

    InnoDB allows you to dump information on its internal data dictionary out to stderr, by creating specific InnoDB monitor tables. Specifically the innodb_table_monitor and innodb_tablespace_monitor tables. This is covered to a limited extent in the MySQL Reference Manual at http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html, however not to any great detail.

    In the following I’ll use output created via these tables based upon the sample “sakila” database, and give details on how to attempt to correlate the information between the different outputs.

    The output of SHOW INNODB STATUS is the output of the InnoDB Monitor, which is different than both the InnoDB Tablespace Monitor and the Innodb Table Monitor. You can also cause this information to be output to

      [Read more...]
    Is Drizzle good for MySQL?
    +0 Vote Up -0Vote Down

    Have you heard of Drizzle? It was announced at OSCON yesterday and is all over the blogosphere. From the Drizzle FAQ:

    * So what are the differences between is and MySQL?

    No modes, views, triggers, prepared statements, stored procedures, query cache, data conversion inserts, ACL. Fewer data types. Less engines, less code. Assume the primary engine is transactional.

    Also from the FAQ is that, right now at least, there is no intention to make this run natively on windows and they make the point:

    * "This is not a SQL compliant relational..."

    Very true, and we do not aim to be that.

    It is a fork of MySQL that takes it backward to pre-5.0 in features

      [Read more...]
    Showing entries 1 to 9

    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.