Tag Info

Hot answers tagged

3

InnoDB tables tend to occupy more space than MyISAM tables do. While converting, new physical files are being created, so you have to have free space on the disk (Hard to estimate, but to be safe I would say 120G) If you use Percona tools to make the change online, all modifications on the original table will be stored in a temporary table using triggers. ...


3

If you want a proper database then of the two you should go for innodb - it supports ACID transactions, referential integrity, and smaller granularity locking. myISAM is faster in a number of read-only use cases but: The lack of support for transaction safety can lead to corruption in multi-user situations where several updates happen at the same time ...


3

There are a few things I can clarify for you here: Yes, it is a good practice to delay secondary index creation until after you import the data (starting from MySQL 5.5 - not before). Mysqlpump does this by default. When you delay secondary index creation, internally MySQL will read, sort and then create the index (reducing fragmentation). For MySQL 5.7 ...


1

I am not sure when but sooner or later we're going to see deprecation of MyISAM. InnoDB is performing well and it does have fulltext as well (though performance is a bit of something being worked over there.) InnoDB is even a default storage engine in latest MySQL versions. If you really really need performance from FULLTEXT go with MyISAM (Note ...


1

Have you tested or would it be possible to drop indexes on the destination DB table(s) where you are inserting into, insert those into smaller batched chunks (optimal as indicated above), and then rebuild the indexes on the destination table(s) once all inserts are complete? May be something easy enough to test to confirm.


1

My reason why to not to use innodb_file_per_table is performance. I did some tests for our database with 450 tables on mysql 5.5.45 Linux CentOS release 6.7 For unit tests which inserts fixtures into the database before each test (not using all tables everytime) and also tests itself does work with database a lot (inserts, update, deletes, selects) the ...


1

The problem is actually very simple. Here is what happened When you installed MySQL, the 5 InnoDB systems tables exist in two places inside /var/lib/mysql/mysql as 5 .frm and 5 .ibd files inside the data dictionary within ibdata1 (InnoDB System Tablespace) At some point in your installation, you must have deleted ibdata1. This left the 10 InnoDB system ...


1

Not sure if the issue is resolved. But as suggested here're some ways you can do this : 1. ALTER TABLE Approach : ALTER TABLE ENGINE=INNODB; Considerations for this approach : As you mentioned you have a big table this command will take time to execute. CREATE TABLE and INSERT INTO Approach : You can create a empty table with INNODB storage engine and ...


1

I have an MyISAM2InnoDB blog that covers your question thoroughly. It discusses index differences, especially PRIMARY KEY and FULLTEXT, space (2x-3x is typical), LOCK TABLES, decreasing key_buffer_size and increasing innodb_buffer_pool_size (very important), etc.


1

Solved: ERROR 1030 (HY000) at line 810: Got error -1 from storage engine The above error means you have innodb_force_recovery set in your my.cnf I'm not sure why I couldn't find this easily in mysql documentation, but it's simple enough.


1

By default, XtraDB is used as the InnoDB replacement. If you login to MariaDB and run SHOW ENGINES;, look for the InnoDB. According to the Documentation for SHOW ENGINES;: Note that both MySQL's InnoDB and Percona's XtraDB replacement are labeled as InnoDB. However, if XtraDB is in use, it will be specified in the COMMENT field. See XtraDB and InnoDB. ...


1

You are near or at a limit of row size. There is a limit of about 8000 bytes per record. TEXT fields, by default, store only 767 bytes toward that 8000, and store the rest elsewhere. You have enough TEXT fields to threaten the 8000. Plan A: Have one JSON TEXT field that is a structure containing the 9 structures you now have. This will easily avoid the ...


1

Consider batching your commits. A batch size of 1024 is a good starting size. Change batch sizes until you reach your optimum throughput.


1

When this DML be written to iblog files ? -- "as needed" during the hour When the DML be written to binary log files ? -- Never. Information for the binlog is gathered, but not written until COMMIT. Will the related data pages on disc be modified during 10:00 AM to 11:00 AM -- There may be modifications to the pages. They will be invisible to the user. ...


1

As per documentation your error is: Error: 1261 SQLSTATE: 01000 (ER_WARN_TOO_FEW_RECORDS) Message: Row %ld doesn't contain data for all columns Thus it appears that your table have more column than the file. You will have to specify the column-names explicitly. Also the remaining columns should be nullable. (You might want to share table definition ...



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