Tag Info

Hot answers tagged

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

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


2

From the manual here, you could try experimenting with your my.cnf settings - I know that I had this problem in the past and I eventually got around this by using [client] setting. # added from http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html # to allow for abrowse to load data! loose-local-infile = 1 local-infile = 1 Now, it was either ...


2

You're committing one of the cardinal (excuse the pun) sins of database schema design here - you're using the Entity-Attribute-Value model. Check out the writings of Joe Celko or Bill Karwin on this issue. It's also called the OTLT (One True Lookup Table) or MUCK (Massively Unified Code Key - there's a reason that particular acronym was coined!). You would ...


1

The right way to get MySQL status regarding recovery is monitoring its error log. There are 3 phases on InnoDB recovery: Checking partially written pages (very fast) REDOing committed and uncommitted transactions written to the transaction log. This can take some time, but its time is IO bound (as fast as reading/writing to the transaction log). If you are ...


1

Answer by Mat provided in a comment to the question: The second link isn't documentation. It's a feature request. It's someone requesting that bitmap indexes be added to MySQL. It's not something that exists, it will potentially exist in future versions.


1

I would code as follows: SELECT (some values) FROM mydb.mytable WHERE m_dt >= CURDATE() - INTERVAL 60 DAY + INTERVAL 0 SECOND AND m_dt <= CURDATE() - INTERVAL 60 DAY + INTERVAL 86399 SECOND; DELETE FROM mydb.mytable WHERE m_dt >= CURDATE() - INTERVAL 60 DAY + INTERVAL 0 SECOND AND m_dt <= CURDATE() - INTERVAL 60 DAY + INTERVAL ...


1

Please note the difference between the information_schema and performance_schema databases INFORMATION_SCHEMA The information_schema database is an inventory of all objects within the MySQL instance Such objects include: databases tables columns constraints indexes (called statistics) processlist locks I wrote a nice post about this 3 years ago : How ...


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

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

It looks like 'page swapping' is the problem here. When you deal with one table at a time, its indexes and parts of the table are loaded into the RAM. If you keep dealing with the same table, it will be fast as the table already exists in the ram. On the other hand, if you keep changing the table that you are dealing with, then the data in RAM has to be ...



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