MySQL client ran out of memory

Rails
1

I’m getting the following error unexpectedly for my rails app. I just
exported the databae from dreamhost and imported it on textdrive for
my rails app.

Mysql::Error: MySQL client ran out of memory: SELECT COUNT(*) FROM
comments WHERE (comments.listing_id = 2666)

/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/abstract_adapter.rb:88:in
log' /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/mysql_adapter.rb:180:inexecute’
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/mysql_adapter.rb:322:in
select' /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/mysql_adapter.rb:175:inselect_one’
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/abstract/database_statements.rb:16:in
select_value' /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/base.rb:518:incount_by_sql’
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/base.rb:511:in
count' /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/associations/has_many_association.rb:49:incount’

Anyone have any suggestions as to what the issue might be?

Thanks,
Nick

2

A few ideas:

  • how much memory is on the Textdrive machine vs. the Dreamhost
    machine? If it’s less, that may be the problem right there
  • did you bring your indexes across from Dreamhost to the Textdrive
    machine?
  • are you presenting seriously big wads of data on a single Web page?
  • is it consistently happening? If not, maybe some other user is
    doing something at the same time that’s causing you grief
  • do you have indexes on your tables, and are they indexing the
    correct fields? If not, and you’re using a few joins in your SQL
    statements, your code may be causing MySQL to load in lots of unwanted
    data unnecessarily
  • are you doing a big SELECT, maybe across a few joined tables, with
    an ORDER BY? That’s a common cause of exhausting memory in most
    databases; lots of data has to be held in memory at once, so the ORDER
    BY can do its stuff. Typically you can restructure your SQL statement
    to get around this; you might need to resort to manually using
    find_by_sql with Rails to do so

You can use EXPLAIN in the MySQL command line client to see how
specific SQL statements work. That might be a help, if you understand
SQL.

Good luck

Dave M.

3

Ah, sorry - just noticed in your error message…

Do you have an index on the comments.listing_id field? If not, you
probably should. If you do, is it the correct type of index? For
example, MySQL ideally wants to see different types of indexes for
fields that must be unique vs. fields that may have many records with
the same index value.

Regards

Dave M.

4

Thanks.

Textdrive has a 80MB virtual and 48M physical limit for my user account,
and
I’m hitting that. Now to get that down…

Thanks,
Nick

5

I’d look at your indexes - if you’ve got any big tables, you really
don’t want to be doing sequential scans through them if you can avoid
it.

Regards

Dave M.

6

Thanks.

But would that effect dispatch.fcgi memory? I would have thought mysql
server memory.

Thanks,
Nick