Tag Info

Hot answers tagged

3

InnoDB isolation levels are not "bad" or "good", it depends on what your application requires in terms of isolation. In your example, by using repeatable read, you make sure that once the first select has been done, no other transaction can modify the state of the database, from the point of view of that transaction. In other words, it is as if you had run ...


2

Is the default setting for Sql Server comparable to "innodb_flush_log_at_trx_commit = 1" or to "innodb_flush_log_at_trx_commit = 2"? No. Assuming similar hardware, SQL Server should be just as slow as MySQL, since both would have to flush the commit for every row. Therefore there must be something else at play, very likely in the (not posted) C# code ...


1

With innodb_file_per_table configured, the network_type table will exist in a physical file under the DB folder. For example, let's say your table has the following attributes: Table name somedb.network_type datadir is /var/lib/mysql You will find the physical file /var/lib/mysql/somedb/network_type.ibd That file is the home of all data and index pages ...


1

First of all, here is a Pictorial Representation of InnoDB (courtesy of Percona CTO Vadim Tkachenko) Strictly in terms of InnoDB, there is only lose data on power failure if certain settings as disabled. There are two parts of the InnoDB Storage that, if disabled, can cause data loss on power failure. Double Write Buffer A record of all data and index ...


1

I did it the easy way of starting a Windows MySQL 5 instance and coping the data files over. I was able to mysqldump to get what I needed.


1

You should use SELECT ... LOCK IN SHARE MODE. Why ? SELECT ... LOCK IN SHARE MODE sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and ...


1

This is an old verified bug in MySQL for InnoDB on larger tables. As this has been patched quite some time ago, perhaps it's not the same situation, but it sure seems similar. The workarounds for the older versions are: Defragment in the filesystem. When you extend many tables the chunks for each end up very fragmented, so the defragmenting can be of ...


1

You should use Percona Toolkit to backup database to prevent from blocking database: sudo innobackupex --user=root --password=rootPASSWORD --host localhost /tmp/ sudo innobackupex --apply-log --use-memory=2G /tmp/$TIMESTAMP/ You need enough disk space in /tmp. Once you finish it, you can copy entire directory to another server. In xtrabackup_binlog_info, ...


1

You need to use Percona XtraBackup tool. It works like a charm for huge datasets and doesn't interrupt MySQL operations. http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/creating_a_backup_ibk.html There are some tricks but it's worth it.


1

try the following command this one since the database size is not small: mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.gz


1

You must use --single-transaction option mysqldump -uroot -p --single-transaction databasename > dump.sql It allows for point-in-time snapshot of data. Once mysqldump starts, all the InnoDB tables will be frozen in time. Suppose you start the mysqldump at 2:30 PM and it finishes at 3:00 PM. All the InnoDB tables dumped will be from 2:30 PM. All other ...


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

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



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