Tag Info

Hot answers tagged

35

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 ...


29

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(' ...


25

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 ...


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 ...


18

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 ...


15

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 ...


11

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 ...


9

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 ...


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

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, ...


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 ...


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

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 ...


6

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. ...


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

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 ...


6

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

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 ...


5

There is no natural, implied or intrinsic ordering to a table Any order is only guaranteed by the outermost ORDER BY If this is slow, then you can either make the index covering (a UNIQUE constraint is an index already) make it the clustered index (which is covering by its nature)


5

To be honest, with a low traffic environment running on 5.1, you shouldn't have any problems with MyISAM. The biggest downfall of MyISAM is table-locking WRITES, IMO. But with limited data in the tables, this won't be noticeable. MyISAM is probably easier to manage from a DBA standpoint with recovery and repair. And in 5.1, I think InnoDB's benchmarks ...


5

A) Take the backup from a replicating slave. Zero performance loss. B) Use LVM to take a consistent snapshot. Copy the database files at your leisure. C) Use Rsync over a remote connection to copy the data files, then do a final Rsync with the database briefly locked for a couple seconds. As an aside, how are they taking a consistent backup with MyISAM ...


5

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 ...


5

Because R-Trees are not B-Trees: For MyISAM tables, SPATIAL INDEX creates an R-tree index. For storage engines that support nonspatial indexing of spatial columns, the engine creates a B-tree index. A B-tree index on spatial values will be useful for exact-value lookups, but not for range scans. Adding a completely different storage structure for ...


5

My recommendations are to start as follows: Go for the latest version of the MySQL Server that you can get, there have been some pretty impressive work done on 5.6 although the production ready version is 5.5 Go for InnoDB - comes as the default for the higher MySQL versions Configure InnoDB as follows: file_per_table - creates an data file for each ...


5

InnoDB supports compression as of MySQL 5.1 (with InnoDB Plugin), or natively in MySQL 5.5. The level of compression really depends on your table content, but I see 75% reduction in size as common. Reads and writes to compressed tables involve more CPU. InnoDB will support your foreign keys as well as any indexes. I would say it is a fairly good ...



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