Tag Info

Hot answers tagged

22

Please look at the Architecture of InnoDB (picture from Percona CTO Vadim Tkachenko) The rows you are deleting is being written into the undo logs. The file ibdata1 should be growing right now for the duration of the delete. According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace: Lots of Transactional Changes Very Long ...


20

I think we may have overcomplicated the answer that was in required in my case. I have no doubt that both Roland & Rick James are correct with their creation of a temporary table, injecting only rows that pass the filter NOT LIKE '-%' but the solution for me was "easier" because there was an important error I was unaware of until now and for that I ...


8

Roland's suggestion can be sped up some by doing both things at once: CREATE TABLE tablename_new LIKE tablename; ALTER TABLE tablename_new ENGINE = InnoDB; INSERT INTO tablename_new SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%' ORDER BY primary_key; RENAME TABLE tablename TO tablename_old, tablename_new TO tablename ; DROP TABLE ...


4

Each row in InnoDB (let assume COMPACT format) has these headers: Offsets - one or two byte per variable length type field. NULL flags - one bit per NULL-able field, aligned to a whole number of bytes. So called "Extra bytes" - a bunch of flags like is_deleted, pointer to next record ect. Five bytes in COMPACT format, fixed. Then comes primary key ...


3

The best way to migrate tables between servers is to do it in binary "native" format. Doing a serial logical dump (as mysqldump does) not only may take days on a very large database, but it will take even more for recovery. If you need to maintain availability on the source server for InnoDB tables, the best way is using an utility like MySQL Enterprise ...


3

The easiest solution is to simply not do that -- do a smaller delete, which can be more easily processed. In this case I would have recommended trying sequential deletes of the form: DELETE FROM `tablename` WHERE `columnname` LIKE '-a%'


2

My first instinct would be to do multiple, smaller deletes by limiting the number of query results, and running the query multiple times: DELETE FROM `tablename` WHERE `columnname` LIKE '-%' LIMIT 1000000


2

I echo the "bad form" comment of @JohnM - design the thing properly, and if you have new requirements (or your design isn't perfect first time - unlikely I know :-) ), then choose to add new fields. Use JSON if it suits your clearly demonstrated requirements, otherwise stick with "normal" field types. I've seen too many systems where these "spare fields" ...


2

You are too focused on the details; back off. Let's look at the big picture, the benchmarking, the indexing, the transactions, etc. How many simultaneous users are you benchmarking for? How many do you expect in reality? How many cores does your CPU(s) have? What version of MySQL are you running? My points are: (a) The benchmark is stressing the ...


2

Perhaps you should impose a SELECT FOR UPDATE SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE; UPDATE pics SET seen=1 WHERE id=:id LIMIT 1; I have mentioned this before Mar 08, 2013 : How are DB locks tied to connections and sessions? Mar 12, 2013 : How I prevent deadlock occurrence in my application? (See MECHANISM #3) UPDATE 2015-02-05 22:22 EST ...


2

Take a look at my answers to similar questions here, here and here. Basically, it's very simple - a database is a dynamic entity with components on disk, in RAM and in the CPU at the same time. There are all sorts of buffers and caches being filled, emptied and flipping between disk, RAM and CPU. InnoDB is an MVCC engine which allows backup software to ...


2

You may have moved around manually the tablespaces or improperly importing the tables physically. The error assertion is a detection by MySQL InnoDB that pages have internal values that do not correspond to their physical location, thus forcing the kill of the server to avoid further corruption. I have very very rarely seen that as a problem due to InnoDB ...


1

Software that uses MyISAM tables is often a sign that you are dealing with amateur quality software. There have been very few valid reasons to use MyISAM for several years, now... ACID and intelligent crash recovery and foreign key constraints are just a few things that come to mind that MyISAM will never dream of being able to provide. Turning off InnoDB ...


1

SHORT ANSWER Only as a last resort LONG ANSWER Having multiple indexes can be a rather arduous adventure for MySQL Query Optimizer. I have written about this before Sep 18, 2012 : How are multiple indexes used in a query by MySQL? Apr 19, 2014 : Optimizing indexes (Under the Heading ANSWER TO QUESTION #2) In essence, MySQL will do lookups along ...


1

If you are testing a and b, INDEX(a, b) is likely to be better. Indexing a flag (by itself) is almost never useful. Please provide SHOW CREATE TABLE and a few WHERE clauses; I will give specific advice. Here's a quick cookbook for building an INDEX that will often be optimal. Given a WHERE with a bunch of expressions connected by AND: List all the ...


1

You have three potential areas you could address: LIKE operators, subqueries, and OR operators. Regarding LIKE operators: See http://stackoverflow.com/questions/6142235/sql-like-vs-performance and http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx If your filter criteria uses equals = and the field is indexed, then most likely ...


1

If you are worried about table locks caused by DDL operations, like ALTER TABLE... ENGINE=InnoDB (what optimize does for InnodB) you must know that in 5.6, that process can be done fully online, and alternatively, and for lower versions, you can use an online alter table tool like pt-online-schema-change. With proper care, the process can be done fully ...


1

I have MySQL 5.6.21 on my laptop running Windows 8.1 and it is doing nothing. It still has that state. Please keep in mind that this state exists in the performance schema, in the table threads, in the column PROCESSLIST_INFO. Please note the MySQL Documentation on PERFORMANCE_SCHEMA.THREADS.PROCESSLIST_INFO The statement the thread is executing, or ...


1

You have a memory problem, something very typical in a memory-bound system, and that can be confirmed on the line: 150207 17:31:42 InnoDB: Fatal error: cannot allocate memory for the buffer pool As InnoDB cannot allocate memory for its buffer pool, it fails, and MySQL cannot start if the InnoDB engine fails (MySQL 5.5). Nothing on your my.cnf seems out ...


1

Maybe you could do something like this: Add a new field called deleted. Do an update like UPDATE tablename SET deleted=1 WHERE `columnname` LIKE '-a%'. Set cron to delete this at night time.


1

When it comes to InnoDB, you are right to be concerned. Here is a Pictorial Representation of InnoDB (Made by Percona CTO Vadim Tkachenko) The whole left side of the diagram represents InnoDB's moving parts in memory. The critical piece here is the InnoDB Buffer Pool The InnoDB Buffer Pool holds three things Data Pages : If a page is dirty, it is ...


1

Is it common to spread data of a single user operation across multiple rows? Think of the "entities": User, Review, Rating. A "user" may go to the restaurant many times, and give a "review" each time. The "review" may give ratings for multiple things, such as 'food' and 'location'. These are "Many-to-one" relationships, so Ratings has a review_id ...


1

The mysql_load() function calls the open_and_lock_tables() function to lock the table mentioned in the LOAD DATA statement. MySQL obtains an exclusive lock on the table so that it can very quickly load data into the table. There is very little overhead in the LOAD DATA process, just the bare minimum parsing is done to make it work. The CONCURRENT option ...



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