Re: A memcached-like server in Ruby - feasible?

On 10/28/07, ara.t.howard <ara.t.howard@xxxxxxxxx> wrote:
i'm reading this as

- need query
- need readonly
- need sorting
- need fast
- need server

and thinking: how isn't this a readonly slave database? i think that
mysql can either do this with a readonly slave *or* it cannot be done
with modest resources.

The problem is that for a perfectly normalized database, those queries
are *heavy*.

We're using straight, direct SQL (no ActiveRecord calls) there, and
several DBAs have already looked into our query strategy. Bottom line
is that each query on the normalized database is non-trivial, and they
can't reduce it to less than 0.2 secs / query. As we have 5+ of these
queries per page, we'd need one MySQL server for every
request-per-second we want to serve. As we need at least 50 reqs/sec,
we'd need 50 MySQL servers (and probably something similar in terms of
web servers). We can't afford that.

We can only improve the queries TTC by replicating data inside the
database, i.e. de-normalizing it with internal caching at the table
level (basically, that amounts to replicating certain columns from
table `bars` in table `foos`, thus saving some very heavy JOINs).

But if we're already de-normalizing, caching and replicating data, we
might as well create another layer of de-normalized, processed data
between the database and the Rails servers. That way, we will need
less MySQL servers, output requests faster (as the layer would hold
the data in an already processed state), and save a much of the
replication / clustering overhead.

-Tom

.

Relevant Pages

  • Re: [PHP] Problem with SELECT statement and reference material wanted..
    ... echo you query and then run it on mysql and see u getting the desired ... I'm having trouble retrieving a selection of my database contents. ... ..I will get all entries from the database, ... required to be named as the column names in the MySQL table, ...
    (php.general)
  • Ordering on Hierarchical Dot Notation
    ... I have a SQL database (MySql) containing a table with a key column which ... The problem is that this column is proving very difficult to sort naturally ... users still want to query the database using the natural representation. ...
    (comp.lang.java.databases)
  • Ordering on Hierarchical Dot Notation
    ... I have a SQL database (MySql) containing a table with a key column which ... The problem is that this column is proving very difficult to sort naturally ... users still want to query the database using the natural representation. ...
    (comp.lang.java.programmer)
  • Re: Request suggestion for query
    ... The first is a request for a suggestion of a SQL query. ... First I suggest you learn some relational database design. ... SELECT SUM(amount) as amount FROM budget WHERE member_id=1 ... Second, LIMIT is a MySQL extension, not standard SQL. ...
    (comp.databases)
  • Re: C client to load binary data to MySQL
    ... to the C standard you're only allowed to compare pointers pointing ... Since one is unlikely to be constructing a query string anywhere even close to 30000 characters long I would say this makes it safe for all implementations without being any harder to read. ... To the OP, if you need further help with the MySQL parts of this, such as the load_image function and connecting to the database, please take it to a suitable group, possibly one with mysql or database in its name, or the MySQL mailing lists. ...
    (comp.lang.c)