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


1

I think mydumper/myloader is a good tool for operations like this: Is getting better every day. You can utilise your CPUs and can load data in parallelhttp://www.percona.com/blog/2014/03/10/new-mydumper-0-6-1-release-offers-several-performance-and-usability-features/ I have managed to load hundreds of gigabytes mysql tables in hours. Now, when it comes to ...


1

What you need is partial backups. innodb_file_per_table=ON is a prerequisite to make it work. In your case procedure would be like following: Take a partial backup from the production server innobackupex --databases="mydatabase" /path/to/backup On the destination server prepare the backup copy for export: innobackupex --apply-log --export ...


1

DISKSPACE FOR EVERYTHING INNODB SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) InnoDB_DiskSpace FROM information_schema.tables WHERE engine='InnoDB'; DISKSPACE FOR DATABASE mydb BY TABLE SELECT IFNULL(tbl,'Total') table_name, FORMAT(SUM(table_bytes)/POWER(1024,3),2) table_size FROM ( SELECT table_name ...


1

These files are related to the new FULLTEXT index support in InnoDB (starting with MySQL 5.6). Removing them is not a good idea for sure. Unlike with MyISAM, where FULLTEXT indexes are stored in the tables .MYI file together with all other indexes fulltext indexes in InnoDB are implemented using several internal helper tables which show up as separate .idb ...


1

First, have a look at the InnoDB Architecture (From Percona's CTO Vadim Tkachenko) When you update an indexed column, changes must migrate through this architecture as follows: From Insert Buffer section of the Buffer Pool to the Insert Buffer inside ibdata1 From Dirty Pages of the Buffer Pool to the Tables Physical File (.ibd file) If you are ...


1

Please look back at my answer again. Issue #1 might be your one and only problem. Why ? FACT #1 : 5199 Qcache_total_blocks allocated FACT #2 : 622 Qcache_free_blocks FACT #3 : 2269 Qcache_queries_in_cache FACT #4 : query_cache_limit is 268435456 (256M) FACT #5 : query_cache_min_res_unit is 4096 (4K) What can said here ? There are 2269 queries in the ...


1

Your issue may involve the configuration settings for either OS or MySQL HISTORICAL EXAMPLES Years ago when I worked at a website firm, I used --skip-extended-insert to load a DB Server that had 2GB RAM. That made a whole world of sense to me because the server I dumped it from had 64GB RAM and the client wanted the data on a smaller machine. At my ...


1

When there is a lot on insert and delete operations on a table, the table will be "fragmented", and the statistics that are used to decide the execution plan of a query will not be accurate. So YES, it is recommended to optimize the table from time to time (Not necessarily after each delete, but when a significant changes happen) hth


1

InnoDB log files are stored on disk and so consume disk space - http://dev.mysql.com/doc/refman/5.5/en/innodb-configuration.html. However the innodb_buffer_pool_size does impact memory usage. I find this a good reference for memory sizing - http://www.mysqlcalculator.com/


1

I have a script to move MySQL data from one version to another as long as it is MySQL 5.x If you look at my old post MySQL upgrade 5.0.88 to latest, I handle this situation going to a higher version. With slight modifications, I can make this process go backwards. STEP 01 : mysqldump all databases except mysql, information_schema, performance_schema ...



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