1

I'm building an app that lets the user export their data. When they click the Export button, we need to do a SELECT * WHERE user_id =, save it to a CSV file, and download it. There may be hundreds of thousands of entries.

If the user edits their data while the export is in progress, the export can include the edits, or not. Either is fine, as long as it's a consistent snapshot taken at any point in time during the export.

The question is how do I do it without killing performance for all other queries? I don't mind if the export takes longer. Can I tell the database to somehow run this query at a low priority?

Assume a MySQL or Postgres database.

The only solution I know is to set up a read replica just for exports. Exports can slow down the replica, but won't slow down the primary, thus ensuring fast performance for other queries. Is there a better solution to this problem?

11
  • Reading the data from the database will impact the database server. There is no way to avoid that. Exporting such a small table shouldn't really be noticeable though if your server is capable enough. If that is a table that is frequently used, the data will most probably be taken from the cache anyway.
    – user1822
    Aug 17, 2022 at 6:09
  • 1
    The result of such a query will always be consistent (in a real database, not in MyISAM), no need for locking. Aug 17, 2022 at 6:53
  • 1
    Do you actually observe that the export negatively affects performance of other queries, or is it your speculation?
    – mustaccio
    Aug 17, 2022 at 11:32
  • 1
    "Hundreds of thousands of rows are small and will be satisfied from cache?" Depends. Are you running this on a Commodore 64?
    – jjanes
    Aug 18, 2022 at 18:47
  • 1
    I've done a lot of testing of MySQL on different servers, both physical, virtual machines, and cloud instances. A query that has a result set of a few hundred thousand rows will use some resources, but it shouldn't halt progress of other queries. I'm not disputing your observation, but I think something else is going on, because on any reasonably modern server, MySQL should be able to handle that with no problem. Aug 19, 2022 at 1:48

1 Answer 1

1

The comments above indicate that several of those who read your question are skeptical that a single query could cause such a problem on a healthy database server, even if the query has a large result set. The query of course would use quite a bit of I/O and network bandwidth to move that many rows, but MySQL should be able to handle it.

This makes me think something else is competing for resources, and when your query runs, it pushes the server over some threshold. I've seen this happen if the server is very old and not appropriate to the scale of data you are using.

Or it could be that the MySQL Server or some other process on the same server are overallocating resources (for example, RAM), and when your query runs, it exceeds a threshold so the server is overtaxed. I've seen this happen for example when the RAM usage of the MySQL Server increases to the point where it makes the operating system use swap space to supplement RAM.

You may need to investigate many operating system statistics while the query is running to spot it. I'd use top, iostat, or vmstat for example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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