Tag Info

Hot answers tagged

2

Foreign keys are not required in order to execute a JOIN. Foreign keys are restrictions that are there for security reasons (so that you cannot change the value of a column, or delete/insert a row ending up with an inconsistent state between tables). It is true that in most cases foreign key are used as JOIN matching conditions. The reasons why InnoDB is ...


2

Try making a temp table with just the data you need and doing a join to that. For each self join do another temp table. I would start with one at a time and check the performance.


2

Install percona-toolkit. Then you can use pt-online-schema-change. I usually use it when I have to alter tables. What it does is copy the data from your current table to another table. Triggers are automatically created, that copy incoming data to the copy while copying. This only works flawlessly if you don't have triggers on the table already. After that, ...


1

Unless your WordPress site is huge, you are unlikely to see much performance improvement in switching to TokuDB, if any at all. TokuDB's 20x performance advantage is for an indexed insertion workload, where the secondary indexes do not fit in memory. You will be using less space on disk with TokuDB's compression.


1

tagName column in table cardTags is not defined as primary key and you are declaring foreign key FOREIGN KEY (tagName) REFERENCES cardTags(tagName).


1

It appears to be a change in the optimizer. I haven't tracked down the exact setting that caused this yet but the explain on the 5.5 version was showing it using the Primary Key. On 5.6 It was using the secondary char1(8) index. Adding force index (primary) got it back to it's 3-5 minute count time.


1

I have four(4) old posts to demonstrate how to measure and size up the InnoDB Buffer Pool based on the amount of RAM on the DB Server and how much space is currently being taken up by data/index pages: Apr 14, 2011 : What are the main differences between InnoDB and MyISAM? Oct 22, 2012 : How much memory do I need for innodb buffer pool? Oct 22, 1012 : How ...


1

OBSERVATION #1 Take a look back at your top output Cpu(s): 84.0%us, 5.1%sy, 0.0%ni, 0.0%id, 0.0%wa, 4.5%hi, 5.0%si, 1.4%st See the last status variable 1.4%st ? What is that ? According to In Linux “top” command what are us, sy, ni, id, wa, hi, si and st (for CPU usage)? st, "steal time", is only relevant in virtualized environments. It ...


1

Improving performance is usually not a single stop solution. But looking at your conf and the results of mysql tuner you can do the following Increase innodb_buffer_pool_size by a LOT. That is usually quite critical for InnoDB tables. Further set the following two paramenters in you cnf file for InnoDB sort_buffer_size=2M join_buffer_size=2M You ...


1

Because you ran the mysql_upgrade on both master and slave, the ALTER TABLE was happening twice on slave side. The changes on master was propagated to slave, trying to add columns which you already added with manual mysql_upgrade. I don't think reset slave has anything to with the problem, unless you were looking to skip the entire block of statements done ...


1

You literally do not need a third party tool. STEP 01 Find out the path to the data by running mysql> SHOW GLOBAL VARIABLES LIKE 'datadir'; For this example, let's suppose it is C:\Program Files\MySQL\MySQL 5.6\data STEP 02 Pick a destination folder to copy the data directory For this example, let's pick D:\MySQLBackup STEP 03 Create batch file ...


1

No, rollbacks cannot be done in the background. Both that and the double write checks have to be done before accepting connections again (meaning that if you kill it, you will have to start again on restart). REDO process, however, can and is done in the background. What you can do is kill mysql (again) and restart it skipping that step: /etc/init.d/mysqld ...


1

The InnoDB buffer pool caches queries; if you have less usage on one of the nodes then you're going to have less cache used. If you're not properly load-balancing queries then the amount of cache in use on each node would be different. If you just turned on a node and added it to the cluster the amount of cache would be different than the others one that ...



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