Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 89 Next 30 Older Entries

Displaying posts with tag: myisam (reset)

The MySQL ARCHIVE storage engine – Alternatives
+0 Vote Up -0Vote Down

In my previous post I pointed out that the existing ARCHIVE storage engine in MySQL may not be the one that will satisfy your needs when it comes to effectively storing large and/or old data. But are there any good alternatives? As the primary purpose of this engine is to store rarely accessed data in disk space efficient way, I will focus here on data compression abilities rather then on performance.

The InnoDB engine provides compressed row format, but is it’s efficiency even close to the one from that available in archive engine? You can also compress MyISAM tables by using myisampack tool, but that also means a table will be read only after such operation.

Moreover, I don’t trust MyISAM nor Archive when it comes to data

  [Read more...]
Comparing MongoDB, MySQL, and TokuMX Data Layout
+2 Vote Up -0Vote Down

A lot is said about the differences in the data between MySQL (http://www.mysql.com/) and MongoDB. Things such as “MongoDB is document based”, “MySQL is relational”, “InnoDB has a clustering key”, etc.. Some may wonder how TokuDB, our MySQL storage engine, and TokuMX, our MongoDB product, fit in with these data layouts. I could not find anything describing the differences with a simple google search, so I figured I’d write a post explaining how things compare.

So who are the players here? With MySQL, users are likely familiar with two storage engines: MyISAM, the original default up until MySQL 5.5, and

  [Read more...]
MySQL and the SSB – Part 2 – MyISAM vs InnoDB low concurrency
+1 Vote Up -0Vote Down

This blog post is part two in what is now a continuing series on the Star Schema Benchmark.

In my previous blog post I compared MySQL 5.5.30 to MySQL 5.6.10, both with default settings using only the InnoDB storage engine.  In my testing I discovered that innodb_old_blocks_time had an effect on performance of the benchmark.  There was some discussion in the comments and I promised to follow up with more SSB tests at a later date.

I also promised more low concurrency SSB tests when Peter blogged about the importance of performance at low concurrency.

The SSB tests a

  [Read more...]
MariaDB/MySQL: Performances of COUNT()
+0 Vote Up -0Vote Down

Versione italiana

How fast is COUNT() execution? Well, it depends from the Storage Engine.

Try to create an Aria or MyISAM table, INSERT some data, and execute an EXPLAIN similar to the following:

MariaDB [(none)]> EXPLAIN SELECT COUNT(*) FROM test.t1;
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |
  [Read more...]
MySQL Triggers with Logging
+0 Vote Up -0Vote Down

Somebody asked why you can’t implement MySQL triggers that write information when you want to stop the DML statement, like autonomous procedures in Oracle. The question was a surprise but I didn’t find anything on it, so here’s how you can do it. This is more or less like an autonomous process by leveraging both the InnoDB and MyISAM engine’s behaviors. This post leverages an earlier explanation of MySQL Triggers.

  • First you create a MyISAM table, which is a persistent store that auto commits when you’re other InnoDB tables can be transactionally dependent. Here’s a simple MyISAM logger table.
  • CREATE TABLE logger
    , logger_event      VARCHAR(50)
      [Read more...]
    The MERGE storage engine: not dead, just resting…. or forgotten.
    +0 Vote Up -0Vote Down

    Following on from my fun post on Where are they now: MySQL Storage Engines, I thought I’d cover the few storage engines that are really just interfaces to a collection of things. In this post, I’m talking about MERGE.

    The MERGE engine was basically a multiplexer down to a number of MyISAM tables. They all had to be the same, there was no parallel query execution and it saw fairly limited use. One of the main benefits was that then you could actually put more rows in a MyISAM table than your “files up to 2/4GB” file system allowed. With the advent of partitioning, this really should have instantly gone away

      [Read more...]
    MyISAM in a MySQL 5.6 InnoDB Only instance
    +1 Vote Up -0Vote Down
    With MySQL 5.5 the default storage engine was changed to InnoDB. This was a good step as most users expected MySQL to support transactions, row level locking and all the other InnoDB features, but with 5.1 they sometimes forgot to choose the right storage engine. As most databases don't have multiple storage engines and many MySQL users changed the default storage engine to InnoDB this made the switch to MySQL easier and the default behaviour more in line with what people expect from a relational database.

    Changing the storage engine can of course be done on a per table or per instance basis (default-storage-engine in my.cnf). The temporary tables created with 'CREATE TEMPORARY TABLE ...' should not be forgotten. The performance of InnoDB or MyISAM for temporary tables can have quite some impact, especially with slow storage, a buffer pool which is too

      [Read more...]
    InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!
    +4 Vote Up -1Vote Down

    InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

    This is part 2 in a 3 part series. In part 1, we took a quick look at some initial configuration of InnoDB full-text search and discovered a little bit of quirky behavior; here, we are going to run some queries and compare the result sets. Our hope is that the one of two things will happen; either the results returned from a MyISAM FTS query will be exactly identical to the same query when performed against InnoDB data, OR that the results returned by InnoDB FTS will somehow be

      [Read more...]
    MyISAM or InnoDB?
    +0 Vote Up -0Vote Down
    ***This post is out of date and old. I have just migrated to blogger and this post is displaying as recent. Apologies***

    Whenever I talk about MySQL performance my first recommendation is normally something along the lines of “Use the InnoDB storage engine” and I always get asked the same two questions 1) Why use InnoDB over MyISAM? and 2) Isn’t MyISAM faster? The short answers to these questions are:
    1) There’s rarely any reason not to.
    2) No……….(pause) well sometimes… in most cases no.
    In this post I will aim to explain my choice of InnoDB and try and loosely define the cases where MyISAM may be better suited for your application. I am also very aware that there are many MySQL storage engines, but I am just going to cover the big two here.
    First I am going to start with the reasons that InnoDB is better suited than MyISAM:

      [Read more...]
    MyISAM and InnoDB compared
    +1 Vote Up -2Vote Down

    Yet another article comparing two database features. This time the different between the two storage engines MyISAM and InnoDB from MySQL will be compared, so you can choose which one you should choose for your project. Some fast facts: InnoDB supports foreign keys InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock! InnoDB supports transactions MyISAM [...]
    Too many or too big open MyISAM tables??
    +2 Vote Up -1Vote Down
    MySQL tries to be smart and to cram as much performance out of available hardware, and one thing that most MySQLers knows is that opening a table (ie. opening the frm file, and depending on the storage engine, any additional files related to the table) is expensive from a performance point of view. When we see the opened_tables status variable increasing, we tend to increase the table_open_cache setting and keep as many tables as possible open and avoid reopening them.

    When it comes to MyISAM though, this has a nasty side-effect. When the server crashes and there are MyISAM tables open, these might well need to be REPAIRed. And if you have big MyISAM tables, this is an issue. Let's say that your application use several MyISAM tables, with the same content, and that you create new tables after a certain time, to keep the size of each

      [Read more...]
    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...]
    Getting rid of huge ibdata file, no dump required, part II
    +1 Vote Up -1Vote Down

    This post continues Getting rid of huge ibdata file, no dump required, part I, where I describe way of converting your single-tablespace InnoDB database into a file-per-table one, without the pain of exporting and importing everything at once.

    In previous part we put aside the issue of foreign keys. We address this issue now.

    What if my InnoDB tables have foreign keys?

    MyISAM does not support them, so you can't just ALTER an InnoDB table to MyISAM and back into InnoDB, and expect everything to work.

    Alas, this calls for additional steps (i.e. additional ALTER commands). However, these still fall well under the concept of "do it one table at a time, then take time to recover your breath

      [Read more...]
    Getting rid of huge ibdata file, no dump required
    +4 Vote Up -1Vote Down

    You have been told (guilty as charged), that the only way to get rid of the huge InnoDB tablespace file (commonly named ibdata1), when moving to innodb_file_per_table, is to do a logical dump of your data, completely erase everything, then import the dump.

    To quickly reiterate, you can only delete the ibdata1 file when no InnoDB tables exist. Delete this file with an existing InnoDB table, even a table in its own tablespace, and nothing ever works anymore.

    The problem with the dump-based solution

    The impact of doing a logical dump is often overwhelming. Well, the dump may be tolerable, but the restore is

      [Read more...]
    Performance improvements for big INFORMATION_SCHEMA tables
    Employee +2 Vote Up -0Vote Down
    A short while after I fixed the legacy bug that prevented temporary MyISAM tables from using the dynamic record format, I got an email from Davi Arnaut @ Twitter. It turned out that Twitter needed to fix the very same problem, but for the case when INFORMATION_SCHEMA temporary tables use MyISAM.

    In short, INFORMATION_SCHEMA tables provide access to database metadata. Despite their name, they are more like views than tables: when you query them, relevant data is gathered from the dictionary and other server internals, not from tables. The gathered data is stored in a temporary table (memory or MyISAM depending on size) and then returned to the user.

    The reason Davi emailed me was to let me know that he had further improved the fix for

      [Read more...]
    Dedicated table for counters
    +1 Vote Up -0Vote Down

    There are a few ways to implement counters. Even though it’s not a complex feature, often I see people having problems around it. This post describes how bad implementation can impact both application and MySQL performance and how to improve it.

    A customer asked me for help with performance problem they were facing. I logged into their database and found many client connections waiting for table locks. Almost all threads were stuck on one, small table called hits. What was the reason?

    The problem was related to the way they developed a very simple system for counting page views they later used in some reporting. The table structure was:

    mysql> SHOW CREATE TABLE hits\G
    *************************** 1. row ***************************
    Table: hits
    Create Table: CREATE TABLE `hits` (
    `cnt` int(11) NOT NULL
      [Read more...]
    Copying unused bytes is bad (duh!)
    Employee +3 Vote Up -0Vote Down
    Last summer my colleague Marko Mäkelä committed this seemingly innocent performance fix for InnoDB in MySQL 5.6:

    3581 Marko Makela    2011-08-10
    Bug#12835650 VARCHAR maximum length performance impact

    row_sel_field_store_in_mysql_format(): Do not pad the unused part of
    the buffer reserved for a True VARCHAR column (introduced in 5.0.3).
    Add Valgrind instrumentation ensuring that the unused part will be
    flagged uninitialized.

    Before this, buffers which were used to send VARCHARs from InnoDB to the MySQL server were padded with 0s if the string was shorter than specified by the column. If, e.g., the string "foo" was stored in a VARCHAR(8), InnoDB used to write "3foo00000" to the buffer (the first character - 3 - determines the actual length of the string). However, even though these trailing bytes

      [Read more...]
    Auto caching tables
    +2 Vote Up -0Vote Down

    Is there a way to create a caching table, some sort of a materialized view, such that upon selecting from that table, its data is validated/invalidated?

    Hint: yes.

    But to elaborate the point: say I have some table data_table. Can I rewrite all my queries which access data_table to read from some autocache_data_table, but have nothing changed in the query itself? No caveats, no additional WHEREs, and still have that autocache_data_table provide with the correct data, dynamically updated by some rule of our choice?

    And: no crontab, no event scheduler, and no funny triggers on data_table? In such way that invalidation/revalidation occurs upon SELECT?

    Well, yes.

    This post is long, but I suggest

      [Read more...]
    FictionPress Selects TokuDB for Consistent Performance and Fast Disaster Recovery
    +1 Vote Up -0Vote Down


    Issues addressed:

    • Support complex and efficient indexes at 100+ million rows.
    • Predicable and consistent performance regardless of data size growth.
    • Fast recovery.

    Ensuring Predictable Performance at Scale

    The Company:  FictionPress operates both FictionPress.com and FanFiction.net and is home to over 6 million works of fiction, with millions of writers/readers

      [Read more...]
    Self throttling MySQL queries
    +3 Vote Up -0Vote Down

    Recap on the problem:

    • A query takes a long time to complete.
    • During this time it makes for a lot of I/O.
    • Query's I/O overloads the db, making for other queries run slow.

    I introduce the notion of self-throttling queries: queries that go to sleep, by themselves, throughout the runtime. The sleep period means the query does not perform I/O at that time, which then means other queries can have their chance to execute.

    I present two approaches:

    • The naive approach: for every 1,000 rows, the query sleep for 1 second
    • The factor approach: for every 1,000 rows, the query sleeps for the amount of time it took to iterate those 1,000 rows (effectively doubling the total runtime of the query).

    Sample query

    We use a simple, single-table

      [Read more...]
    Basic Apache and MySQL Performance Tuning: Part 2: MySQL
    +0 Vote Up -0Vote Down

    MySQL Tuning

    This is another section that is broader than one would first imagine.  There’s a reason that many large organizations employ dedicated database administrators.  That said, this doesn’t prevent the average sysadmin from making some changes to enhance performance on their database.

    The easiest way to start on this is to utilize a script to automatically check your configuration options and make suggestions based on status variables MySQL sets.  I’ve had good luck with a script called mysqltuner.pl.  You can

      [Read more...]
    MyISAM Key Buffer Usage
    +0 Vote Up -0Vote Down
    For MyISAM one of the most important variables is the Key Buffer.  The Key Buffer is sometimes called the Key Cache. It's used as a buffer for the indices of MyISAM tables. There is some overhead in the buffer depending on the configured key block size.

    The official way to calculate the key buffer usage as documented in the MySQL Reference manual:
    1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)
     This will return the factor, so you have to multiply it with 100 to get the percentage. The Key_blocks_unused is used instead of the more obvious Key_blocks_used. This is due to the fact that Key_blocks_used is the maximum number of key blocks ever used. It will not return to 0 after a FLUSH TABLES.

    This calculation does not take the overhead in account. The key buffer efficiency can be calculated if the

      [Read more...]
    Silent MyISAM Table Definition Changes and mysqldump
    +2 Vote Up -0Vote Down

    The other day while trying to move a schema from one MySQL server to another, I encountered a very odd issue. The schema to be moved contained both MyISAM and InnoDB tables, so the only option I had was to dump the schema using mysqldump on the source server and import it on the destination server. The dump on the source server went fine with absolutely no issues but it failed to import into the second server, and the error message was:

    Can't create/write to file ‘/disk1/activity.MYI’ (Errcode: 2)

    This was an extremely odd message as the data directory on the destination server was properly setup in terms of ownership and permission. The source and destination MySQL servers have been running without issues for months. Prior to the error, four tables in the dump file were imported into the destination server without any issues whatsoever.

      [Read more...]
    On Hot Backups and Restore using XtraBackup
    +3 Vote Up -0Vote Down
    Backups are an integral and very important part of any system. Backups allow you to recover your data and be up and running again, in the advent of problems such as system crashes, hardware failures or users deleting data by mistake. I had been evaluating backup solution for a while but to be honest I really wasn't satisfied with the solutions available until I came across XtraBackup and I am loving it since. In this post I intend on showing how to do backups and restores using XtraBackup.
    Limiting table disk quota in MySQL
    +1 Vote Up -0Vote Down

    Question asked by a student: is there a way to limit a table’s quote on disk? Say, limit a table to 2GB, after which it will refuse to grow? Note that the requirement is that rows are never DELETEd. The table must simply refuse to be updated once it reaches a certain size.

    There is no built-in way to limit a table’s quota on disk. First thing to observe is that MySQL has nothing to do with this. It is entirely up to the storage engine to provide with such functionality. The storage engine is the one to handle data storage: how table and keys are stored on disk. Just consider the difference between MyISAM’s .MYD & .MYI to InnoDB’s shared tablespace ibdata1 to InnoDB’s file-per table .ibd files.

    The only engine I know of that has a quota is the MEMORY engine: it

      [Read more...]
    What is this MySQL file used for?
    +7 Vote Up -0Vote Down

    MySQL keeps many different files, some contain real data, some contain meta data. Witch ones are important? Witch can your throw away?

    This is my attempt to create a quick reference of all the files used by MySQL, whats in them, what can you do if they are missing, what can you do with them.

    When I was working for Dell doing Linux support my first words to a customer where “DO YOU HAVE COMPLETE AND VERIFIED BACKUP?” Make one now before you think about doing anything I suggest here.

    You should always try to manage your data through a MySQL client.  If things have gone very bad this may not be possible. MySQL may not start. If your file system get corrupt you may have missing files. Sometimes people create other files in the MySQL directory (BAD).  This should help you understand what is safe to remove.

    Before you try to work with one of

      [Read more...]
    Comparing InnoDB to MyISAM Performance
    Employee_Team +6 Vote Up -0Vote Down
    The MySQL performance team in Oracle has recently completed a series of benchmarks comparing Read / Write and Read-Only performance of MySQL 5.5 with the InnoDB and MyISAM storage engines.

    Compared to MyISAM, InnoDB delivered 35x higher throughput on the Read / Write test and 5x higher throughput on the Read-Only test, with 90% scalability across 36 CPU cores.

    A full analysis of results and MySQL configuration parameters are documented in a new whitepaper (http://www.mysql.com/why-mysql/white-papers/mysql_5.5_perf_myisam_innodb.php)

    In addition to the benchmark, the new whitepaper (http://www.mysql.com/why-mysql/white-papers/mysql_5.5_perf_myisam_innodb.php), also includes:
    - A discussion of the use-cases for each storage engine
    - Best practices for users

      [Read more...]
    Comparing InnoDB to MyISAM Performance
    Employee_Team +0 Vote Up -0Vote Down
    The MySQL performance team in Oracle has recently completed a series of benchmarks comparing Read / Write and Read-Only performance of MySQL 5.5 with the InnoDB and MyISAM storage engines.

    Compared to MyISAM, InnoDB delivered 35x higher throughput on the Read / Write test and 5x higher throughput on the Read-Only test, with 90% scalability across 36 CPU cores.

    A full analysis of results and MySQL configuration parameters are documented in a new whitepaper (http://www.mysql.com/why-mysql/white-papers/mysql_5.5_perf_myisam_innodb.php)

    In addition to the benchmark, the new whitepaper (http://www.mysql.com/why-mysql/white-papers/mysql_5.5_perf_myisam_innodb.php), also includes:
    - A discussion of the use-cases for each storage engine
    - Best practices for users

      [Read more...]
    Moving from MyISAM to Innodb or XtraDB. Basics
    +4 Vote Up -0Vote Down

    I do not know if it is because we're hosting a free webinar on migrating MyISAM to Innodb or some other reason but recently I see a lot of questions about migration from MyISAM to Innodb.

    Webinar will cover the process in a lot more details though I would like to go over basics in advance. You can also check my old post on this topic about Moving MyISAM to Innodb as well as searching the blog - We've blogged so much on this topic.

    So what are the basics ?

    Regression Benchmarks - Make sure to run regression benchmarks in particular in terms of

      [Read more...]
    Why mysqldump is converting my tables from InnoDB to MyISAM?
    +5 Vote Up -0Vote Down

    First of all: mysqldump is not converting tables. It is something else. Here is the story:

    One of my clients had a case when they were migrating to a new mysql server: they used mysqldump to export data from the old server (all InnoDB) and imported it to the new server. When finished, all the tables became MyISAM on the new server. So they asked me this question:
    “Why mysqldump is converting my tables from InnoDB to MyISAM?”

    First of all we made sure that the tables are InnoDB on the old server. It was true.
    Second we run “show engines” on the new server:

    | Engine | Support | Comment

      [Read more...]
    Showing entries 1 to 30 of 89 Next 30 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.