11 votes

Inserts in MySQL 8 are slower than Inserts in MySQL 5.7

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-...
Sybil's user avatar
  • 2,498
9 votes
Accepted

Can converting MyISAM to InnoDB cause problems?

Yes, it can cause a number of troubles. However, troubles are rare. There is a list of possible 'troubles' that you might encounter, including possible solutions in a discussion format in the MariaDB ...
Rick James's user avatar
  • 78.1k
8 votes

MySQL Performance Tuning for MyISAM

You clearly stated you goal in the question The goal is to significantly improve the performance of searches on the MyISAM table OK let's tune the MyISAM side of things. I have four(4) suggestions for ...
RolandoMySQLDBA's user avatar
7 votes

What are the main differences between InnoDB and MyISAM?

MyISAM MyISAM is a storage engine for MySQL. Before MySQL 5.5 it was the default storage engine for MySQL. It is based on the older ISAM storage engine. MyISAM is optimized for environments with heavy ...
wogsland's user avatar
  • 416
5 votes

using Aria vs InnoDB vs MyISAM

We have millions of tables deployed running ARIA in production. It is definitely a better choice over MyISAM as far as crashing and recovering from a crashed table. If you have high concurrent write ...
jozwikjp's user avatar
  • 346
5 votes

TRUNCATE TABLE statement sometimes hangs

There is a long standing bug in InnoDB that affects both MySQL (#98869) and MariaDB (MDEV-9459) which results in long stalls, proportional to the size of your buffer pool (typically around 1 second ...
Gordan Bobić's user avatar
4 votes

Migrating MySQL from Windows to Linux

OK Brace yourself. You can migrate the table from Windows by copying it and converting it. STEP 01 Copy those three(3) files into the data folder /usr/var/lib/mydb in Linux You should have /usr/var/...
RolandoMySQLDBA's user avatar
4 votes
Accepted

Is there any disadvantages if i shift from myisam to innodb in mysql 5.7?

Oracle's goal seems to be to get rid of MyISAM. Version 5.7 has virtually all the improvements to eliminate the 'desire' to use MyISAM. The next version, 8.0, is attempting to finish the task -- ...
Rick James's user avatar
  • 78.1k
4 votes

"Create Unique Index" on big table taking too long time

There are multiple problems. Auto_inc of 922M is half way to the 2 billion limit on INT SIGNED. Suggest you change to INT UNSIGNED (4 billion limit) during the next ALTER. MyISAM saves disk space, ...
Rick James's user avatar
  • 78.1k
4 votes
Accepted

MariaDB 10.3: InnoDB much slower than MyISAM

TLDR; The problem turned out to be the file system. Don't use BTRFS for database storage. I did an analysis of MariaDB timings for different file systems using some of the data that I have to work ...
Raul Laasner's user avatar
4 votes
Accepted

Estimate query speed for +1.5 billion rows mysql table

I was able to solve this slowness problem by executing the following query: alter table fingerprints order by hash; I have a lot of repetition on the hash column (there is 34m unique hashes only). ...
msuliman's user avatar
4 votes
Accepted

Will too many tables slow Mysql down?

Overall, splitting tables adds overhead for a perceived benefit that isn't there. Tables of a few billion rows do need to be carefully designed as far as index and normalization, however splitting ...
danblack's user avatar
  • 7,719
4 votes

MySQL - change all tables' ENGINE from MyISAM to InnoDB with a single query

My strong advice is not to try and do this on a running database! MyISAM isn't transactional in any case, so any operation that spans many tables can't be performed in this manner (it can in InnoDB - ...
Vérace's user avatar
  • 29.9k
4 votes

Why MySQL performs so poor on SHOW PROCEDURE STATUS?

The "meta" commands (SHOW...) are generally not designed for performance. The presumption is that you rarely, if ever, need to run them. And when you do, it is more of a "one-time&...
Rick James's user avatar
  • 78.1k
4 votes

mysqldump with --no-tablespaces on InnoDB with index?

"Tablespaces" are for clumping tables into a single .ibd. The mysqldump options relating to that control whether to output the DDL statements such as CREATE TABLESPACE. mysqldump does not ...
Rick James's user avatar
  • 78.1k
3 votes

Will OPTIMIZE TABLE have any impact on my data?

Don't Ctrl+C during an optimize operation on a large table. A few months ago (late 2017) I corrupted a 150 GB MyISAM MySQL table when I Ctrl+C'd during the optimize run.
Nazar's user avatar
  • 31
3 votes

Consistent logical backup of databases that use MyISAM and InnoDB engines

As an additional idea for your situation, maybe it's helpful to spend some thoughts on setting up a master slave setup, where your backup is made from the slave. A MySQL db with a mix of InnoDB and ...
Raffael Meier's user avatar
3 votes
Accepted

Converting from InnoDB tables to innodb_file_per_table

The process you mentioned works fine but it takes much amount of time if the tables are bigger in size Here the way you can do it quickly .but it requires some down time depends on the table size . ...
Narendra's user avatar
3 votes
Accepted

Will hacking away ibdata1 destroy databases with only MyISAM tables?

Since you are using MySQL 5.1.73, you can blow away the ibdata1 file without worries. First, let's look inside ibdata1 (picture created by Percona CTO Vadim Tkachenko) STEP 01 : Make sure there is ...
RolandoMySQLDBA's user avatar
3 votes

Migrating MySQL from Windows to Linux

You are migrating from 5.0 to 5.7, so the safest way is to dump and restore using mysqldump, you can find this link useful http://mysqlserverteam.com/upgrading-directly-from-mysql-5-0-to-5-7-with-...
jerichorivera's user avatar
3 votes

When the number of users increases, the site is slowed down

MyISAM. Say no more. The "table locks" are probably what causes the performance to decline as you get more 'users'. Some tips on the conversion to InnoDB: http://mysql.rjweb.org/doc.php/...
Rick James's user avatar
  • 78.1k
3 votes
Accepted

How can I trace the root cause of a MySQL database table that crashed?

The cause is almost always a non-graceful shutdown -- abort, power failure, etc. So, I suggest you already know what is causing each occurrence. It goes something like this... When a MyISAM table ...
Rick James's user avatar
  • 78.1k
3 votes

Estimate query speed for +1.5 billion rows mysql table

(UNHEXing is not a significant part of the problem.) The real problem is the randomness of hashes. The leads to jumping around lots of places on disk. Let's dissect the query. The IN list is a list ...
Rick James's user avatar
  • 78.1k
3 votes

MySQL: Is it safe to set `key_buffer_size` to 0 if the db doesn't have any MyISAM table?

In the MySQL Docs for 5.5, 5.6, 5.7, and 8.0 the minimum value is 8. If you set it to 0 in my.cnf and restart mysqld, it may go to 8 anyway. Since key_buffer_size is dynamic, login to mysql and run ...
RolandoMySQLDBA's user avatar
3 votes
Accepted

Mysql separate username from client table for performance and use myisam for it

The MyISAM vs InnoDB Myth "InnoDB is not good as MyISAM" That's an old wives' tale. Erase it from your mind. InnoDB has improved a lot since that rumor was started. You now have user information ...
Rick James's user avatar
  • 78.1k
3 votes

Why MySQL performs so poor on SHOW PROCEDURE STATUS?

Please note that most of INFORMATION_SCHEMA is implemented as temporary memory tables (See my old post How is INFORMATION_SCHEMA implemented in MySQL?) When mysqld starts, information from mysql.proc ...
RolandoMySQLDBA's user avatar
3 votes

Default 0000-00-00 00:00:00 DateTime values in InnoDB

there are two sql modes NO_ZERO_IN_DATE,NO_ZERO_DATE which control that behaviour, as they only apply after you converted your table. that isw hy you can convert such tables but you will not be able ...
nbk's user avatar
  • 8,268
2 votes

How can I change the default storage engine in phpmyadmin?

UPDATE `information_schema`.`GLOBAL_VARIABLES` SET `VARIABLE_VALUE`="InnoDB" WHERE `VARIABLE_NAME`="DEFAULT_STORAGE_ENGINE"
Owen Parker's user avatar
2 votes

Is the overhead of frequent query cache invalidation ever worth it?

I can think of a perfect case for it, and we have tested thoroughly and run it in production...I call it the "fast lane" clustering strategy: If you do read-write splitting with a proxy like MaxScale,...
Christopher McGowan's user avatar
2 votes
Accepted

MariaDB/Galera Cluster with 3 nodes - Change ENGINE from MyISAM to InnoDB without crash

I need the tables to be InnoDB and in sync. Those two statements crashing. Does it crash with a stack trace in the server logs? Could you share it here? I tried the above two commands on a 2-node ...
Nirbhay Choubey's user avatar

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