Tag Info

New answers tagged

0

It really depends on the read-write split on your data in general. If its read heavy, then you save CPU load in RDS by smartly using query_cache. However, I would never recommend turning it to default to ON, but rather use DEMAND (number 2 in RDS parameter) that way you can use SQL_CACHE in your select statements for particularly cumbersome queries to ...


2

This issue was well dealt with in this post by Yves Trudeau who seems to suggest that it is safe - his conclusion is that Conclusion Like ZFS, ext4 can be transactional and replacing the InnoDB double write buffer with the file system transaction journal yield a 55% increase in performance for write intensive workload. Performance gains are ...


1

The only situation I can think of is reloading a large mysqldump. Why ? Check out this Pictorial Representation of InnoDB (Percona CTO Vadim Tkachenko) From the picture, you can see that the InnoDB Buffer Pool writes dirty pages to Log Buffer Insert Buffer in ibdata1 Double Write Buffer in ibdata1 .ibd file for each InnoDB table Shutting off the ...


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


0

You found a (known) limitation of the optimizer of MySQL. The problem with DELETE FROM table WHERE column IN (SELECT ...) is that the subquery (SELECT) is executed for each row of the table that has a different value for column. In your case, as it is a PRIMARY KEY, it is executed a million times. More details in ...


0

The application now is stable again. As a first step the innodb_thread_concurrency was reduced 2 * number of cores. That seems to reduce the problem slightly. But in the end the statement that the Magento-Shop has issued has been replace by a simpler statement that is adequate for the special situation of the concrete shop. The shop uses only one ...


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.


0

Another alternative would be to add a column which stored the time of the last successful lock and then anything else that wanted to lock the row would need to wait until it was either cleared or 5 minutes (or whatever) had elapsed. Something like... Schema id (int) name (varchar50) status (enum 'pending', 'working', 'complete') created (datetime) updated ...


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


0

This appears to be a regression bug that exists in Percona Server 5.6.14 and possibly some previous versions of 5.6. https://bugs.launchpad.net/percona-server/+bug/1247021 It appears it was fixed in Percona Server 5.6.16-64.0: Due to a regression in the buffer pool mutex split, a combination of InnoDB compression, write workload, and multiple active ...


0

You can and will be better served by removing created_by from your primary key and creating a separate index for it. This will allow those queries which use created_by in the where e.g. where created_by = ?, to be able to use that index to service the query. There may be a slight, and probably not noticeable, performance hit from managing the primary key and ...


0

Unfortunately, you have to let InnoDB purge all its moving parts (Redo Logs, Undo Logs, Dirty Pages, Index Changes, Log Buffer Contents, etc) Going forward, I recommend the following : Set innodb_max_dirty_pages_pct = 0 By default, innodb_max_dirty_pages_pct is 75. If you set it to 0, it forces the InnoDB Storage Engine to flush every dirty page and its ...


0

Try to turn off Query Cache, which might cause extra lock.


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.


0

As some days passed, I've checked my queries and got my mistake: Wrong use of User-Defined Variables As this: @position+1 give me always 1 because it's not an increasing value, it's just the same variable +1... in order to make it an increasing value it must be assigned the new increased value: @position := @position+1 SET @position := 0; INSERT INTO ...


0

You may need to try something a little unorthodox. Let's say you have the following scenario: Database is mydb table is mygianttable datadir is /var/lib/mysql the physical file is /var/lib/mysql/mydb/mygianttable.ibd You want to drop mydb.mygianttable I found PalominoDB's How to recreate an InnoDB table after the tablespace has been removed. Based on ...


0

Use pt-archiver to delete all the rows from the table first, in batches. This will take much more time but it will not block other operations. Once the tables have no rows (they are empty) and the rows have been purged, you can delete the tables, which should only take a microseconds lock. What you are suffering is a full buffer pool global lock. You can ...


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

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


0

You need to tweek a few things. STEP 01 Simply add these to my.cnf under the [mysqld] group header [mysqld] max_allowed_packet = 1G innodb_log_file_size = 1G innodb_log_buffer_size = 64M STEP 02 Login to MySQL and run this mysql> SET GLOBAL index_fast_shutdown = 0; STEP 03 service mysql restart Since you are running MySQL 5.6, the log files ...


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

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

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


0

Create Auto Increment Position Column when you Create product TABLE EXAMPLE Create Table category_product ( id_category int, id_product int, position int Auto_Increment=1 ) Then Simple Insert Insert into category_product select 2,id_product from Product ...


0

This seems to be a bug that was introduced in MySQL 5.6.10 and has since been fixed (although there is no bug report on http://bugs.mysql.com that I can find). The behaviour described in the question does not occur in the latest version of MySQL. Observed behaviour in MySQL 5.7.5-m15: mysql> CREATE TABLE `Test` ( -> `id` int(10) auto_increment ...


0

If your database is already slow, running batch inserts may decrease your performance further. Especially in InnoDB where each write operation is recorded in transaction logs. Suggest that you first tune your system and/or upgrade hardware before taking the benefit of batch insertions in InnoDB.


0

Percona's Vadim Tkachenko made this fine Pictorial Representation of InnoDB You definitely need to change the following innodb_buffer_pool_size = 4G innodb_log_buffer_size = 256M innodb_log_file_size = 1G innodb_io_write_threads = 16 innodb_flush_log_at_trx_commit = 0 Why these settings ? innodb_buffer_pool_size will cache frequently read data ...


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

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


0

You cannot move just indexes to another location. You have to move both data and indexes. When you use innodb_file_per_table, this will create a file with the extension .ibd. For example, if you have an InnoDB table as follows datadir /var/lib/mysql database mydb table mytable The physical files are the following /var/lib/mysql/mydb/mytables.frm ...



Top 50 recent answers are included