Showing entries 31 to 40 of 106
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: myisam (reset)
Getting rid of huge ibdata file, no dump required

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 much longer. The real pain is that you can't do …

[Read more]
Performance improvements for big INFORMATION_SCHEMA tables

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 temporary MyISAM tables to also enable the use of dynamic record format for …

[Read more]
Dedicated table for counters

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
) ENGINE=MyISAM

mysql> SELECT * FROM hits;
+---------+
| cnt     |
+---------+
| 3823273 |
+---------+

[Read more]
Copying unused bytes is bad (duh!)

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 are not used anywhere, writing 0s to the buffer certainly has a cost. Hence …

[Read more]
Auto caching tables

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 you read it through to understand the …

[Read more]
FictionPress Selects TokuDB for Consistent Performance and Fast Disaster Recovery

FictionPress

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 participating from around the world in over 30 languages

The Challenge: FictionPress offers a number of interactive features to its large user base. These …

[Read more]
Self throttling MySQL queries

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 scan. No aggregates (which complicate …

[Read more]
Basic Apache and MySQL Performance Tuning: Part 2: MySQL

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 visit the project page at GitHub here: …

[Read more]
MyISAM Key Buffer Usage

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 key buffer is empty or (has been) completely full.

If …

[Read more]
Silent MyISAM Table Definition Changes and mysqldump

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. Furthermore the source and destination server have the exact same operating system …

[Read more]
Showing entries 31 to 40 of 106
« 10 Newer Entries | 10 Older Entries »