Tag Info

Hot answers tagged

42

First major difference I see is that InnoDB implements row-level lock while MyISAM can do only a table-level lock. You will find better crash recovery in InnoDB. However, it doesn't have FULLTEXT search indexes, as does MyISAM. InnoDB also implements transactions, foreign keys and relationship constraints while MyISAM does not. The list can go a bit ...


33

Another major difference not as yet mentioned is how caching for each storage engine is done. MYISAM The main mechanism used is the key cache. It only caches index pages from .MYI files. To size your key cache, run the following query: SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), SUBSTR(' ...


33

The only way MyISAM can be faster that InnoDB would be under this unique circumstance MyISAM When read, a MyISAM table's indexes can be read once from the .MYI file and loaded in the MyISAM Key Cache (as sized by key_buffer_size). How can you make a MyISAM table's .MYD faster to read? With this: ALTER TABLE mytable ROW_FORMAT=Fixed; I wrote about this ...


20

One more thing: you can backup InnoDB tables just by taking a snapshot of the filesystem. Backing up MyISAM requires using mysqldump and is not guaranteed to be consistent (e.g. if you insert into a parent and a child table, you might find only the child table's row in your backup). Basically, if you have another copy of the data and are only caching it in ...


19

Performing an ALTER to change storage engines won't make rows disappear. However, let me offer some advice since you said you're 'database noobs' in your question. When modifying existing schema or doing anything that could affect data, here's some basic advice: Make a backup first. Have a change plan. Test your plan on an offline host. Have a test plan ...


16

InnoDB offers: ACID transactions row-level locking foreign key constraints automatic crash recovery table compression (read/write) spatial data types (no spatial indexes) In InnoDB all data in a row except for TEXT and BLOB can occupy 8,000 bytes at most. Full text indexing is not available in InnoDB until MySQL 5.6 (Feb 2013). In InnoDB the COUNT(*)s ...


12

There's nothing wrong with using multiple storage engines on the same physical machine, as long as you understand the pros and cons of each. There are performance considerations, feature limitations and use cases for all the plugin storage types. For instance, if you have a small table that's 90% writes, you might choose MyISAM. If the data can be ...


10

In my experience, the most significant difference is the way each engine handles locking. InnoDB uses row locking while MyISAM uses table locking. As a rule of thumb, I use InnoDB for write heavy tables and MyISAM for read heavy tables. Other important differences include: InnoDB support transactions and foreign keys. MyISAM does not. MyISAM uses full ...


10

You have to use InnoDB. Here is why : The major advantages of InnoDB over MyISAM InnoDB caches data and index pages, whereas MyISAM only caches index pages. InnoDB is designed for ACID-compliant transactions InnoDB is designed for row-level locking, MyISAM uses table-level locking. InnoDB is designed for Multiversion Concurrency Control, critical for ...


9

You have to add the line default-storage-engine = InnoDB under the [mysqld] section of your mysql config file (my.cnf or my.ini depending on your operation system) and restart the mysqld service. I don't believe you can change this through PhpMyAdmin.


9

The reason why you experience performance degradation or stall while executing TRUNCATE TABLE is a known issue with this statement. Please refer to Bug #68184:Truncate table causes innodb stalls. There are other bug numbers opened for prior versions as well. You can use: CREATE TABLE log_table_new LIKE log_table; RENAME TABLE log_table TO log_table_old, ...


8

mysqltuner rarely provides any useful information. It uses mostly irrelevant statistics about "hit rates" and puts arbitrary limits on what is an acceptable number of widgets are acceptable. If you are not facing a performance problem, then you don't actually need to solve any of the problems that it presents to you. That being said, here's a little ...


8

You will have to compare the WHERE clauses and GROUP BY and ORDER BY statements of all your queries to make sure your current indexes can support them in their EXPLAIN plans. Yesterday, I answered this question : InnoDB vs MyISAM with many indexes In that question I suggested doing something to the MyISAM table that you can do as well ALTER TABLE orders ...


8

First let me clarify by confirming that MyISAM does not do asynchronous I/O, but that InnoDB does and will by default from MySQL 5.5. Prior to 5.5 it used "simulated AIO" by using worker threads. I think it is also important to distinguish between three situations: Multiple queries executing at once A single query executing in parallel Some sort of ...


7

One of the best ways to convert MyISAM to InnoDB without a whole lot of downtime has just one prerequisite: Use a Replication Slave. Here is a bird's eye view of the plan Create Replication Master/Slave Setup Convert every MyISAM table on the slave to InnoDB Point your app to the Slave Sounds simple? There are a lot of details behind this. Create ...


7

It is rather easy for a MyISAM table to crash. In the header of every MyISAM table is a counter that tracks how many open file handles there are against the table. If you start up mysql and the number in the header does not match the number of actual file handles against, mysqld treates the table as crashed. If a simple REPAIR TABLE mdl_user makes it work ...


7

The reason is very simple. When you insert a row into MyISAM, it just puts it into the server's memory and hopes that the server will flush it to disk at some point in the future. Good luck if the server crashes. When you insert a row into InnoDB it syncs the transaction durably to disk, and that requires it to wait for the disk to spin. Do the math on ...


7

In a simple world, MyISAM is faster for reads, InnoDB is faster for writes. Once you start introducing mixed read/writes, InnoDB will be faster for reads as well, thanks to its Row locking mechanism. I wrote a comparison of MySQL storage engines a few years ago, that still holds true to this day, outlining the unique differences between MyISAM and InnoDB. ...


7

If you need concurrency of heavy UPDATEs and INSERTs, you will want InnoDB If you need deadlock resolution, you will want InnoDB If you want a storage engine that caches both data and indexes, you will want InnoDB If you want to access multiple CPUs effective, you will want InnoDB (and tune it to do so) Please refer to my past articles on InnoDB: Feb ...


7

MyISAM Key Cache You said you have key_buffer_size at 8GB. Question: Do you really have 8GB of MyISAM indexes? Please run this query SELECT KBS/power(1024,0) KBS_BB, KBS/power(1024,1) KBS_KB, KBS/power(1024,2) KBS_MB, KBS/power(1024,3) KBS_GB FROM ( SELECT SUM(index_length) KBS FROM information_schema.tables WHERE ...


6

The gen_clust_index (clustered index) under the hood of InnoDB houses entries of primary keys along with rowids. What is interesting about the use of the gen_clust_index is the fact that any non-unique indexes you create will always have a corresponding rowid for the gen_clust_index of a table. Thus, there are always double index lookups, one for the ...


6

I tend to view MyISAM as the 'default' table choice for MySQL, so I'll point out the differences for most users of InnoDB Row level-locking Foreign key enforcement Transaction support Performance hit on high-use systems


6

I can't tell you if this is a common practice. I can say about my own experience. I always use the best tool for the job, so I mix engines all the time. Most of my projects use MyISAM as the default engine. When I need special features just available on InnoDB, I go for it. When a table is mostly read-only, I choose Archive engine before I could blink. ...


6

With mysqldump you can only safely use --single-transaction if all your tables are InnoDB, otherwise your backup is inconsistent. If you have the requirement for a hybrid backup, then you need the lock-tables on all tables in the backup (default), which will be safe for all engines. It's also worth mentioning that the default options will make sure your ...


6

When it comes to a MyISAM table, deleting rows does trigger a kind of garbage collection in that all the unused space is recording in a linked list. The data length you saw is correct. Your cron job deleted every row less than UNIX_TIMESTAMP(NOW()). This will not cause the table to shrink. All the delete rows are just chained together. THere are other ...


6

You may find this surprising, but you should set the innodb_thread_concurrency to 0 (which is infinite concurrency). This will allow the InnoDB Storage Engine to decide how many concurrency tickets to issue. I wrote a post about InnoDB's multicore engagement (MySQL 5.5, also MySQL 5.1.38 InnoDB Plugin) back on May 26, 2011. According to the MySQL ...


6

Absolutely !!! Just run ALTER TABLE tblname ENGINE=MyISAM; against all tables on the Slave that you want to have the FULLTEXT index. Afterwards, you can run ALTER TABLE tblname ADD FULLTEXT (column[,column]);. Please be very careful not to run DDL against those tables in the Master that are unique to InnoDB that will replicate to the Slave. I have ...


6

Your issues have nothing to due with the version of MySQL. It has to do with the Storage Engine. Answer to PROBLEM #1 : Wow is that slow! Running mysqldump only touches data from the .MYD file of the MyISAM table. Thus, I do not find anything surprising about dumping 163 million rows in 15 minutes. Loading data into Amazon RDS taking 50 hours is not ...


6

Since all tables are MyISAM, this makes my answer easier to express. First, you need to query the INFORMATION_SCHEMA for the tables that have zero rows: SELECT table_schema,table_name FROM information_schema.tables WHERE table_rows = 0 AND table_schema NOT IN ('information_schema','mysql','performance_schema'); Next, formulate the query to drop the empty ...


6

The idea would probably be to look for the empty tables using INFORMATION_SCHEMA.TABLES SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_ROWS = '0' AND TABLE_SCHEMA = 'my_database_only' Then you might be able to produce an SQL query with SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name), ';') AS query FROM INFORMATION_SCHEMA.TABLES WHERE ...



Only top voted, non community-wiki answers of a minimum length are eligible