Hot answers tagged

69

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


67

The architecture of InnoDB demands the use of four basic types of info pages Table Data Pages Table Index Pages Table MetaData MVCC Data (to support Transaction Isolation and ACID Compliance) Rollback Segments Undo Space Double Write Buffer (background writing to prevent reliance on OS caching) Insert Buffer (managing changes to non-unique secondary ...


32

The following is just insane ranting and raving... If you leave all data in one table (no partitioning), you will have O(log n) search times using a key. Let's take the worst index in the world, the binary tree. Each tree node has exactly one key. A perfectly balanced binary tree with 268,435,455 (2^28 - 1) tree nodes would be a height of 28. If you split ...


31

I would instead recommend the official method, which I reproduce here for convenience: To change the number or the size of InnoDB log files in MySQL 5.6.7 or earlier, use the following instructions. The procedure to use depends on the value of innodb_fast_shutdown, which determines whether or not to bring the system tablespace fully up-to-date ...


29

A bit late to the game...but here's a quite comprehensive post I wrote a few months back, detailing the major differences between MYISAM and InnoDB. Grab a cuppa (and maybe a biscuit), and enjoy. The major difference between MyISAM and InnoDB is in referential integrity and transactions. There are also other difference such as locking, rollbacks, and full-...


24

YOUR QUERY SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506; At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506. REALITY CHECK No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them can ...


24

Please look at the Architecture of InnoDB (picture from Percona CTO Vadim Tkachenko) The rows you are deleting is being written into the undo logs. The file ibdata1 should be growing right now for the duration of the delete. According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace: Lots of Transactional Changes Very Long ...


23

Below is the Query to find all the tables which have MyISAM Engine SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema'); Above Query will list all the tables having MyISAM Engine. For how to convert your ...


21

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


21

I think we may have overcomplicated the answer that was in required in my case. I have no doubt that both Roland & Rick James are correct with their creation of a temporary table, injecting only rows that pass the filter NOT LIKE '-%' but the solution for me was "easier" because there was an important error I was unaware of until now and for that I ...


20

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


17

Based on my experience, I would use LOAD DATA INFILE to import your CSV File. The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. Example I found on the internet Load Data example. I tested this example on my box and worked fine Example Table CREATE TABLE example ( `Id` int(11) NOT NULL AUTO_INCREMENT, ...


16

200 million rows is certainly in the range where you could benefit from table partitioning. Depending on your application, you could bet some of the benefits listed below: Ease of purging old data If you need to clear down records more than (say) 6 months old, you can partition the table on the date and then swap out older partitions. This is much faster ...


16

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


16

You should just disable the query cache with [mysqld] query_cache_size = 0 and then restart mysql. Why would I suggest that ??? The Query Cache will always butt heads with InnoDB. It would be nice if InnoDB's MVCC would let queries be served from the query cache if modifications do not affect repeatable reads for other transactions. Unfortunately, InnoDB ...


14

You can toggle InnoDB to store tables per file by adding innodb-file-per-table to your cnf. Innodb really just cares about pages of data at a basic level. In fact, you can set up InnoDB to use just a raw block device with no filesystem what so ever! http://dev.mysql.com/doc/refman/5.5/en/innodb-raw-devices.html There are conveniences to storing tables for ...


14

To add to the responses here covering the mechanical differences between the two engines, I present an empirical speed comparison study. In terms of pure speed, it is not always the case that MyISAM is faster than InnoDB but in my experience it tends to be faster for PURE READ working environments by a factor of about 2.0-2.5 times. Clearly this isn't ...


14

There is only one status variable that cares about sort_buffer_size. That's what you have in the message back in the question : Sort_merge_passes. The MySQL Documentation says: Sort_merge_passes : The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size ...


14

In light of all the things mentioned, it looks like the bottleneck is the join itself. ASPECT #1 : Join Buffer Size In all likelihood, your join_buffer_size is probably too low. According to the MySQL Documentation on How MySQL Uses the Join Buffer Cache We only store the used columns in the join buffer, not the whole rows. This being the case, make ...


13

I took the three strings in your question and added it to a table plus three more string with pankt instead of punkt. The following was executed using MySQL 5.5.12 for Windows mysql> CREATE TABLE artikel -> ( -> id INT NOT NULL AUTO_INCREMENT, -> meldungstext MEDIUMTEXT, -> PRIMARY KEY (id), -> FULLTEXT ...


13

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


13

There are some options that can cause temp tables to materialize as MyISAM tables or can be configured to delay it. Keep in mind that for disk-based temp tables, there are no .frm files, but only .MYD and .MYI files (of course. the .MYI file is never used since it is impossible index an internal temp table). Here are the options: Temp Table Variables That ...


12

OPTIMIZE TABLE basically does three(3) things Shrinks the data pages Shrinks index pages Computes Fresh Index Statistics Conceptually, OPTIMIZE TABLE operates something like this on mydb.mytable USE mydb CREATE TABLE mytabletmp LIKE mytable; INSERT INTO mytabletmp SELECT * FROM mytable; ALTER TABLE mytable RENAME mytablezap; ALTER TABLE mytabletmp RENAME ...


12

Roland's suggestion can be sped up some by doing both things at once: CREATE TABLE tablename_new LIKE tablename; ALTER TABLE tablename_new ENGINE = InnoDB; INSERT INTO tablename_new SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%' ORDER BY primary_key; RENAME TABLE tablename TO tablename_old, tablename_new TO tablename ; DROP TABLE ...


11

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


11

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


11

Cardinality Even with high cardinality, the tipping point used by the MySQL Query Optimizer is either the key distribution or the storage engine. Back on November 13, 2012, I discussed how lopsided keys can make the Query Optimizer choose different indexes (sometime not choose and index at all) : Must an index cover all selected columns for it to be used ...


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


10

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


10

This is the default behaviour but not mandatory. From MySQL docs, Using Per-Table Tablespaces: By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because each table that is created when this setting ...


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