Tag Info

Hot answers tagged

3

Background info... The "torn page" problem occurs when part of an InnoDB block is written to disk, but the physical write died before writing all the low level (usually 512-byte) blocks. This leads to an unreadable block for InnoDB. The double-write buffer, and its extra write, makes it possible to recover from a torn page. The hardware needs to ...


3

When you use a column from a table on the "wrong"* side of an OUTER join, the join usually becomes equivalent to an INNER join. * "wrong": right side of a LEFT join and left side of a RIGHT join. So, the solution is usually to move the condition from the WHERE to the ON of the outer join. Like this: SELECT cre.id, rer.idChambre -- ...


2

First, let's look at a different aspect. Is your table ENGINE=InnoDB? If so, and you have not done anything (yet) about "transactions"? Then 5 Updates is 5 transactions. There is a significant overhead for each transactions. Hence, this would be a lot better: BEGIN; UPDATE ...; UPDATE ...; ... COMMIT; You had 5 statements versus 3. I turned the 5 ...


2

I wish add something to the answer of Rolando. From Mysql 5.6 it is possible to put out undo logs away from ib_data1. I have tried the following experiment. On a fresh installation I have put undo log out of system tablespace (ibdata1) and created a lot of tables to check how ibdata1 grows. Then I created a big table and opened a transaction in one ...


1

In addition to what Derek says, kill 119634; -- Since it is in Sleep mode, this will disconnect the client. That, in turn, terminates the runaway transaction. look in your code for autocommit=0. Don't do transactions that way; it leads to the problem you are experiencing. (No, I can't be sure this is the cause this time.)


1

You are right that the transaction is 'running' for several hours. This basically means you have a query that has failed to run 'commit' and is holding the transaction open. If you are running in the default REPEATABLE READ transaction isolation level, you will also see your 'history list length' growing large in the TRANSACTION section of the SHOW ENGINE ...


1

InnoDB makes it impossible to just cut and paste a database because each .ibd files has tablespace_id that are referenced in the data dictionary within c:/ProgramData/Mysql/Mysql Server 5.5/data/ibdata1. Here is a Pictorial Representation of InnoDB (from Percona CTO Vadim Tkachenko) You cannot recover just a database. You must restore the entire data ...


1

I'll bet that you are currently I/O bound. This means that nothing can speed it up. (And Rolando's suggestions may be futile.) Let's look deeper. Is this LOAD a recurring task? If so, how often? Is everything blocked waiting for table to be reloaded? Simple solution: Load into a different table, then do a double RENAME TABLE to swap it in. Only ...


1

BEGIN or START TRANSACTION initiates a transaction. It is ended by COMMIT or ROLLBACK in the same connection. SET TRANSACTION provides optional attributes for the transaction; it is not often used. If you need a long-running transaction, or something that spans multiple web page invocations, you need to do it some other way. (This is probably a common ...


1

According to this, your problem is not with VARCHAR fields. However: SELECT id, LENGTH(field1)+LENGTH(field2)...+LENGTH(field_n) AS total_length WHERE total_length > 8126 Can be done using 2 queries for each field. (This can be done in a complex query if you want to use INFORMATION_SCHEMA): SELECT MAX(LENGTH(field1)) INTO @max_length1 FROM table_name; ...


1

Someone make this bench: http://mimdl.tumblr.com/post/115308901909/benchmarking-handlersocket-mysql-innodb-memcached Get Operation with HandlerSocket was completed in 1.20499610901 Sec, i.e; making 8,299 QPS (Queries per second) Get Operation with InnoDB Memcached Plugin was completed in .93115 seconds i.e; making 10,739 QPS Select Query for MySQL took ...


1

InnoDB Architecture (from Percona CTO Vadim Tkachenko) What are the values recommended for the batch insert size, considering the size of the columns (longtext, longblob)? Increase innodb_log_buffer_size to 256M Increase innodb_log_file_size to 2G I wrote about increasing these to accommodate TEXT/BLOB fields in the past Apr 20, 2011 : MySQL ...


1

phpMyAdmin will only do an actual select count(*) if the estimated number of rows are within the set threshold (default is 500k depending on your version): $cfg['MaxExactCount'] For InnoDB tables, determines for how large tables phpMyAdmin should get the exact row count using SELECT COUNT. If the approximate row count as returned by SHOW TABLE ...



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