Hi Pete,
If you suspect queries are your pain point then I would do the
following to get a sense of what you app is doing under the hood.
Get a copy of http://hackmysql.com/mysqlreport and use it against
your db. It can assist in tweaking innodb or myisam table configs.
Turn on slow query logging, add the lines
log_slow_queries=/usr/local/mysql/data/mysql-slow.log (adjust path as
necessary)
log-queries-not-using-indexes
To your /etc/my.cnf and restart the db. This will log any query that
takes longer than 1 second to run (will only spot the really heinous
ones, in a busy web app a 1 second query is game over). This also logs
queries which do not use and index, hence scale badly as your tables
grow.
Recent versions of linux have an excellent vmstat and iostat, have
them running in a wind during busy times of the day and see how your
db machine handles it. Is it really out of CPU, or does your dataset
not fit in memory and you pulling huge blobs of data off the disk
because of poorly specified queries, or worse spending it rashly by
creating many temporary tables.
I have found that time spent optimising db queries, and rethinking
the datamodel to be more sql efficient pays off in the long run vs
throwing hardware at the problem.
Cheers
Dave
On 03/11/2007, at 5:38 AM, Pete DeLaurentis wrote:
> Yeah, all the foreign keys are indexed. I'm also caching what I can
> in our ruby app's memory.
>
> It seems to be the database writes that are taking up a lot of the
> time. The other offender are some multiple JOINs we're doing.
>
> I've ordered more processors for the database server, and am clearing
> off a second machine so I can get a little MySQL cluster going. Any
> advice on this is welcome since I've never scaled a database server
> before.
>
> As the database gets faster, it will help reduce the timeouts. But
> demand + server load has a way of sneaking up on you. I'm looking
> for a way of making sure our server is less likely to timeout on
> users when we run into system bottlenecks (now + in the future).
>
> Thanks,
> Pete
>
>
> On Nov 2, 2007, at 11:29 AM, barsalou wrote:
>
>> Pete,
>>
>> What about the DB is the bottleneck? I'm assuming you have already
>> looked at indexing and the like.
>>
>> Mike B.
>>
>> Quoting Pete DeLaurentis <[EMAIL PROTECTED]>:
>>
>>> Hi guys,
>>>
>>> Does anyone know if mongrel has a built-in timeout for requests that
>>> take too long? If so, how do you increase it?
>>>
>>> I've found that some server requests are timing out. Complicated
>>> requests are already being farmed out to asynchronous processes.
>>> But
>>> some of the more basic requests are timing out when we have our peak
>>> server load in the mornings (the bottleneck is the database server).
>>>
>>> There are a couple possible locations for the timeout I'm seeing:
>>>
>>> 1. Mongrel
>>> 2. Lighttpd
>>> 3. Client browser / application
>>>
>>> On my local development server, it does not timeout, leading me to
>>> believe it's not #3.
>>>
>>> Any feedback is appreciated.
>>>
>>> Thanks,
>>> Pete
>>> _______________________________________________
>>> Mongrel-users mailing list
>>> [email protected]
>>> http://rubyforge.org/mailman/listinfo/mongrel-users
>>>
>>
>>
>>
>> ----------------------------------------------------------------
>> This message was sent using IMP, the Internet Messaging Program.
>
> _______________________________________________
> Mongrel-users mailing list
> [email protected]
> http://rubyforge.org/mailman/listinfo/mongrel-users
_______________________________________________
Mongrel-users mailing list
[email protected]
http://rubyforge.org/mailman/listinfo/mongrel-users
