Memcached implementation inquiry

[Steven Grimm]

Michael Firsikov wrote:
> 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).
>   

Try PostgreSQL instead. MySQL has awful concurrency problems. Much 
better in 5.x than it was in 4.x, but still not great.

PostgreSQL is slower for certain types of queries at low concurrency 
levels -- though it's actually faster across the board in more cases 
than you might expect given its reputation -- but for complex queries at 
high concurrency levels it pretty much wipes the floor with MySQL. We 
are in the middle of evaluating switching to it on some or all of our 
database nodes right now.

Also, MyISAM is very dangerous if you have high update concurrency. It 
can corrupt your data. We have had that happen and were told by the 
MySQL guys to switch to InnoDB to fix the problem (which we did.) So 
make sure you have good backups.

-Steve