Tag Info

Hot answers tagged

5

To counter the points directly: Drupal doesn't use them and gets along fine without them, so why should we? Drupal supports many database layers, perhaps at least one of those does not support FKs and they chose to stick with the lowest common feature set? A great many people do use them, the one data point where people aren't using them is relatively ...


4

YOUR QUERY SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506; At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506. REALITY CHECK No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them ...


4

A change in the 5.6.20 release notes: Redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. The 5.6.20 patch limits the size of redo log BLOB writes to 10% of the redo log file size. The 5.7.5 patch addresses the bug without imposing a limitation. For MySQL 5.5, the bug remains a known limitation. As a ...


4

Implementing this stuff at an app level is a nightmare. You and your team will have to test, double check and retest code which does EXACTLY the same thing that's been done by MySQL (for InnoDB) for MILLIONS of users over a period of YEARS. Follow the discussion (one of the best threads I've seen on stackoverflow) here. With all due respect to you and your ...


3

Please tell me you are not (and not even thinking) of using file system copying on a database server that's up and running? That is virtually guaranteed to lead to corruption of your copies, leaving your backups useless. You won't receive any errors - except, of course, when you try to restore :-( It's not very clear from your post (correct me if I'm ...


2

With innodb_flush_log_at_trx_commit = 0 there is not a guaranteed data loss (such an option would be nonsense), but a non-guarantee that data will not be lost in the last innodb_flush_log_at_timeout seconds (between fsyncs). To test how much data you are losing, you need to write several times per second and then kill mysqld just after some of those writes ...


2

1) means that if the parent is deleted, the child is also deleted (not a good idea IMHO - you should keep track of all data that's ever been in a database), although this can be done using triggers. You could also SET NULL. 2) means that if the parent primary key is changed, the child value will also change to reflect that - again IMHO, not a great idea. If ...


2

I would choose Option 2 If you use Option 1 and revoke privileges, you have to put them back. The mysql grant tables are MyISAM. Should any crash, human error, or other unexpected event corrupt the tables, you have a mess to clean up. Only those with SUPER privilege can perform writes when read_only is enabled. SUPER is not a database-level grant. With ...


2

Yes, it is safe. There is a tool http://www.lenzg.net/mylvmbackup/ that uses LVM snapshots to take backups of MySQL. From InnoDB's standpoint a backup copy taken with LVM snapshots looks like as InnoDB has suddenly crashed. When you start MySQL from the backup copy it will start crash recovery. To save some time it's better to do the crash recovery ...


2

@RolandMySQLDBA has given the right hint to answer the question. The problem seems to lie in the query and that for the results to be given back, each of those fields has to be read (somehow from the database). I dropped all indexes but the PRIMARY KEY, and inserted this new index: ALTER TABLE newbb_innopost ADD INDEX threadid_visible_dateline_index ...


2

While there are many reasons why you may want separate tablespaces, I will assume you are aware of all the disadvantages of having a single shared tablespace, specially in terms of administration (for example, how you can waste filesystem space when creating a temporary table on ALTER). You probably got innodb_file_per_table changed automatically if you ...


1

Here are my suspicions: mysqld is restarting for some reason, leaving the buffer pool cold on restart. This can be eliminated by SHOW GLOBAL STATUS (uptime column) and looking at the error log for restart information. Your buffer pool is set large, but your innodb_log_file_size is too low. I'm not sure of all the behavioral characteristics by ...


1

the deadlock message sometimes will not show the full picture. It could be some other query is holding the lock which is not any of those two showed in the log. As you said, you have delete query in the beginning, that properly would be the case. Here is a way I normally use to troubleshoot the deadlocks when you can not get all the info from the log. ...


1

That is 100% normal: what you are seeing is the background threads performing the following operations: Flushing dirty pages from the buffer pool into the tablespace files. You can see that is happening on your case due to the difference between your LSN and the latest checkpoint on the SHOW ENGINE INNODB STATUS. Merging non-unique secondary indexes ...


1

Most of the good tips has been given so far, but without lots of explanations for the best ones. I will give more details. First, delaying index creation is a good one, with enough details in other responses. I will not come back on it. A larger InnoDB log file will help you a lot (if you are using MySQL 5.6 as it is not possible to increase it in MySQL ...


1

For InnoDB and other transactional data stores it should be OK purely from a referential integrity point of view as the normal crash recovery procedure will roll out any incomplete transactions that were in progress when the backup was taken, but there are potential problems that mean it is not recommended that you backup databases this way: Any ...


1

If you are going to put MySQL in a wimpy environment (WIMP = Windows, IIS, MySQL, PHP/Perl/Python), your first priority needs to be RAM. Please get more RAM on the box.I suggest 32GB. As for MySQL, you need to tune InnoDB as much as possible. Here is the InnoDB Architecture InnoDB Architecture Recommendation #1 By default, 25% of the Buffer Pool is ...


1

Problem solved: It should be an unknown option as I've put it below [mysql] not [mysqld]. I put all the parameters below [mysqld] and I went through the process again and now it works just fine. ibd files are in their respective database folder.


1

this bug is reported in MySQL Forums (67179) and the solution is mentioned here. please note that you should do clean-up, before creating tables /* temporary fix for problem with windows installer for MySQL 5.6.10 on Windows 7 machines. I did the procedure on a clean installed MySql, and it worked for me, at least it stopped lines of innodb errors in ...


1

MyISAM [and] InnoDB data and log files are binary-compatible on all platforms having the same floating-point number format. Source. You can copy freely between those two platforms if you maintain the same vendor and version. Obviously, the binary executables for 64 bits will not work on 32-bit.


1

From the MySQL Documentation The type of read varies for selects in clauses like INSERT INTO ... SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT that do not specify FOR UPDATE or LOCK IN SHARE MODE: By default, InnoDB uses stronger locks and the SELECT part acts like READ COMMITTED, where each consistent read, even within the same ...


1

will you please show me the variable innodb_old_blocks_pct, innodb_old_blocks_time, innodb_flush_method, innodb_buffer_pool_instances and sync_binlog I also want to know what kind of business you are runing aginst this database;pls show me some representative SQL queries; do you have lots of insert on tables with auto_increment column, and pls show me the ...


1

There are at least two tools which attempt to mitigate the pain on online shema modifications - both by big hitters in the MySQL world. One is pt-online-schema-change from Percona and the other is oak-online-alter-table from Shlomi Noach. Could depend on whether you're more at ease with perl (percona) or python (Noach). Needless to say, I urge you to test ...


1

You have a read/write ratio of 26:1 (based on 10869877 divided by 406010), which is 96% reads. I think you might be suffering from what I call READ AHEAD FUTILITY. Sounds like I made that up. Well, you are right. I did. What do I mean ? According to the MySQL Documentation on the status variable Innodb_buffer_pool_read_ahead_evicted The number of pages ...


1

These two are actions to be performed, respectively, when the referenced record on the parent table changes its id and when it gets deleted. If you execute: UPDATE parent SET id = -1 WHERE id = 1; And there is at least one record on child with parent_id = 1, 1) will fail; in cases 2) and 3), all records with parent_id = 1 are updated to parent_id = -1. ...


1

Removing foreign keys does not damage data because you are doing DDL to the indexes. Once you do that, data integrity (even for existing data) going down the road needs its integrity tested. EXAMPLE create table parent ( id int not null auto_increment, ... primary key (id) ); create table child ( id int not null auto_increment, fk_id ...


1

Actually I guess the answer is in your boss's words: 3.He's removed them from existing tables to change things and it's caused data corruption that was only noticeable weeks or months later, on high-traffic/ high activity sites, so he'd rather not use them. FKs removal does not change data, yet, if someone runs queries against the DB, like your boss did, ...



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