Memcached implementation inquiry

[Michael Firsikov]

Marcus,

A typical complex search involves 5-10 tables, all properly indexed. (some
tables have in excess of 12-15 million records) However, the concurrency is
problematic (1000+ concurrent users).
We opted using myISAM engine type due to faster reading (tried innoDB but to
no avail for better performance), however quite a few tables get updated
frequently, and thus everything gets locked. When this happens, search
select queries pile up very fast, and a cascading reaction occurs. (Locking
is so bad sometimes, that slaves get out of synch with masters up to 15-30
minutes).

I am quite sure that our db is close to being optimal, we have even invited
mySQL consultant engineers over, and they have optimized tables, queries and
architecture significantly. (Normalization, replication setup, separation of
reads/writes, etc...)
I was hoping working with memcached would speed things up, as we would not
read from the database at all, but only update db/cache, and read from
cache.

Michael 


-----Original Message-----
From: Marcus Bointon [mailto:marcus at synchromedia.co.uk] 
Sent: Thursday, April 19, 2007 1:19 PM
To: Michael Firsikov
Cc: memcached at lists.danga.com
Subject: Re: Memcached implementation inquiry

On 19 Apr 2007, at 18:04, Michael Firsikov wrote:

> That is a solution, however, I can have potential searches that  
> yield very
> large resultset of matching IDs (excess of 1 million) and since  
> memcached
> can store only up to 1MB per key, I can not store 1 million 6 digit  
> IDs in
> one element and would have to fragment it further. (not that big of a
> problem)

Sounds like you could do with bigger stored values, but... If a MySQL  
search on a table of 1.4 million rows for an exact match on one  
indexed field is slower than manually looping through the same number  
of individual results from memcached and running PHP functions on  
every one of them, something is seriously wrong with your DB. You  
should fully expect MySQL to be several orders of magnitude faster  
than PHP for searches like this. If you can arrange for your data to  
be in a format that you can search this way in memcache, then you can  
arrange the same inside MySQL, where it will be many times faster.  
Can you give more detail on exactly what your searches look like?

Marcus
-- 
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of info at hand CRM solutions
marcus at synchromedia.co.uk | http://www.synchromedia.co.uk/