Tag Info

New answers tagged

0

Many things can be done to speed things up... Use FLOAT or DECIMAL, not VARCHAR for latitude and longitude. (This is one of many things to shrink the record size.) Do not INDEX boolean values like clocked_in and public_post. (The optimizer is unlikely to ever use the index. And it is costly to update.) Don't split DATE and TIME; use DATETIME instead of ...


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


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


0

This answer has nothing to do with mysql database versions or types, I wanted to know if update statements were making changes AND to do this in my php code.. Created a dummy table with one record and one field which I would query to get the value of mysql's current_timestamp. To the data table being updated, added a timestamp field and used the mysql ...


0

I have addressed this once before : MySQL database drop insanely slow My suggestion in that post was this SET unique_checks = 0; SET foreign_key_checks = 0; SET GLOBAL innodb_stats_on_metadata = 0; DROP DATABASE db_madeintouch; SET GLOBAL innodb_stats_on_metadata = 1; SET foreign_key_checks = 1; SET unique_checks = 1; If all your data uses InnoDB, this ...


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


0

The number of cores should not be relevant. What else is different? MySQL version? Operating system brand? OS version? Filesystem (ext3, ext4, zfs, etc)? DROP DATABASE has to do a lot of filesystem work, so that is what I would suspect. Ditto for TRUNCATE. Why are you doing a lot of DROPs and TRUNCATEs?


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


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

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


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


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


0

Well.... KEY TOP_QUERIES_LAST_30DAYS_fk (siteId) is redundant with the PRIMARY KEY, so you may as well DROP it. INT UNSIGNED would get you to 4 billion, will that suffice? Consider changing filter to an ENUM. Do you have 1.75 billion rows? Or did you "burn" a lot of ids? If so, maybe we can fix that? For example REPLACE and certain flavors of INSERT ...


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


0

You have specified a cascade ON DELETE SET NULL, but the relevant key field is set to NOT NULL. You can fix this by either changing your cascade or setting the field to allow NULL values. Ref:- http://verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/


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


0

innodb_log_file_size -- Did you change that after running mysql? It cannot easily be changed. Recommend you set it back to what it was (or remove the line). The are many answers on the web to "How do I change innodb_log_file_size". "On startup, MySQL would not start if there was a mismatch between the value of the innodb_log_file_size configuration ...


0

Since it is a temp table, you are OK to just delete the files. rm -f *#sql-ib32694.* Is this harmful to InnoDB ? No. There wass a data dictionary entry still inside ibdata1. However, that entry is either missing or invalid (due to the tablespace_id). There is actually a one in 1,099,511,627,766 chance of it causing a problem. See my older posts on why it ...


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


0

Check out mydumper. It's a lot quicker than mysqldump: http://www.percona.com/blog/2014/03/10/new-mydumper-0-6-1-release-offers-several-performance-and-usability-features/ You can download from here: https://launchpad.net/mydumper


0

ANSWERS TO YOUR QUESTIONS 1) Since I have a Master/Slave structure, do I need to manage this and change the Slave also? Or any structural changes will directly affect the Slave? I would recommend you change the Master to all InnoDB and leave the Slave all MyISAM You will definitely need to make configuration changes since InnoDB has more a lot more ...


0

Move the #sql files. Usually you will find those files in the tmp directory. It is recommended to use pt-online-schema rather than direct alter to avoid locking. mysql> show variables like '%tmp%';


0

Go into the filesystem (DOS prompt, shell prompt, whatever) and delete (rm, whatever) any files starting with #sql Also check to see how much free space exists on disk. ALTER usually copies the entire table over.


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

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


0

Back in August 2014, I answered this question : How to start mysqld with read-only data directory? I mentioned how you could make the InnoDB Architecture completely read-only. You can also read MySQL Documentation (Configuring InnoDB for Read-Only Operation) The only reason ibdata1 would grow would be MVCC (needed to support transaction isolation). I ...


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


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


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


0

STEP #1 Add the option to my.cnf [mysqld] innodb_file_per_table = 1 STEP #2 If you are running MySQL 5.6, login to mysql and run mysql> SET GLOBAL innodb_file_per_table = 1; If you are not running MySQL 5.6, restart mysqld # service mysql restart STEP #3 Write a script to create NULL ALTER TABLE commands MYSQL_USER=root ...


0

I you can't or don't want to make "native" binary backups than mydumper is a good alternative. dumping a loading is much faster than regular mysqldump backups.


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



Top 50 recent answers are included