Which MySQL engine do you use for this table? MyISAM is faster for read operations, but locks the whole table for an update or an insert and this might affect the performance a lot. InnoDB engine uses row- level locking. Note that PostgreSQL does too.
Have you thought about using document DBs like Tokyo Cabinet, MongoDB or others? They can handle huge loads. On Aug 18, 5:39 am, Frank Poo <[email protected]> wrote: > The query is just an UPDATE with a COALESCE keyword in it. All it does > is increment counters: > > UPDATE `statistics_api` SET `count_request` = COALESCE(`count_request`, > ?) + ? WHERE (`id` = ?) > > This one took 8,553 ms according to New Relic. The weird part is that > there is an index on 'id' (as well as a few other columns) on this > table. I just can't figure out why the query's taking so long ... > > > > Marnen Laibow-Koser wrote: > > Frank Poo wrote: > >> Marnen: here are some more details. I need to be able to handle at least > >> 10 API requests / second. Let met clarify what happens: > > >> 1) User makes a RESTful API request > >> 2) I find a record in a database, return it as JSON > >> 3) I record the request counter for the user in the database (i.e. if I > >> user makes 2 API calls, I record '2'). > > >> #1 and #2 are really fast in SQL - they are SELECTs. #3 is really slow, > >> because it's an UPDATE. In the real world, my database (MySQL) is NOT > >> scaling. > > > Well, there's your first problem -- you're using mySQL. :) Try > > PostgreSQL instead. > > > (That was admittedly snarky. Although PostgreSQL is certainly the > > better choice, mySQL should still work.) > > >> According to New Relic, #3 is taking most of the time. > > > What does that query look like? A simple UPDATE query should not be a > > major performance hit, so there must be something else going on. > > > Best, > > -- > > Marnen Laibow-Koser > >http://www.marnen.org > > [email protected] > > -- > Posted viahttp://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
