4

I have a big MySQL database which I frequently access from a number of other servers. This puts a heavy load on the database, making the database access the main bottleneck in my application's performance. I want to improve this, by caching some of the more common read queries separately. My first thought was just to have a slave database updating itself from the master, but the problem is that while most of the reads can be cached, some of them need to be up to the minute accurate and can not.

Is there some tool out there, that I can put between my server and the database, and say "queries X, Y, Z are to be cached, but not anything else"?

Improve this question
2

1 Answer 1

Reset to default
1

Materialized Views

You do not need a tool, if your database offers materialized views (MV). That is the purpose of MVs, to cache the results of a query.

Think of an MV as a table pre-populated with the results of a query. You only pay for the query once to generate the MV, then you can repeatedly access the rows in the MV "for free" without waiting for another query. You can treat the MV as a table, including doing a query on it to further narrow the results.

MVs arrived in Postgres 9.3 with the CREATE MATERIALIZED VIEW command. In a later version, you can even update the MV while still in use, meaning you can execute the query to create a replacement MV to swap out in place of the current one.

According to this DBA Stack Exchange Question and specifically this Answer, MySQL does not offer MVs directly but discusses some alternatives:

  • FlexViews is an open-source project implementing materialized views in MySQL.
  • That Answer also provides alternative strategies.
Improve this answer
4
  • Mysql does not support materialized views, but even if it did, this would not solve the problem as you still need to make a database call to query the materialized table, so it's even less effective than a read replica. The problem I am having, is that I need to reduce the number of queries being made on the database.
    – Benubird
    Commented Jul 6, 2015 at 9:42
  • @Benubird You asked for a tool to cache the results of certain queries to ease the burden on your database server. Materialized views cache the results of certain queries to ease the burden on your database server. The FlexViews project discussed in the linked Question/Answers is an implementation of materialized views for MySQL. So, I'm confused by your comment.
    – Basil Bourque
    Commented Jul 6, 2015 at 15:50
  • Materialized views reduce load by taking queries that take a long time to run, and storing the output in another table so they can be accessed quickly. This reduces the load impact of complex queries, but it does nothing for the load impact caused by a large number of requests, which is what I'm asking about. If, for instance, all of your requests are for a single table with no joins, groups, etc, then any kind of view (materialized or otherwise) is unnecessary and will actually INCREASE the complexity load, as it adds overhead.
    – Benubird
    Commented Jul 7, 2015 at 8:01
  • Flexviews looks really interesting, but it still doesn't solve the problem, which is that I want a tool I can put between my server and the database, not on the same server as the database.
    – Benubird
    Commented Jul 7, 2015 at 8:04

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.