search

gingbug / Drupal-for-The-Synaptic-Leap

Drupal 7
http://thesynapticleap.org
GNU General Public License v2.0
3 stars 0 forks source link

CPU and memory utilization was high #5

Open gingbug opened 12 years ago

gingbug commented 12 years ago

On April 25th Site5 shut down all of our websites because they said TSL was using inordinate resources. https://backstage.site5.com/client/helpdesk/view/872423/PHNP-22876

I took the following measures to reduce load:

They said we're within tolerance again.

We may want to consider adding Boost in the future. If it works as described, it may prevent this from occurring later: http://drupal.org/project/boost

incoherentboy commented 12 years ago

Hi Ginger,

Ok, interesting. Pretty soon we're expecting a spike of traffic to the site as we hope a well read blog is going to do an article on us ( http://pipeline.corante.com). It would be good to keep an eye on our stats.

Cheers,

Paul

On 15 May 2012 06:20, Ginger < reply@reply.github.com

wrote:

On April 25th Site5 shut down all of our websites because they said TSL was using inordinate resources. https://backstage.site5.com/client/helpdesk/view/872423/PHNP-22876

  • Your account: 196.44 CPU minutes per day
  • Average usage: 7.28 CPU minutes per day
  • Top Process %CPU 37.0 /usr/bin/php /home/bizmesol/public_html/prod/drupal-7/cron.php
  • Top Process %CPU 34.0 /usr/bin/php /home/bizmesol/public_html/prod/drupal-7/index.php
  • Top Process %CPU 1.8 /usr/bin/php /home/bizmesol/public_html/chadwickconsulting/index.php

I took the following measures to reduce load:

  • turned on css and page compression
  • turned caching on globally
  • turned caching on for views
  • removed the member directory from the front page

They said we're within tolerance again.

We may want to consider adding Boost in the future. If it works as described, it may prevent this from occurring later: http://drupal.org/project/boost

Reply to this email directly or view it on GitHub: https://github.com/gingbug/Drupal-for-The-Synaptic-Leap/issues/5

gingbug commented 11 years ago

Site5, our hosting provider, opened another ticket up against us and warned us they'd shut us down if we don't fix our performance issues.
https://backstage.site5.com/client/helpdesk/view/1151721/MQHC-13822

This was shortly after chadwickconsulting.com was upgraded to 6.x Drupal. But per Site5 admins, the bulk of the activity is coming from thesynapticleap.org

In parallel, Mark Lindsey deployed the Drupal 7 envt for TSL locally and noticed that he had to up his mysql.conf settings to give it more files and parameters than the Workday DB - which is far bigger. We began looking at the structure - looking to see if tables had indexes. The do. BUT, some of the tables are running as MyISAM and a few are running as InnoDB. Uggh.

We're now investigating how to untangle this. I have a comment out to Site5 guys asking for help. We'll see what they come back with.

In the meantime my research notes: A shell script, looks a bit complex: http://wiki.vpslink.com/Convert_InnoDB_to_MyISAM

This thread is more simple: http://stackoverflow.com/questions/8558147/whats-the-safest-way-to-convert-table-with-innodb-to-myisam

It would appear that if you don't have foreign keys you can run the following command: ALTER TABLE jobs ENGINE = MyISAM;

Another person suggested dumping the DB and replacing all the text to MyISAM and then recreating the DB. That sure seems like a good idea too.

I'll discuss with Mark.

I'm leaning towards exporting the database creating a copy of that file - changing the create statements to use MyISAM. But only doing that for backup.

Then trying to use the simple sql commands on each table: ALTER TABLE tbname ENGINE = MyISAM; Per this create table list, it should work.
http://dev.mysql.com/doc/refman/5.0/en/create-table.html

gingbug commented 11 years ago

Tables configured as InnoDB on TSL database: block_node_type blocked_ips cache cache_admin_menu cache_block cache_bootstrap cache_field cache_filter cache_form cache_image cache_menu cache_page cache_path cache_update ckeditor_input_format ckeditor_settings ctools_css_cache ctools_object_cache date_format_locale date_format_type date_formats field_config field_config_instance field_data_body field_data_comment_body field_data_field_file_attachment field_data_node_image field_data_taxonomy_forums field_data_taxonomy_vocabulary_2 field_data_taxonomy_vocabulary_3 field_data_taxonomy_vocabulary_4 field_data_upload field_revision_body field_revision_comment_body field_revision_field_file_attachment field_revision_node_image field_revision_taxonomy_forums field_revision_taxonomy_vocabulary_2 field_revision_taxonomy_vocabulary_3 field_revision_taxonomy_vocabulary_4 field_revision_upload file_managed file_usage filter forum_index image_effects image_styles queue registry registry_file role_permission sequences shortcut_set shortcut_set_users taxonomy_index tracker_node tracker_user trigger_assignments

gingbug commented 11 years ago

I took mark's suggestion and as of 10:40 AM Pacific today, our tables are now all MyISAM. Steps to get there as follows:

Note: the old database is still there for now. I need to clean that up and update our backup scripts.

To truly minimize site overhead, I need to do the same thing for chadwickconsulting which has the same issues.
I commented to site 5 admins to apprise them of the status. Hopefully they'll give us good news.