PlanetMysql.ru — информация о СУБД MySQL

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 include discussion forums, in-site messaging and user reviews. FictionPress made the decision to build its own discussion forums to meet its strict security and performance requirements. Xing Li, CTO of FictionPress, noted that the site “needs to host hundreds of thousands of forums. Existing forum software doesn’t do this while meeting our performance and security targets.”

To ensure the real-time responsiveness of the forums, FictionPress needs the ability to create and efficiently maintain complex indexes and be able to support millions of small rows. In addition, it needs the ability to index them with minimal impact to resource costs and performance. “The only way to make this all work and provide a good customer experience is to guarantee that we can deliver a flat predictable performance with our database back-end even as the number of rows crosses the 100 million mark,” according to Li.

FictionPress considered InnoDB, the default storage engine for MySQL, but it did not offer predictable performance at scale. Indexes became dramatically slower as the number of rows increased, causing a reduction of both read and write performance. InnoDB also did not offer the performance-enhancing feature of multiple clustering indexes.

The Solution:  FictionPress uses MariaDB and TokuDB to manage its discussion forums, reviews, and in-site messaging systems.

FictionPress installed TokuDB in a Linux environment with dedicated hardware. Each configuration has a single master with multiple read slaves. “TokuDB’s high write concurrency and support for multiple clustering indexes gave us the freedom to design and deploy better performing queries at scale,” according to Li. This was important to FictionPress as its environment is continually expanding.

The Benefits:

Predictable Performance: “While raw performance is important, the predictability of response time as one scales the system was our focal point” according to Li. “InnoDB can only have one clustering index, but TokuDB gives you basically an unlimited number. In addition, both MyISAM and InnoDB slow down with many indexes on databases of our size. MyISAM also causes replication lag due to concurrency. In the end, TokuDB gives us predictability, performance at scale, and more flexible indexing without the limitations found in other MySQL options.”

Cost: “To get additional performance, one can always throw hardware at the problem,” according to Li. “By utilizing TokuDB instead we improved scalability and at the same time saved on costs for additional server hardware that would have been required if TokuDB was not in the picture. In addition, we saw an 8x size reduction in disk space compared to MyISAM due to improved compression. The hardware cost saving made moving to TokuDB an easy decision.”

Crash Recovery: FictionPress had been using MyISAM initially. “We needed a replacement for MyISAM for small BLOB data,” according to Li. “In fact, we wanted to move away from MyISAM whenever possible to shorten its long crash recovery. InnoDB was an option but TokuDB offered better compression and a smaller storage footprint for both core data and index data for our own data sets.”

Hot Schema Changes: “For performance reasons we need a lot of indexes but also need to add and maintain these indexes quickly,” according to Li. “TokuDB is the only MySQL solution I found that offers Hot Schema changes such as Hot Indexing. Hot Schema changes are a powerful capability which we use to minimize downtime during system-wide upgrades and shorten our application/schema development cycle.”

 

 

PlanetMySQL Voting: Vote UP / Vote 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 scan. No aggregates (which complicate the solution considerably).

SELECT
  rental_id,
  TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days
FROM
  sakila.rental
;

The naive solution

We need to know every 1,000 rows. So we need to count the rows. We do that by using a counter, as follows:

SELECT
  rental_id,
  TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days,
  @row_counter := @row_counter + 1
FROM
  sakila.rental,
  (SELECT @row_counter := 0) sel_row_counter
;

A thing that bothers me, is that I wasn't asking for an additional column. I would like the result set to remain as it were; same result structure. We also want to sleep for 1 second for each 1,000 rows. So we merge the two together along with one of the existing columns, like this:

SELECT
  rental_id +
    IF(
      (@row_counter := @row_counter + 1) % 1000 = 0,
      SLEEP(1), 0
    ) AS rental_id,
  TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days
FROM
  sakila.rental,
  (SELECT @row_counter := 0) sel_row_counter
;

To remain faithful to my slides, I rewrite as follows, and this is the naive solution:

SELECT
  rental_id +
    CASE
      WHEN (@row_counter := @row_counter + 1) % 1000 = 0 THEN SLEEP(1)
      ELSE 0
    END AS rental_id,
  TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days
FROM
  sakila.rental,
  (SELECT @row_counter := 0) sel_row_counter
;

The WHEN clause always returns 0, so it does not affect the value of rental_id.

The factor approach

In the factor approach we wish to keep record of query execution, every 1,000 rows. I introduce a nested WHEN statement which updates time records. I rely on SYSDATE() to return the true time, and on NOW() to return query execution start time.

SELECT
  rental_id +
    CASE
      WHEN (@row_counter := @row_counter + 1) IS NULL THEN NULL
      WHEN @row_counter % 1000 = 0 THEN
        CASE
          WHEN (@time_now := SYSDATE()) IS NULL THEN NULL
          WHEN (@time_diff := (TIMESTAMPDIFF(SECOND, @chunk_start_time, @time_now))) IS NULL THEN NULL
          WHEN SLEEP(@time_diff) IS NULL THEN NULL
          WHEN (@chunk_start_time := SYSDATE()) IS NULL THEN NULL
          ELSE 0
        END
      ELSE 0
    END AS rental_id,
  TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days
FROM
  sakila.rental,
  (SELECT @row_counter := 0) sel_row_counter,
  (SELECT @chunk_start_time := NOW()) sel_chunk_start_time
;

Proof

How can we prove that the queries do indeed work?

We can see if the total runtime sums up to the number of sleep calls, in seconds; but how do we know that sleeps do occur at the correct times?

A solution I offer is to use a stored routines which logs to a MyISAM table (a non transactional table) the exact time (using SYSDATE()) and value per row. The following constructs are introduced:

CREATE TABLE test.proof(
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  dt DATETIME NOT NULL,
  msg VARCHAR(255)
) ENGINE=MyISAM;

DELIMITER $$
CREATE FUNCTION test.prove_it(message VARCHAR(255)) RETURNS TINYINT
DETERMINISTIC
MODIFIES SQL DATA
BEGIN
  INSERT INTO test.proof (dt, msg) VALUES (SYSDATE(), message); RETURN 0;
END $$
DELIMITER ;

The prove_it() function records the immediate time and a message into the MyISAM table, which immediately accepts the write, being non-transactional. It returns with 0, so we will now embed it within the query. Of course, the function itself incurs some overhead, but it will nevertheless convince you that SLEEP()s do occur at the right time!

SELECT
  rental_id +
    CASE
      WHEN (@row_counter := @row_counter + 1) IS NULL THEN NULL
      WHEN @row_counter % 1000 = 0 THEN
        CASE
          WHEN (@time_now := SYSDATE()) IS NULL THEN NULL
          WHEN (@time_diff := (TIMESTAMPDIFF(SECOND, @chunk_start_time, @time_now))) IS NULL THEN NULL
          WHEN SLEEP(@time_diff) + test.prove_it(CONCAT('will sleep for ', @time_diff, ' seconds')) IS NULL THEN NULL
          WHEN (@chunk_start_time := SYSDATE()) IS NULL THEN NULL
          ELSE 0
        END
      ELSE 0
    END AS rental_id,
  TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days
FROM
  sakila.rental,
  (SELECT @row_counter := 0) sel_row_counter,
  (SELECT @chunk_start_time := NOW()) sel_chunk_start_time
;

mysql> SELECT * FROM test.proof;
+----+---------------------+--------------------------+
| id | dt                  | msg                      |
+----+---------------------+--------------------------+
|  1 | 2011-11-01 09:22:36 | will sleep for 1 seconds |
|  2 | 2011-11-01 09:22:36 | will sleep for 0 seconds |
|  3 | 2011-11-01 09:22:36 | will sleep for 0 seconds |
|  4 | 2011-11-01 09:22:36 | will sleep for 0 seconds |
|  5 | 2011-11-01 09:22:36 | will sleep for 0 seconds |
|  6 | 2011-11-01 09:22:36 | will sleep for 0 seconds |
|  7 | 2011-11-01 09:22:38 | will sleep for 1 seconds |
|  8 | 2011-11-01 09:22:38 | will sleep for 0 seconds |
|  9 | 2011-11-01 09:22:38 | will sleep for 0 seconds |
| 10 | 2011-11-01 09:22:38 | will sleep for 0 seconds |
| 11 | 2011-11-01 09:22:38 | will sleep for 0 seconds |
| 12 | 2011-11-01 09:22:40 | will sleep for 1 seconds |
| 13 | 2011-11-01 09:22:40 | will sleep for 0 seconds |
| 14 | 2011-11-01 09:22:40 | will sleep for 0 seconds |
| 15 | 2011-11-01 09:22:40 | will sleep for 0 seconds |
+----+---------------------+--------------------------+

The above query is actually very fast. Try adding BENCHMARK(1000,ENCODE('hello','goodbye')) to rental_id so as to make it slower, or just use it on a really large table, see what happens (this is what I actually used to make the query run for several seconds in the example above).

Observant reads will note that the "will sleep..." message actually gets written after the SLEEP() call. I leave this as it is.

Another very nice treat of the code is that you don't need sub-second resolution for it to work. If you look at the above, we don't actually go to sleep every 1,000 rows (1,000 is just too quick in the query -- perhaps I should have used 10,000 seconds). But we do make it once a second has elapsed. Which means it works correctly on average. Of course, the entire discussion is only of interest when a query executes for a substantial number of seconds, so this is just an anecdote.

And the winner is...

Wow, this contest was anything but popular. Marc Alff is the obvious winner: he is the only one to suggest a solution

But Marc uses a very nice trick: he reads the PERFORMANCE_SCHEMA. Now, I'm not sure how the PERFORMANCE_SCHEMA gets updated. I know that the INFORMATION_SCHEMA.GLOBAL_STATUS table does not get updated by a query until the query completes (so you cannot expect a change in innodb_rows_read throughout the execution of the query). I just didn't test it (homework, anyone?). If it does get updated, then we can throttle the query based on InnoDB page reads using a simple query. Otherwise, an access to /proc/diskstats is possible, assuming no apparmor or SELinux are blocking us.

Marc also uses a stored function, which is the clean way of doing it; however I distrust the overhead incurred by s stored routine and prefer my solution (which is, admittedly, not a pretty SQL sight!).

Happy throttling!

PlanetMySQL Voting: Vote UP / Vote 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 key buffer is empty or (has been) completely full.

If the the key buffer is full:
key_buffer_coefficient = key_cache_block_size/(key_buffer_size/Key_blocks_used)

If the the key buffer is empty:
key_buffer_coefficient = key_cache_block_size/(key_buffer_size/Key_blocks_unused)

The 'fixed' formula:

1 - ((Key_blocks_unused * key_cache_block_size) / (key_buffer_size * key_buffer_coefficient))

The result is shown below:

The overhead in the key buffer will be allocated on startup, so the larger overhead for a larger key buffer will result in more memory usage, even if MyISAM is only used for the internal mysql tables.
PlanetMySQL Voting: Vote UP / Vote 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 key buffer is empty or (has been) completely full.

If the the key buffer is full:
key_buffer_coefficient = key_cache_block_size/(key_buffer_size/Key_blocks_used)

If the the key buffer is empty:
key_buffer_coefficient = key_cache_block_size/(key_buffer_size/Key_blocks_unused)

The 'fixed' formula:

1 - ((Key_blocks_unused * key_cache_block_size) / (key_buffer_size * key_buffer_coefficient))

The result is shown below:

The overhead in the key buffer will be allocated on startup, so the larger overhead for a larger key buffer will result in more memory usage, even if MyISAM is only used for the internal mysql tables.
PlanetMySQL Voting: Vote UP / Vote 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 accepts the max_heap_table_size, which limits the size of a single table in memory. Hrmmm… In memory…

Why limit?

I’m not as yet aware of the specific requirements of said company, but this is not the first time I heard this question.

The fact is: when MySQL runs out of disk space, it goes with a BOOM. It crashed ungracefully, with binary logs being out of sync, replication being out of sync. To date, and I’ve seen some cases, InnoDB merely crashes and manages to recover once disk space is salvaged, but I am not certain this is guaranteed to be the case. Anyone?

And, with MyISAM…, who knows?

Rule #1 of MySQL disk usage: don’t run out of disk space.

Workarounds

I can think of two workarounds, none of which is pretty. The first involves triggers (actually, a few variations for this one), the second involves privileges.

Triggers

The following code (first presented in Triggers Use Case Compilation, Part II) assumed the DATA_LENGTH and INDEX_LENGTH values in INFORMATION_SCHEMA to be good indicators:

DROP TABLE IF EXISTS `world`.`logs`;
CREATE TABLE  `world`.`logs` (
  `logs_id` int(11) NOT NULL auto_increment,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `message` varchar(255) character set utf8 NOT NULL,
  PRIMARY KEY  (`logs_id`)
) ENGINE=MyISAM;

DELIMITER $$

DROP TRIGGER IF EXISTS logs_bi $$
CREATE TRIGGER logs_bi BEFORE INSERT ON logs
FOR EACH ROW
BEGIN
  SELECT DATA_LENGTH+INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='world' AND TABLE_NAME='LOGS' INTO @estimated_table_size;
  IF (@estimated_table_size > 25*1024) THEN
    SELECT 0 FROM `logs table is full` INTO @error;
  END IF;
END $$

DELIMITER ;

Or, you could write your own UDF, e.g. get_table_file_size(fully_qualified_table_name) and be more accurate:

DELIMITER $$

DROP TRIGGER IF EXISTS logs_bi $$
CREATE TRIGGER logs_bi BEFORE INSERT ON logs
FOR EACH ROW
BEGIN
  SELECT get_table_file_size('world.logs') INTO @table_size;
  IF (@table_size > 25*1024) THEN
    SELECT 0 FROM `logs table is full` INTO @error;
  END IF;
END $$

DELIMITER ;

(Same should be done for UPDATE operations)

In both workarounds above, triggers are pre-defined. But triggers are performance-killers.

How about preventing writing to the table only when it’s truly on the edge? A simple shell script, spawned by a cronjob, could do this well: get the file size of a specific table, and test if it’s larger than n bytes. If not, the script exits. If the file is indeed too large, the scripts invokes the following on mysql:

DELIMITER $$

DROP TRIGGER IF EXISTS logs_bi $$
CREATE TRIGGER logs_bi BEFORE INSERT ON logs
FOR EACH ROW
BEGIN
  SELECT 0 FROM `logs table is full` INTO @error;
END $$

DELIMITER ;

So, during most of the time, there is no trigger. Only when the external script detects that table is too large, does it create a trigger. The trigger has no logic: it simply raises an error (PS, use raise in MySQL 5.5).

Privileges

Another way to work around the problem is to use security features. Instead of creating a trigger on the table, REVOKE the INSERT & UPDATE privileges from the appropriate user on that table.

This may turn out to be a difficult task, since MySQL has no notion of fine grain changes. That is, suppose we have:

GRANT INSERT, UPDATE, DELETE, SELECT ON mydb.* TO 'webuser'@'%.webdomain'

If we just do:

REVOKE SELECT ON mydb.logs FROM 'webuser'@'%.webdomain'

We get:

There is no such grant defined for user 'webuser' on host '%.webdomain' on table 'logs'.

So this requires setting up privileges on the table level in the first place. Plus note that as long as the grants on the database level do allow for INSERTs, you cannot override it on the table level.

Other ideas?

I never actually implemented table disk quota. I’m not sure this is a viable solution; but I haven’t heard all the arguments in favor as yet, so I don’t want to rule this out.

Please share below if you are using other means of table size control, other than the trivial cleanup of old records.

PlanetMySQL Voting: Vote UP / Vote 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 these files make sure you have the file permissions set correctly.

This may not be a complete list of files used my MySQL.  It most certainly doesn’t describe everything each table is used for. If you know of ways to replace a missing file or what happens to MySQL when a file is missing that I haven’t described here, please leave me a comment or email me.  I’ll update this document and give your a reference.

my.cnf

This file alters the default configuration settings. MySQL looks in the /etc directory for my.cnf. You should review this file to insure you are looking in the right place for all other MySQL files.  MySQL WILL run without it.  If you have trouble getting MySQL to start, read the error log then try moving are renaming this file.

mysql <directory>

On Linux servers the default location for MySQL files is /var/lib/mysql.  This directory is controlled by the “datadir” variable.

Do I need to say, deleting this directory deletes everything?

ibdata1

If your remove this file your InnoDB DATA IS GONE and MySQL will recreate an empty file.

If you are not using the innodb_file_per_table option (default), this file holds almost ALL of your data in InnoDB tables.  This file is all but useless without its corresponding  ‘.frm’ file for each table in the right database directory. If all you have is the .frm files you can recreate the structure of your tables. (See below.)

Idbdata1 can get really big. The default size is 10MB. MySQL will automatically extended it by the default size as needed. If MySQL crashed, some of your InnoDB data may be in your transaction logs (ib_logfile.*).

By design the InnoDB file does not shrink. The safest way to shrink this file is to take a complete backup, stop MySQL, remove the ib* files, start MySQL and restore all your data.  REALLY.

DatabaseName <directory in mysql>

Each MySQL database has a directory named after it.  Each directory holds the meta data for the database. Your MyISAM data is in this directory. InnoDB tables may be here if the innodb_file_per_table variable is used. By default InnoDB tables are in the ibdata1 file (see above).

If you delete the directory your data may be gone. MyISAM data WILL be lost.  InnoDB tables may still be in the ibdata1.  If so, you will need to recreate the meta data files to recover your data.

Creating a directory is almost equivalent to ‘create database’.  If a directory exists MySQL will show you have a database by that name. The create database command may also creates a db.opt file.

<TableName>.frm

This file is key to both InnoDB and MyISAM databases. It is the meta data to the location of your data. It contains the table column definitions.

If you remove this file MySQL will tell you your DATA doesn’t exist.  It does. Your data is still in the ibdata (.ibd) file or the ibdata1 file. You need to recreate the table to recreate this file. If you don’t know the exact structure of this table your out of luck.

Stop MySQL and move the .MYD and .MYI files to another directory.  (You might also make a backup copy.) Start MySQL and recreate this table. Stop MySQL and copy the .MYD and .MYI files back to the database directory and restart MySQL.

<TableName>.MYD

THIS IS YOUR MyISAM DATA. If this is all you have, and you know the data structure of the the table, all is not lost.  (See .frm Above.)  You may also need to recreate the .MYI index file.

<TableName>.MYI

This file contains the indexs for your table.  If it becomes corrupt or is deleted you can recreate it using the ‘REPAIR TABLE table_name USE_FRM;’ command.

<TableName>.ibd

THIS IS YOUR InnoDB DATA. If this is all you have, and you know the data structure of the the table, all is not lost.  (See .frm Above.) Unlink MyISAM tables the indexes are contained in this file with your data.

MySQL doesn’t create this file unless you are using the innodb_file_per_table option.

<TableName>.CSV

THIS IS YOUR CSV DATA.  This file contains comma separated text data. These file do not have indexes.

<TableName>.CSM

This file contains meta data for CSV and archive tables.  I have not found what is stored here. I do know it tells MySQL if you are logging to the general logs.

ib_logfile*

This file contains your un-committed transactions data. MySQL uses it to recover from a crash.

If you shut down InnoDB cleanly, you can remove them. MySQL will recreate them.

If you change the size of innodb_log_file_size, you will need to recreate these files by stopping MySQL cleanly and deleting them.

mysql-bin.*

This “Bin Log” files contain any change made to any database. Each transaction is assigned a MASTER_LOG_POS(ion).  These files are not created by default. They are used for replication and point-in-time recovery.

You can stop the server and remove these files IF you remove the mysql-bin.index file as well.  MySQL creates a new bin log file each time it starts or the logs are flushed.  Deleting these files will the server is running will break replication.

mysql-bin.index

This fail is used by MySQL to keep a Bin Log list.  It is a simple text file like;

./mysql-bin.000001

./mysql-bin.000002

./mysql-bin.000003

If you remove this file, MySQL will recreate it with only the newest bin log name. If you need to remove old bin logs use the command “purge binary logs [to mysql-bin.######] [before “yyyy-mm-dd”]”.

You can control the number of bin logs using the expire_logs_days variable.

mysqld.log

This is MySQL’s primary administration log. MySQL reports starts and stops as well as some warning and errors in this file.  If MySQL crashes, mysqld_safe will restart it. This log will report this.

You can delete this file if needed.

slow.log

The slow query log consists of all SQL statements that took more than long_query_time seconds to execute and (as of MySQL 5.1.21) required at least min_examined_row_limit rows to be examined.

You can delete this file if needed.

db.opt

Database characteristics, like the CHARACTER SET clause are stored in the db.opt file. You may have strange query results if this file is missing. MySQL will use it’s default. You can recreate this file my altering the table with the correct settings.

mysql.pid

The PID file hold the process ID number for the running server. MySQL creates this file and scripts that start and stop MySQL use it to control MySQL.

MySQL will remove this file when is stops. You should not delete it if MySQL is running. If mysql is NOT running and the file exists MySQL may have crashed and you should delete this file.

References:

FULL DISK
http://dev.mysql.com/doc/refman/5.1/en/full-disk.html

MySQL Database Backup .MYI and .MYD
http://www.aeonity.com/frost/mysql-database-backup-myi-myd

Recovering from Crashes
http://dev.mysql.com/tech-resources/articles/recovering-from-crashes.html

Tweet

PlanetMySQL Voting: Vote UP / Vote 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 concurrent behavior. You may have hidden dependencies of MyISAM table lock behavior in your applications, also check if your application handled deadlocks well. MyISAM will not produce deadlocks for Innodb you should always see deadlocks as a probability as long as you write to the database. They may be rate but it is rather hard to guaranty you will never run into them.

Performance Benchmarks – Innodb and MyISAM have different performance properties and you can’t really say one is faster than other it is very much workload dependent, and again concurrent tests should be important here. Innodb also may result in different plans for some queries which is rather easy to check with mk-upgrade

Feature Differences – There are some feature differences between MyISAM and Innodb though well it is typically easily spotted by converting tables to Innodb on restored backup. Full text search indexes, GIS, multi-column auto increment keys are great examples. There are also different limits for MyISAM and Innodb – it is possible to have some rows which can be stored in MyISAM but would not fit to Innodb, though this is an exception.

Space Innodb Tables tend to be larger. Again converting schema will show you some of this. Though the best is to take a look at the size after stressing system with load for a while as depending on the schema and usage Innodb tables may increase in size significantly due to fragmentation.

Usage Differences What works well for MyISAM may not work for Innodb and vice versa. You may benefit from different index structure for Innodb, including different primary key setup, as well as you may want to structure workload differently. With MyISAM it is often for people to do updates in small chunks, almost row by row to avoid holding table lock for long time in Innodb you want larger updates to reduce cost of transaction commit. You also may want to avoid excessive SELECT COUNT(*) FROM TBL (with no where clause) which is very fast for MyISAM but does table/index scan for Innodb.

Defaults You need to know two things about defaults for Innodb. First Depending on MySQL version they may be somewhere from suboptimal to absolutely disastrous, you do not want to try to run Innodb or XtraDB with them. Second Innodb is tuned to be ACID by default – if you’re moving from MyISAM often you do not need such strong guarantees and can at least change innodb_flush_log_at_trx_commit=2. It still will be much more secure than storing data in MyISAM. The 3 most important values to check are innodb_flush_log_at_trx_commit, innodb_buffer_pool_size and innodb_log_file_size. There are a lot more options for fine tuning but make sure at least these are right.

---

Entry posted by peter | No comment

Add to: | | | |

PlanetMySQL Voting: Vote UP / Vote 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 | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+

As we see, there is no InnoDB in the list. So, InnoDB was not started.
Next we look into the error log to find out, why InnoDB was not started. And we saw this:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 134217728 bytes!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[Note] Event Scheduler: Loaded 0 events
[Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.51-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

So, InnoDB was not stared as the size of log files was changed in the my.cnf and the old log files were not moved. Although, mysql server was started, but without InnoDB. In this case mysql restored the tables, but the storage engine was substituted from InnoDB to MyISAM. For example if we create a table with non-existing storage engine, MySQL will use MyISAM instead:


mysql> create table aaa(i int) engine=non_existing_engine;
Query OK, 0 rows affected, 2 warnings (0.16 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1286 | Unknown table engine 'non_existing_engine' |
| Warning | 1266 | Using storage engine MyISAM for table 'aaa' |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)

That was happened: mysql used MyISAM instead of InnoDB, produced warnings, but they are usually ignored.

The fix was easy: restart mysql using this instructions (http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html) and upload the dump again (or convert myisam to innodb manually).

PlanetMySQL Voting: Vote UP / Vote DOWN

This week I was one of the presenters at the first Surge Scalability Conference in Baltimore. An event that focused not just on one technology but on what essential tools, technologies and practices system architects need to know about for successfully scaling web applications.

While MySQL is an important product in many environments, it is only one component for a successful technology stack and for many organizations is one of several products that manage your data.

My presentation was on the common MySQL scalability mistakes and how to avoid them. This is a problem/solution approach and is a companion talk with Successful MySQL Scalability which describes design for successfully scalability from the ground up.

PlanetMySQL Voting: Vote UP / Vote DOWN

Why should you convert myisam tables to innodb ? For the perfectly simple reason that innodb tables do not get locked by concurrent selects & inserts. So if you find that your myisam table is suffering for too many locks - due to concurrent selects and inserts, it is time for you to covert the table to innodb. The simple query which does the trick is Alter table myisam_table_name engine =
PlanetMySQL Voting: Vote UP / Vote DOWN