Tag Info

Hot answers tagged

4

MySQL's optimizer looks only at what indexes are available. There is an exception in 5.6: If you have a subqueries such as FROM ( SELECT ... ) JOIN ( SELECT ... ) ..., there are no indexes on the temporary tables that are created. This used to lead to terrible performance. Now, the optimizer will try out various indexes, and create the best one for the ...


4

You probably have lots of large transactions or one really big transaction. Take a look at Vadim Tkachenko's Pictorial Representation of InnoDB If you look inside the system table (ibdata1), you see rollback segments and undo logs. When a rollback segment rolls back a transaction, it has to use the MVCC information it stockpiled in the undo space. ...


4

The Rick James answer is good (I vote it up). You can try the following test. Create table and insert some values: create table table_x ( id int unsigned not NULL, status varchar(100) not NULL, index(id) ) engine=INNODB; insert into table_x values (123, 'PENDING'), (321, 'PENDING'); Then start two mysql sessions into two separated terminal. I ...


4

No problem. Use InnoDB. One of those queries will take out an "exclusive" lock on the row in question. The other query will try to take out such a lock, but be blocked until the first finishes (and COMMITs). Then it will see that there is no longer any row matching the WHERE clause, so it will say "0 rows matched, 0 rows updated".


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

Assuming that your dataset is larger than your buffer pool, having it at 95% usage is not only normal, but a desired state. You want as much information as possible on memory- hardware and resources are there to be used- so that both next reads and writes can be done faster than having to access disk. A different thing is if that memory usage is being used ...


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


2

Revised math: 8 bytes for BIGINT -- Do you really need more than 4 billion values? Consider INT UNSIGNED at only 4 bytes. 2 byte length of VARCHAR 764 bytes for 191 characters (potentially 4 bytes per utf8mb4 character) 2+764 < 767, so the VARCHAR column passes that test 8+2+764 < 3KB, so the entire index passes another test. (As would a pair of ...


2

You can avoid the whole problem by using this pattern. Old tables had id MEDIUMINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(id) New table has tenant_id SMALINT UNSIGNED, id INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(tenant_id, id), INDEX(id) Notes: INDEX(id) is sufficient to handle AUTO_INCREMENT. The ids for a given tenant will have a lot of gaps and ...


1

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


1

In almost every use case, InnoDB is preferred over MyISAM. So, yes. To make sure the indexes, etc are converted correctly, see if anything in MySQL to InnoDB checklist needs to be addressed. Note that key_buffer_size should be decreased and innodb_buffer_pool_size increased. In MyISAM, an UPDATE blocks all other operations on the table. In InnoDB, it ...


1

Yes and no. No, there is no way to apply multiple cores or servers to a single InnoDB transaction. Yes, you can build a cluster with InnoDB. But it is a Galera (PXC or MariaDB) Cluster, which means multiple servers replicating to each other. Overall throughput increases, but response time for individual queries won't. This could help "load", but not ...


1

In your case, your problem is RAM. Why? In my old post What are the main differences between InnoDB and MyISAM?, I discuss how each Storage Engine does caching. InnoDB has a Buffer Pool that cache data and index pages MyISAM does no dedicated caching of data, only indexes. Data get cached in OS RAM. Here is a picture from Vadim Tkechenko that shows what ...


1

If you have enough space, you can create a copy of the actual table and do the work on that: CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl; Then you can change the column as desired: ALTER TABLE tbl_name MODIFY COLUMN col_name BIGINT; Once the process is done, you can rename the tables: RENAME TABLE tbl_name TO new_tbl_name, tbl_name2 TO ...


1

I wrote a post 3 years ago that discusses this very subject : innodb_file_format Barracuda In terms of storage, using Antelope and Barracuda is possible. However, in my post, I discuss a residual effect on the InnoDB Buffer Pool. You need a larger Buffer Pool because Barracuda compressed pages in the Buffer Pool have to be decompressed to the read in the ...


1

Correct, skipping skips the transaction not the statement and that is why it is better to investigate the failure than blindly skipping. You're best placed to use pt-table-checksum and pt-table-sync to resolve drift after a skip since you will likely have diverged.


1

From your status lists, here is what I see 96GB Buffer Pool Buffer is 85.8345% Full (88477548544 / 1030792151040); Dirty Pages 2.699% of the whole buffer pool (2782134272 / 103079215104) 3.1445% of the data in the buffer pool (2782134272 / 88477548544); If there are so little dirty pages, why all the extra write activity ? First, look at InnoDB's ...


1

Possibly it is a non-problem. The buffer_pool waits until 90% (that's tunable) of it is filled with "dirty" blocks before it gets serious about flushing them to disk. Dirty pages come from writes (INSERT/UPDATE/DELETE/etc). I am unfamiliar with whether there will be a spike like you see. (There used to be a serious spike, before Percona fixed it; but ...


1

(I'm adding an "answer", not a comment for formatting reasons.) @akuzminsky covered most things quite thoroughly. Why a BIGINT? Won't an INT UNSIGNED (max value 4 billion) suffice? That would saver 4GB. The fill factor comes in two flavors -- If the writes are cleanly done, then the blocks are 15/16 full. Grand total might be 40-45GB If there is ...



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