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:in `execute'' /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:in `select_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:in `count_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:in `count'' Anyone have any suggestions as to what the issue might be? Thanks, Nick -- Nicholas Van Weerdenburg -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060225/10a16048/attachment-0001.html
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. On 25/02/06, Nicholas Van Weerdenburg <vanweerd@gmail.com> wrote:> 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:in > `execute'' > /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:in > `select_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:in > `count_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:in > `count'' > > Anyone have any suggestions as to what the issue might be? > > Thanks, > Nick > -- > Nicholas Van Weerdenburg > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >
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. On 25/02/06, David Mitchell <monch1962@gmail.com> wrote:> 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. > > On 25/02/06, Nicholas Van Weerdenburg <vanweerd@gmail.com> wrote: > > 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:in > > `execute'' > > /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:in > > `select_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:in > > `count_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:in > > `count'' > > > > Anyone have any suggestions as to what the issue might be? > > > > Thanks, > > Nick > > -- > > Nicholas Van Weerdenburg > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > >
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 On 2/25/06, David Mitchell <monch1962@gmail.com> wrote:> > 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. > > On 25/02/06, Nicholas Van Weerdenburg <vanweerd@gmail.com> wrote: > > 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:in > > `execute'' > > /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:in > > `select_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:in > > `count_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:in > > `count'' > > > > Anyone have any suggestions as to what the issue might be? > > > > Thanks, > > Nick > > -- > > Nicholas Van Weerdenburg > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Nicholas Van Weerdenburg -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060225/908b90c6/attachment.html
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. On 26/02/06, Nicholas Van Weerdenburg <vanweerd@gmail.com> wrote:> 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 > > > On 2/25/06, David Mitchell <monch1962@gmail.com> wrote: > > > > 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. > > > > On 25/02/06, Nicholas Van Weerdenburg <vanweerd@gmail.com> wrote: > > > 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:in > > > `execute'' > > > > /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:in > > > `select_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:in > > > `count_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:in > > > `count'' > > > > > > Anyone have any suggestions as to what the issue might be? > > > > > > Thanks, > > > Nick > > > -- > > > Nicholas Van Weerdenburg > > > _______________________________________________ > > > Rails mailing list > > > Rails@lists.rubyonrails.org > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > > > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > -- > Nicholas Van Weerdenburg > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >
Thanks. But would that effect dispatch.fcgi memory? I would have thought mysql server memory. Thanks, Nick On 2/25/06, David Mitchell <monch1962@gmail.com> wrote:> > 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. > > On 26/02/06, Nicholas Van Weerdenburg <vanweerd@gmail.com> wrote: > > 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 > > > > > > On 2/25/06, David Mitchell <monch1962@gmail.com> wrote: > > > > > > 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. > > > > > > On 25/02/06, Nicholas Van Weerdenburg <vanweerd@gmail.com> wrote: > > > > 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:in > > > > `execute'' > > > > > > /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:in > > > > `select_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:in > > > > `count_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:in > > > > `count'' > > > > > > > > Anyone have any suggestions as to what the issue might be? > > > > > > > > Thanks, > > > > Nick > > > > -- > > > > Nicholas Van Weerdenburg > > > > _______________________________________________ > > > > Rails mailing list > > > > Rails@lists.rubyonrails.org > > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > > > > > > > > _______________________________________________ > > > Rails mailing list > > > Rails@lists.rubyonrails.org > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > > > > -- > > Nicholas Van Weerdenburg > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Nicholas Van Weerdenburg -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060226/3121f2bb/attachment-0001.html