Hot answers tagged

34

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


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


25

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


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


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


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


16

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

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


15

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


15

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


14

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


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


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

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

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


12

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


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


10

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


10

Don't OPTIMIZE. Seriously, there is almost never a need to OPTIMIZE an InnoDB table. 90GB would take hours, maybe days, depending on tunables, disk speed, RAID, etc, etc. OPTIMIZE may free up some of the free space caused by DELETEs and UPDATEs. But, normally, InnoDB takes care of itself, rarely leaving more than 50% of the disk footprint "free". Normal ...


10

I have three suggestions SUGGESTION #1 : Rewrite the query You should rewrite the query as follows SELECT http, COUNT( http ) AS count FROM reqs WHERE date >= ( DATE(NOW() - INTERVAL 1 DAY) + INTERVAL 0 SECOND ) GROUP BY http ORDER BY count; or SELECT * FROM ( SELECT http, COUNT( http ) AS count FROM reqs WHERE date >= ( DATE(NOW() ...


9

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


9

A difference between 5.7 and 8.0 is that binary logging (to be used for replication and PITR) is on by default in MySQL 8.0. To run without binary logging in 8.0, start the MySQL server with --disable-log-bin. More details The Binary Log The binary log contains “events” that describe database changes such as table creation operations or changes to ...


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 MyISAM Storage Engine is furiously notorious for performing full table locks for any DML (INSERTs, UPDATEs, DELETEs). InnoDB would definitely solve that issue in the long term. I wrote about pros and cons of using MyISAM vs InnoDB InnoDB vs MyISAM with many indexes (Jul 05, 2012) Which is faster, InnoDB or MyISAM? (May 03, 2012) Any gotchas at all with ...


8

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 engine='MyISAM' ...


8

From the sound of this, you could use a different DB infrastructure. Based on your aforementioned needs, I have a suggestion but would require a little compromise. SUGGESTION #1 : Use MySQL Replication on the Same Server but Different Disk Your second need (No need of real-time replication) would have to take a slight backseat in this suggestion. Since ...


8

You mentioned before you are running MySQL 5.5.29 16 GB RAM Here are some good settings to start with innodb_buffer_pool_size=4G innodb_buffer_pool_instance=2 innodb_read_io_threads=12 innodb_write_io_threads=12 innodb_io_capacity=300 innodb_log_file_size=128M Don't do the conversion yet !!! Put there settings in my.cnf, then do the following service ...


8

INITIAL OBSERVATIONS Process ID 42686 says its preparing to execute a SELECT query There are some sleeping connections All other processes cannot acquire a table lock I would have expected an UPDATE, DELETE, or INSERT to do the lock. There are no claiming ownership of the table in question. Can't see the full query in Process ID 42686, but I suspect it ...


8

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


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