New answers tagged

0

To reclaim space I would advise running an no-op online scheme change; alter table {table_name} engine = innodb; ^ the above DDL can be completed with pt-online-schema-change online but be sure to read the docs before you start to avoid unexpected behavior.


0

Didn't you ask this question somewhere else? Didn't the answer come back that TEXT fields are preallocated at full size? That is the "allocation" mechanism for SELECT has a bias toward VARCHAR.


0

not sure this help but in my case have table with 3mil row. very simple join or union or loop queries all very slow.. for only 20 row results affter test and test I just put a simple command to query FORCE INDEX (one_colum_indexed) which one_colum_indexed you has to test one by one if your table have multi index field, to look for which one best. all ...


0

I'd say normalize database - and add propper indizes. A single photo doesnt need to carry all info about the album it belongs to - this cries for a separate table for albums - and a relation table that only contains the mapping of photoID<->albumID the same applies - if included - to the photographer (separate table and a mapping table between photoID ...


0

I was able to fix this using the brute force method of backing up my databases, removing and reinstalling MariaDB, and restoring my databases.


0

I think I have seen cases where buffer_pool is set really low (much less than 128M) The transaction was complex, not simply "big". Errors indicated that the buffer_pool was out of space (or something like that). You are checking for errors, yes? "Big" is not a problem -- InnoDB is designed to handle arbitrarily many rows being held for ROLLBACK, etc. But ...


0

NO! MySQL is not designed to allow two servers to access the same databases. Or, maybe... If your goal is to provide redundancy for the case of server failure, then maybe you could set up a non-running MySQL on a secondary server, ready to mount the disk subsystem in the case of failure. Consider, instead, Clustering (Galera, PXC, InnoDB Cluster).


0

Switch from MyISAM to InnoDB. This covers the differences and gotchas in conversion: http://mysql.rjweb.org/doc.php/myisam2innodb As for the settings, see http://mysql.rjweb.org/doc.php/memory . That is summarized below: If you have 64GB of RAM that is mostly used for MySQL, then these may be close to optimal: During the transition from MyISAM to ...


0

But it’s safe to assume PK is indeed present ? Yes. (The following applies only to InnoDB; other engines probably differ.) A copy of the PK column(s) is tacked on the end of regular indexes. FULLTEXT and SPATIAL work differently, so this discussion may not apply. There has been some chatter about whether the FK columns are redundantly added, or only if ...


0

It's difficult to answer without knowing the nature of the data being copied and maybe of the integration software that is used. But I decided to share my guesses hoping it may lead to better diagnostic: Row length increase may be because texts were converted into Unicode during migration. Lower row count maybe due to some kind of deduplication that also ...


0

Please note that you ran ALTER TABLE ... ENGINE=InnoDB; That is a full table rebuild whether you go from MyISAM to InnoDB, or InnoDB to InnoDB (known as the NULL ALTER). Please note the paragraph from the MySQL 5.7 Docs on innodb_file_format: The innodb_file_format default value was changed to Barracuda in MySQL 5.7. The innodb_file_format setting ...


1

I just encountered a question 5 days ago : InnoDB errors and crashes with MyISAM database? When troubleshooting the issue in a live chat session, I discovered that dirty pages from Temporary InnoDB Tables were occupying the InnoDB Buffer Pool. The file ibtmp was 482MB with 75% of the buffer pool being dirty since innodb_max_dirty_pages_pct was 75. The ...


2

I have some annoying news for you. MySQL 5.7 has InnoDB enabled permanently. It's in the Docs: Important InnoDB cannot be disabled. The --skip-innodb option is deprecated and has no effect, and its use results in a warning. It will be removed in a future MySQL release. This also applies to its synonyms (--innodb=OFF, --disable-innodb, and so forth). ...


0

(I'm pretty sure of the following.) InnoDB does not depend on "dirty" pages for recovery. Recovery is guaranteed by what is stored in iblog* and the double-write buffer. The presumption is that the information about a transaction can be more compactly stored, and more rapidly written to disk, in the redo log (versus the actual table). The log files are ...


1

I know this question is a year old but thought I would post an answer considering there is no real answer to this that I have found. In this answer, for the remote server commands, I'm assuming you're using a Linux Debian-based OS. Otherwise there is another option at the end. This sounds like an issue in the storage engine "ibdata1" file in your /var/lib/...


0

On one occasion, I helped a company that had a replica ahead of its master. It was a very odd situation. It isn't supposed to be possible. What caused it was that their master server's hard drives were failing, and data was not able to be written to disk. So the data committed to the InnoDB redo log and tablespace wasn't being saved. But the transactions ...


Top 50 recent answers are included