New answers tagged

0

I have found the solution. There is a variable called innodb_print_all_deadlocks which logs all the deadlocks in to the error log.


0

My understanding: The table contains 1M rows of which 250k are returned by the query. There are 500k rows with foreign_key_id = 1 and 500k rows with af.foreign_key_id2 IS NOT NULL. The query using full table scan (actually doing full index scan on the PRIMARY key in InnoDB) will read all 1M rows sequentially and check each of them for the conditions. The ...


0

After a long experimentation, I've found the problem to be the infamous swap insanity. The problem has been fixed by using the innodb-numa-interleave option.


0

I'll disagree with the assessment that "you are lucky enough to have a buffer pool with a perfect 100% hit rate" At the top of the output (which is chopped off), is a line something like: Per second averages calculated from the last 16 seconds This says to me that no reads happened in the last 16 seconds, thereby (artificially) giving you a perfect ...


1

The InnoDB/XtraDB is a table engine, it has no say in parsing SQL. Thats another layer, which should be mostly engine independent. MariaDB 5.5 is supposed to be 99.99% SQL compatible with MySQL 5.5 (there were some minor hiccups) and MariaDB 10.x is backward compatible with 5.5. With MariaDB 10 and MySQL 5.6 (and now 5.7) the paths diverged somehow - some ...


2

We have millions of tables deployed running ARIA in production. It is definitely a better choice over MyISAM as far as crashing and recovering from a crashed table. If you have high concurrent write neither are a option as they both only support table level locks. For tables with more write intensive operations stick with InnoDB, and with heavy write tables ...


1

EXPLAIN tells you the difference. If it shows DEPENDENT SUBQUERY it means that the subquery in IN() is executed once-per-row for the table1, that may be really many times if the table is big. Different MySQL versions may apply different optimizations to the first query to get rid of the DEPENDENT SUBQUERY or at least minimize its performance impact: use ...


1

You just asked Does: INSERT INTO table1... SELECT .. FROM table2 Also create a lock on table2? Yes, it does create a lock on table2. I wrote about this behavior back on Aug 08, 2014 (See my answer to MySQL consistent nonlocking reads vs. INSERT ... SELECT) In my old post, I mentioned from the MySQL Documentation: By default, InnoDB uses ...


0

You have to look in my.cnf for the variable innodb_parallel_doublewrite_path You should also set this [mysqld] innodb_doublewrite=0 Then, restart mysql After restarting mysql, you can then delete /test/mysql/xb_doublewrite CAVEAT What is the purpose of this feature ? According to Percona Server 5.7 performance improvements Parallel doublewrite ...


2

Tip 1: PRIMARY KEY(x, id), INDEX(id) -- where `id` is `AUTO_INCREMENT` is perfectly valid. It has the performance advantage of being more efficient when many queries need to find multiple rows WHERE x = 123. That is, it is slightly more efficient than the 'obvious' PRIMARY KEY(id), INDEX(x, id) The only rule about AUTO_INCREMENT (for InnoDB) is that ...


0

The above solution was not working on MariaDB 10 for me. The ibd files were not removed. Our server has over a million tables in it so going using INFORMATION_SCHEMA.TABLES is pretty slow. You can use INFORMATION_SCHEMA.INNODB_SYS_DATAFILES to rip out just the ibd table spaces To convert only the InnoDB tables that are actually in separate files use the ...


1

It could be related to the value of foreign_key_checks variable. Try the following statement, and check the value: mysql> show variables like 'foreign_key_checks'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | foreign_key_checks | ON | +--------------------+-------+


1

Found a solution that works MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH LIKE '%5377%'; +---------+---------------------------------+ | SPACE | PATH | +---------+---------------------------------+ | 2432544 | ./db5377/menu_router.ibd | +---------+---------------------------------+ 1 row ...


3

No. If your table has the InnoDB engine and the PRIMARY KEY is (pet_id), then defining a secondary index as (person_id) or (person_id, pet_id) makes no difference. The index includes the pet_id column as well so values are sorted as (person_id, pet_id) in both cases. A query like the one you have: SELECT pet_id FROM yourtable WHERE person_id = 127 ORDER ...


2

According to the MySQL Documentation on the Clustered and Secondary Indexes How Secondary Indexes Relate to the Clustered Index All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. ...


0

What you are asking for can lead to unpredictable results in certain situations. What kind of situations? Running this on a Master and the Slave receives the same SQL. The problem I see is that rows of data in a Master may not be physically written in the same order on the Slave. Triggers will encounter issue (or really create issues) with this type of ...


2

Just going by the MySQL Documentation, I would say yes. According to the MySQL 5.6 Documentation on innodb_adaptive_hash_index Disabling the adaptive hash index empties the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that were using the hash table access the index B-trees directly ...


2

Don't randomly add indexes. Look at your queries to decide which indexes are needed. See my cookbook. InnoDB really needs a PRIMARY KEY. Keep in mind that a PK is, an index, is UNIQUE, and is clustered. So don't add any index(es) that start with the same column(s). WHERE a=2 AND b=4 begs for a "composite" index: INDEX(a,b) or INDEX(b,a). Those are ...


2

For a table with 1000 rows I think it does not make sense to put any index. The complete table could fit into memory and that would be fast enough. But if you want to put indexes you should always take the business value into consideration. Take into account that each index generates an overhead to the table. Not sure where the limit is, but in any case ...


1

The short answer is no, MySQL does not have that 'feature'. The long answer (and opinion)... Perhaps 1% of tables are partitioned. Perhaps 1% of partitioned tables could use that feature. Now imagine the effort to implement, test, and deploy such an obscure feature. Oracle has had several decades to incorporate little-used features like that. MySQL is ...


0

Your problem is very simple INDEX DIRECTORY is not supported for InnoDB, only MyISAM create table InnoDB_Test(a INT) DATA DIRECTORY='/var/log/storage/MySQL' engine=InnoDB; is all you need UPDATE 2016-04-15 07:02 EDT You just said in your comment Thanks Ronaldo for your answer. I try with removing INDEX DIRECTORY option but it is not working. Even ...


1

What you did applies to tables one at a time. Instead... Plan A: Change datadir when installing. (See documentation for details and caveats.) Plan B: Symlink .../mysql/data to some place in the other partition. (And remove DATA_DIRECTORY, etc from CREATE TABLE.)


1

(This does not address your question directly; rather it addresses your goal of deleting old records.) Plan A You are talking about DELETEing "old" records. Assuming you are removing "all" the old records, then PARTITIONing is an excellent way to do that. PARTITION BY RANGE(TO_DAYS) by weeks. Use DROP PARTITION (instantaneous and non-invasive) to get ...


0

Thanks for your answer. I have checked the connections on both servers. But the results are strange. On the Master DB server there are more connections than on the slave: Master netstat | grep TIME_WAIT | wc -l 148 htop mysql VIRT 44G RES 34,7G Slave netstat | grep TIME_WAIT | wc -l 78 htop mysql VIRT 174G RES 68G


0

You should not be using MySQL 5.6.20 at all. Why ? I wrote this post back on Nov 14, 2014 : Find MySQL Patch Level where I had just found out about Oracle's Security Patch. My company had upgraded to 5.6.20. To our horror, the security patch then came out of nowhere. We had to redeploy MySQL Installation in puppet to upgrade to 5.6.21. I still need a ...


1

Under MySQL 5.7, ROW_FORMAT=FIXED is no longer an option for the InnoDB Storage Engine. According to the MySQL 5.7 Documentation for innodb_default_row_format, these are values you can use for the table's row format: DYNAMIC, COMPACT, and REDUNDANT are allowed as default values COMPRESSED is not allowed to a default value, but you can explicitly use ...


1

Since MySQL 5.6, MySQL may automatically convert IN-subqueries into a JOIN query. This is called semi-join transformation. By converting the subquery to a join, the MySQL optimizer may be able to process the tables in a different order than for traditional subquery execution. For your query, the amount of data that need to be accessed, will be much less ...



Top 50 recent answers are included