Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 31 to 40 of 100 10 Older Entries

Displaying posts with tag: myisam (reset)

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 …
  [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 …

  [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 …




  [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 …

  [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 & …

  [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 …

  [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


In addition to the benchmark, the …






  [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


In addition to the benchmark, the new …






  [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 …

  [Read more...]
10 Newer Entries Showing entries 31 to 40 of 100 10 Older Entries

Planet MySQL © 1995, 2015, 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.