This is more of a a design question

I have a local db (mysql if relevant) for reads and remote master for writes but wondering if it's better to..

1) Write changes to a local queue and bulk insert to remote master every 1 min or so

2) Write changes immediately to the remote master

Using 1 I [think] I have more control but there is the delay and it uses 1 connection/thread per outgoing table

but 2 could be any cpu/memory usage and no control, but changes are updated in realtime.

I currently use #1 but now as my project uses more tables I bulk insert 10+ connections and I am planning for horizontal scalability and 10+ connections each instance could eventually add up so please let me know what real/professional developerts/DBA's handle this as I am just in development stage with no actual [production] experience and just using common sense/logic currently.

  • How many "changes" per second? How far away (in milliseconds) is the "remote" master? See also High speed ingestion. – Rick James Jun 24 '16 at 20:05

It's fine to write to the master directly. A database does much more for you than just persist your changes to disk. It manages transaction, enforces integrity, etc. If you introduce a queue you introduce a new cog in the machine that can break and a lot of complexity that you need to manage for yourself and that is actually not required in most cases.

I worked with MySQL master servers having thousands of connections all the time and handling thousands of inserts/updates per second without problem. InnoDB is quite efficient if your tables are well designed and you have your configuration properly optimized. There are many ways how tables and my.cnf can be optimized for writes if that's your main concern. Some general tips:

  • Keep your innodb_log_file_size big enough to keep about an hour worth of changes.
  • If you can afford have a large enough server to keep the hot dataset in innodb_buffer_pool
  • In InnoDB don't use complex primary keys. For inserts AUTO_INCREMENT primary keys are the best.
  • Look into table partitioning if you have tables in the tens of gigabytes range.

(These are only the very basics there are much more your can do here)

You should worry about IO usage first because CPU and Memory are less likely to become the bottleneck. Keep an eye on IO utilization to identify when you're likely to hit the ceiling of what your system can do. If your writes are competing for locks it's possible you will experience high CPU usage but that's usually coming from mutex contention not "actual" CPU work. These can be remedied designing your tables and queries in such a way that they don't lock out each other.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.