Tag Info

Hot answers tagged

5

You could also solve this with a trigger that populates the copy of the table on insert/update/delete. It wasn't clear in the question that these tables are actually on different servers, and that the subscriber was unreliable. In that case you could simply log ship to the subscriber - you can get pretty close to real time here, though you will have to kick ...


3

Since replication isn't normally used on slave even though it's running That statement doesn't quite make sense. In master/master, both servers are each other's master and slave and there is no real distinction between them -- only an administrative distinction, as you seem to understand. If set up correctly, then yes, SET GLOBAL READ_ONLY = 0; on the ...


3

Instead of doing TRUNCATE TABLE (which locks up any connections accessing the table), try making an empty copy of the table, swapping it in, and dropping the old table. EXAMPLE Suppose the table is called mydb.mytable. Do it like this USE mydb CREATE TABLE mytable_new LIKE mytable; ALTER TABLE mytable RENAME mytable_old; ALTER TABLE mytable_new RENAME ...


2

I've been looking into reclaiming disk space this morning. I'd like to add that as of version 2.6 Mongo permits use of repairDatabase on the secondaries. So the process of recovery is to simply execute db.repairDatabase() on the offending databases.


2

Sorry for waking up this dead thread, but I hate it when questions remains inconclusive. Anyway, I had the same problem. Apparently SQL server replication saves foreign keys in "dbo.MSsavedforeignkeys", which is where I found my blocker FK. A quick delete dbo.MSsavedforeignkeys where constraint_name = N'FK_TableOnlyInDestDB_MyReplicatedTable' solved my ...


2

The safest approach is to stay far, far away from trying to filter replication... and tweaking sql_log_bin on and off is a recipe for replication problems, too. In a replicated environment, the general idea is that both servers have identical data sets, and to whatever extent they don't, your setup becomes exponentially more complex. There is an easier and ...


2

Although TRUNCATE TABLE is definitely faster than DELETE FROM I would stick to deleting the records in small chunks. The TRUNCATE TABLE sometimes can be still slow because a lot of stuff is going on behind scenes: it has to grab exclusive lock on the dictionary, it still has to delete ibd file and re-create one, it has to evict pages from the buffer pool. ...


2

Use TRUNCATE TABLE (that will empty the table in the fastest way possible, by droping it and recreate it in a non-rollable-back way. If that takes too much time for you (can happen in older versions of mysql using innodb_file_per_table), you can run it independently on master and each slave with SET sql_log_bin = 0; The underlying bug is probably this ...


1

I still don't really know how to see what exactly the slave is spending time on, but I was able to let the slave catch up. The problem was the action of deleting old rows with DELETE FROM table WHERE timestamp < x ORDER BY y LIMIT z. In MIXED mode, that is logged as ROW, meaning z amount of rows. I now delete with: SET SESSION log_warnings=0; SET ...


1

My first thought was to restore the backup. At 1TB this becomes impractical, however. Log shipping would not allow you to write to the pre-prod instance either. I think it should be possible to engineer something around table partitioning - assign each partition to its own filegroup, perform backups after the nightly batch and restore that to pre-prod in ...


1

Transaction replication would be the solution I would choose for this. More info about transaction replication can be found here: http://msdn.microsoft.com/en-us/library/ms151176.aspx To only choose the columns needed, you need to use a column filter: http://msdn.microsoft.com/en-us/library/ms151775.aspx AlwaysOn availability groups would not work ...


1

What you're seeing is completely unacceptable, and developers should not be expected to work around it. You've suppressed it in your answer, and I don't know if you didn't notice it, or didn't consider it significant, or something is broken in your setup. Here's what you should have seen: mysql> insert into t1 values (uuid_short()); Query OK, 1 row ...



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