Reputation
Top tag
Next privilege 200 Rep.
See reduced ads
Badges
5
Newest
 Commentator
Impact
~17k people reached

  • 0 posts edited
  • 0 helpful flags
  • 3 votes cast
Feb
17
awarded  Commentator
Feb
17
comment What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?
None, other than as a satisfied user.
Jan
8
comment Efficient manual cleanup of foreign keys in MySQL
Becasue it's slow and expensive, I only run it occasionally (hourly).
Jan
8
comment Efficient manual cleanup of foreign keys in MySQL
I don't know why, but finding the rows to delete seems to take a long time (i.e. a select with the same criteria takes about the same amount of time as a delete), even though they are indexed. Some tables contain tens of millions of records.
Jan
8
comment Efficient manual cleanup of foreign keys in MySQL
Using a transaction presents similar problems to the FKs - it will lock records in both tables simultaneously, instead of one at a time. I don't know about you, but I can't afford to have my app lock up for 10 minutes at a time...
Jan
8
comment Efficient manual cleanup of foreign keys in MySQL
@ypercube Locks due to FKs are a very common issue (it's part of the tradeoff that FK's bring), and this approach to dealing with it is very common too, much like denormalising. It's done as a cleanup because that helps maintain code separation (avoids having one model deleting records from another's table), but it's not a bad idea to do as you suggest.
Jan
8
comment Efficient manual cleanup of foreign keys in MySQL
@BateTech I'm not blindly deleting anything - I removed the FK specifically to avoid a very common locking situation, and removing them solved that problem, but made this one. I have appropriate indices on these tables, as stated in the Q. I can't really look for a smaller set since it's the looking for them that's the problem, not the finding of them (i.e. LIMIT would make no difference) - if there are any matches there are very probably only a few.
Jan
8
asked Efficient manual cleanup of foreign keys in MySQL
Mar
15
comment Install/bootstrap mysql without name-based users
hostname comes back with hostname.localdomain, but resolveip says "Unable to find hostid for 'hostname.localdomain': host not found". I guess it could do with a line in the hosts file to let that resolve, but I don't see why it's even looking up the name in the first place.
Mar
10
asked Install/bootstrap mysql without name-based users
Feb
21
awarded  Student
Feb
21
asked Upgrading from MySQL 5.5 to 5.6 - timestamp issue?
Jun
15
comment Update rows with a range of dates
There is an autoincrement column, but it doesn't increment by one because the database is part of a replicated cluster. Within a given set of records, IDs should be consistent and consecutive. It might be possible to do arithmetic using the IDs, but it will be a little fragile as offsets will vary according to which server runs it, though that value can be obtained dynamically.
Jun
13
comment Update rows with a range of dates
Yes I have the row count on hand. One optimisation for the naive case would be to group row updates that have the same timestamp - in the 10k/hour scenario that would reduce the query count by about 60%.
Jun
10
comment Update rows with a range of dates
Within a particular batch, the order doesn't matter, so long as they are evenly spread out over the desired duration - it's a kind of rate-limiter that has the ability to catch up should it fall behind. Any given batch may contain more or less than 3600 records and the rate may be variable between say 1000 and 2 million/hour.
Jun
10
asked Update rows with a range of dates
Sep
26
awarded  Teacher
Sep
26
awarded  Supporter
Sep
26
answered What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?
Sep
20
awarded  Autobiographer