Hot answers tagged

14

You should definitely (in my opinion) not have 142 tables - it'll be a complete mess to name, index and maintain, and you'll generate yourself a lot of extra work if you some day add another category, if you need to move ads from one category to another, etc. Storing JSON blobs in the database will kill performance when you're performing searches, so I ...


4

I would heavily advise you not make 142 tables, it will be a nightmare. But don't necessarily use JSON, either. Instead, you should have one table containing categories, and another table containing the items. Link items to their respective categories by using a foreign key column. Your items table could have a category_id column, for example. Do not store ...


3

With merge replication, identity ranges must be allocated to the publisher and each subscriber. Identity range management can be either manual or automatic. As you already have found out, when a range is exhausted, a new range must be allocated. With automatic range management, the merge agent takes care of allocating a new range, with manual range ...


2

Have two instances of MySQL on that one server. Each has its own data directory, its own port (3307 is typically used for the second one), each has its own my.cnf and logs. But, since they share the RAM, you need to cut back many tunables relating to RAM. Why? What do you gain? Certainly not HA. You may be able to better use lots of Cores. But you are ...


2

Yes. A slave can (and should) offload SELECTs from the Master. If you outgrow one slave, you can have many. This gives you unlimited read scalability. Keep in mind that every write that happens on the Master must be replayed on every Slave. Using Row Based Replication helps make this less of a burden on the Slaves. But, eventually, you may become write ...


2

does anyone know if it's possible to use Change Tracking on a replication target? When you say replication target, do you mean subscriber ? Irrespective, of it, CT will work with replication. Things to consider when implementing Change Tracking (CT): Make sure you have retention & clean up of the data in the hidden CT tables. Enable CT on the ...


2

Was query_cache_type OFF (0) in my.cnf, not merely turned off later? (This seems to be critical for having it really off.) Another possible common thread in the bugs -- MyISAM. If you are using MyISAM, you really should convert to InnoDB. There are many reasons for converting. Here are some tips on doing the conversion: ...


2

WordPress WordPress was not designed with scaling in mind. (Caveat: What I say here is based on looking at innards several years ago; things may have changed.) There is no "read-write" separation, so scaling reads via Replication would be a major undertaking. Each "user" has his own database with 13+ tables. For large installations, this leads to OS ...


2

I see at least two options: if you don't need near-real-time replication, but just once a day (or maybe every hour) is enough for you, and the amount of data is small, you can quite simply create a script that will run in each remote location: connect to the local database do a dump of the local data (pg_dump --data-only should be fine) connect to the ...


2

From the details, it looks like the 'massive' difference is ~70MB in the dataplay schema. You can adjust your query to find out exactly which tables are different. Two potential reasons for this difference is: Schema difference on the master vs slave. Does the slave have utf8 instead of latin1 on a column or three? Does the slave have additional indexes? ...


2

When you setup a Slave by loading a mysqldump from a Master, there is a strong likelihood that many of the the new Slave's tables will be bigger than the Master because of the order BTREE indexes are being loaded as the corresponding tables are being loaded. Usually that order is dictated by PRIMARY KEYs. If you talking about InnoDB, data and PRIMARY KEY ...


2

Another option (similar to what happens in replication but more manual but less setup) is to do a FULL backup, restore over the network (with NORECOVERY) and continuously do Transactio Log backups with automatic restores. This would cut your downtime to only the last Tranlog Restore...


1

WAL Files are a record of all the transactions that have taken place .. but that's it - a delta between state before and state after, essentially. Which means you have to have a BASE to start off of before you can apply any WAL Files. If you clear out WAL archives without creating a new BASE, then you will be in trouble when you try to restore your ...


1

Which one was built first? How did you copy the data to the other one? Have you ever done OPTIMIZE TABLE or ALTER TABLE on one machine but not the other? ROW or STATEMENT based replication? Same indexes? I ask about the order of the dual-master because I would sort of expect the Master to be smaller. RBR vs SBR impacts how the inserts/updates are ...


1

Looking at you config, it seems you are missing log-slave-updates in your my.cnf files. You will need to add this in to both masters my.cnf files and restart them. This tells the slave database to take anything it receives from it's master, and copy from the relay log to the binary log so it can be replicated onwards. e.g. Master 1 Updates a table. It ...


1

I need the tables to be InnoDB and in sync. Those two statements crashing. Does it crash with a stack trace in the server logs? Could you share it here? I tried the above two commands on a 2-node MariaDB-5.5.47 cluster and it worked as expected. As @jkavalik suggested, you could do something like the following : Create a new INNODB table with the ...


1

There are 4 possible explanations mentioned in the error message, and a fifth one that isn't mentioned. the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or ...


1

Sounds like you were attempting to perform the delete operation in a single transaction. This being the case, the answer is "Yes, it will most likely break." Even without replication, it's a bad idea to do such a large operation in a single batch. You would be better off looping a smaller number of deletes - it's faster and less of a performance issue. ...


1

While the specifics will depend on the database system itself this could be handled with pretty much any of the RDBMS platforms out there. Permissions will pretty much handle what you want. The process that puts the data in would need to have insert permissions and all other access would be select only. As far as tracking the modifications - most of the ...


1

That slave info is in RAM. If you are running MySQL 5.6, run RESET SLAVE ALL; If you are running MySQL 5.5, restart MySQL If you are running MySQL 5.0/5.1, run the following: STOP SLAVE; CHANGE MASTER TO master_host=''; These are the ways to make the SHOW SLAVE STATUS\G disappear from view GIVE IT A TRY !!!


1

I finally managed to find a solution that works so far. I installed plpython procedural language module along with the psycopg2 module, wrote a FUNCTION and created two TRIGGERs, one for INSERT and one for DELETE (I won't have any UPDATE events). The function code would be something like this: CREATE OR REPLACE FUNCTION synchronize() RETURNS trigger AS $$ ...



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