Tag Info

Hot answers tagged

4

Forget about mysqltuner and check for human advice. This tool tells you general recommendations that may be useless and even hurtful in some cases. Optimize table is probably going to be useless, but it locks your tables for writes. A consultant may save you time and money in the long run. Swapping should be a no-go for MySQL. Make sure to tune your ...


3

Here's a method you can use without dumping all your InnoDB tables. You will have to drop the foreign key constraints before you can alter the tables. You can generate the drop statements using the INFORMATION_SCHEMA this way: mysql> SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` DROP FOREIGN KEY `', constraint_name, '`;') AS ...


2

What you have seems fine. I would add the following Run this on the Master. SET GLOBAL innodb_fast_shutdown = 0; SET GLOBAL sync_binlog = 1; SET GLOBAL sync_master_info = 1; This will cause everything that has been uncommitted to be committed on shutdown. Then, it flushes the binlogs to disk. On the Slaves, run this SET GLOBAL innodb_fast_shutdown = ...


2

InnoDB Architecture ANALYSIS Somehow, you lost the my_user.frm and my_user.ibd files. The data dictionary still has an entry for that table. You cannot run DROP TABLE my_user; because mysqld looks for the my_user.frm first. Since this is no my_user.frm, the table cannot be dropped. Although my_user.frm does not exist, you cannot run CREATE TABLE my_user ...


2

this file ibdata1 also known as system tablespace, which consistent of undo log even if you have seperated tables into different files; from your process I found it is a really huge transaction, which will produce drampatic quantitiy of undo logs, till MySQL 5.5, undo log space can not be shrink or adjust automatically or manually. so there is a simple way ...


2

MySQL Server will only execute each query in a single foreground thread, there is no support for multi-thread execution at SQL side. It also executes maintenance operations like ALTER TABLEs that can rebuild a whole table in a single thread. However, engines like InnoDB, specially in recent versions, are able to perform its background threads concurrently, ...


1

The --single-transaction option of mysqldump does not do FLUSH TABLES WITH READ LOCK;. It causes mysqldump to setup a repeatable read transaction for all tables being dumped. From your question, you stated that the mysqldump's SELECT for the db_external_notification table is holding up hundreds of INSERT command to that same table. Why is this happening ? ...


1

I finally figured out and resolved my problem through a lot of trial and error. For those who do not have their original ibdata1 file, and only have their .frm and .ibd files, here's how I restored my data. Download and install the MySQL utilities at -> http://dev.mysql.com/downloads/utilities. Go into your command/terminal to open the MySQL utility, ...


1

The main InnoDB data file - usually named ibdata - is essential for you MySQL to be able to understand your .ibd files. If you need to move data between servers using the binary files, you should stop MySQL cleanly and then move all data files, including the ibdata file(s), between directories. A more reliable mechanism of moving data between servers on ...


1

Alan Storm wrote: At the end of a long series of compromises working in a system I didn't design, own, or maintain, I need to convert a number of MySQL InnoDB tables to MyISAM tables Indeed and may I just say that you have my deepest sympathies. This is a guess, so YMMV... but have you looked at your startup parameters for your mysqld daemon? In ...


1

If you want all tables to be MyISAM, here is a vicious way to make MySQL "suck it up and do the work" STEP #1 : mysqldump everything MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" MYSQLDUMP_OPTIONS="--routine --triggers --all-databases" mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLData.sql STEP #2 : Restart ...


1

InnoDB InnoDB can access multiple cores, but don't expect it to be that way out of the box. InnoDB must be configured to do so. Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian Sep 20, 2011 : Multi cores and MySQL Performance Sep 12, 2011 : Possible to make MySQL use more than one core? May 26, 2011 : About single threaded versus ...


1

You should look at this. Mark Leith is a senior MySQL dev. manager. It's a way (MySQL are trying to get better...) that you can track some metrics - not per query like you want, but data access by table. Install the sys schema in your 5.6 instance and see what happens. Oh - yes - you were asking about InnodDB in 5.6 - you will have to enable ...


1

Innodb_log_writes monitor only writes to the innodb transaction log. InnoDB also writes to the double write buffer, data (aka primary key) and secondary indexes on the tablespace, change buffer, undo space, ... Some of these can be buffered on memory, at least for some amount of time. Additionally, MySQL may write to the mysql binary, general or slow log, ...


1

According to the MySQL Documentation on innodb_file_format The file format to use for new InnoDB tables. Currently, Antelope and Barracuda are supported. This applies only for tables that have their own tablespace, so for it to have an effect, innodb_file_per_table must be enabled. The Barracuda file format is required for certain InnoDB features such ...


1

Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads are the values you need to monitor for buffer reads and disk read for the Buffer Pool. What you are looking for is a cache miss rate of 1% or less based on this Innodb_buffer_pool_reads X 100 / Innodb_buffer_pool_read_requests I have written posts like this before Feb 04, 2014 : Tuning ...


1

This is a complex topic which is very interesting - I/O, caching - i.e. what's really happening? Percona always have good stuff about every aspect of MySQL performance - check here. Mark Leith (a senior MySQL devlelopment manager) is worth a look here. Be sure to follow the links to Baron Schwartz's and Brendan Gregg's stuff, both big hitters in the MySQL ...


1

The InnoDB dictionary contains information about all tables in your database(s). The dictionary is a set of InnoDB tables hidden from a user. Looks like a records about example_example/table1 is missing in the dictionary. I'd explore content of SYS_INDEXES and SYS_TABLES to see if the table is in it. https://twindb.com/how-to-recover-innodb-dictionary/


1

With your initial feedback (you are using innodb), I can tell you that your innodb_buffer_pool_size is too small (2M), so most of your queries may be using disk instead of memory. As a rule of the thumb, for a dedicated server, the usual recommendation is reserving between 60-85% of the available memory for the innodb buffer. Increase it by setting it in ...


1

Regarding "Dynamic", the non-compressed Barracuda-only format, very little has changed from compact, mainly on how blobs (and any very dynamic fields) are stored. I have never had any issues with compact vs. dynamic, so I can safely recommend Barracuda's dynamic. Remember that Barracuda also supports old redundant and compact row formats. The article you ...


1

COMPACT is format supported by Antilope. It stores first 768 bytes of BLOB in case its value doesn't fit in page. DYNAMIC is almost the same as COMPACT except only 20 bytes for each BLOB field is used. Benefits - more BLOB fields are possible in a record. COMPRESSED is used for compressed tables. Hence its benefits.



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