Dear ThemeBurn!

This has happened to me before and has happened once again. I understand that I should not be running my website on a shared plan and should move to (at least) a VPS but I was wondering if you could provide me with some insight on this. Your response shall be much appreciated in this regard. Hostgator blocked the DB and sent me this:

============================================================================
============================================================================

This message is to advise you of a temporary block placed on your database. The database associated with the script in “/home/megamall/public_html/” was found to be consuming an inordinate amount of mysql connections, to the point of degrading overall system performance. While we do limit each account to no more than 25% of a system’s resources in our terms of service, we do not actively disable accounts until they greatly exceed that number, which is what happened in this case.
Resolving this situation may be as simple as modifying the code to to use mysql_connect instead of mysql_pconnect, closing connections sooner, adding additional indexes to your database, optimizing the queries used, or something equally easy. If not, it may simply be a matter of moving this database to dedicated services, as it may have outgrown a shared environment.

If you believe you have a solution to this overuse, we are happy to discuss the situation with you and possibly reinstate the database on the server. Otherwise, we will be happy to assist you with the upgrade process if a dedicated server is the most appropriate solution. Thank you, and we look forward to hearing from you shortly.

~
Excessive MySQL activity is caused by (a) a long-running process that locks a table, causing other queries to back up, (b) a query that is not optimized ][example: select all from ... and involving a large or complex query], (c) huge table copies/maintenance during peak hours.

NOTE:, the following are just possible fixes or suggestions, and are not endorsed or supported by HostGator. They are included in the hope that they may apply to your situation, and/or help you reduce the amount of resources your SQL queries consume. As always, it’s best to backup any data before making any changes or adjustments.

First and foremost, you may need to optimize your tables. The frequency depends on the size and usage of the database, but most databases would benefit from doing something like this on a yearly basis: a) Enter your phpMyAdmin/MySQL control panel. Click on the database (not the table, the database name), and on the right hand column your tables should be listed. Scroll down till you see the .Check all. link. Click on that link, make sure all database tables are checked and then from the drop-down next to it, and carefully select .Optimize table..

Additionally, adding indexes to your table(s) may improve performance. If you’re not sure what you’re doing, it’s best not to modify any table; caution is recommended. There are various articles (http://www.developer.com/print.php/3667831). It may be best to Google for something like [Your Software Name] MySQL indexes for suggestions.

If you reply back to this with your IP address (http://www.hostgator.com/ip.shtml) we will be more than happy to go ahead enable HTTP access for you, so that you can safely work on the script without it causing further issues. Please let us know how you would like to proceed.
Running Processes:
=============== Pass 1 ===============
megamall 4231 0.1 0.1 231528 14452 ? SN 12:26 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 6950 0.2 0.1 231788 14428 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 6952 0.2 0.1 231788 14608 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 7129 0.2 0.1 231788 14468 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 7131 0.2 0.1 231788 14612 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 7145 0.2 0.1 231788 14612 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 7497 0.2 0.1 231272 14224 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
=============== Pass 2 ===============
megamall 4231 0.1 0.1 231528 14452 ? SN 12:26 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 6950 0.2 0.1 231788 14428 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 6952 0.2 0.1 231788 14608 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 7129 0.2 0.1 231788 14468 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 7131 0.2 0.1 231788 14612 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 7145 0.2 0.1 231788 14612 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 7497 0.2 0.1 231272 14224 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
=============== Pass 3 ===============
megamall 4231 0.1 0.1 231528 14452 ? SN 12:26 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 6950 0.2 0.1 231788 14428 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 6952 0.2 0.1 231788 14608 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 7129 0.2 0.1 231788 14468 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 7131 0.2 0.1 231788 14612 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 7145 0.2 0.1 231788 14612 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php
megamall 7497 0.2 0.1 231272 14224 ? SN 12:27 0:00 /usr/bin/php /home/megamall/public_html/index.php

Running Queries:
*************************** 1. row ***************************
USER: megamall_vaqar
DB: megamall_oc1511
STATE: closing tables
TIME: 1
COMMAND: Query
INFO: SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = ’8′ AND pd2.quantity = ’1′ AND ((pd2.date_start = ’0000-00-00′ OR pd2.date_start < NOW()) AND (pd2.date_end = ’0000-00-00′ OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = ’8′ AND ((ps.date_start = ’0000-00-00′ OR ps.date_start < NOW()) AND (ps.date_end = ’0000-00-00′ OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = ’8′) AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = ’1′ AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = ’1′) AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = ’1′) AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = ’1′ GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = ’1′ GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = ’86′ AND pd.language_id = ’1′ AND p.status = ’1′ AND p.date_available <= NOW() AND p2s.s tore_id = ’0′
*************************** 2. row ***************************
USER: megamall_vaqar
DB: megamall_oc1511
STATE: closing tables
TIME: 0
COMMAND: Query
INFO: SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = ’1′ AND p.status = ’1′ AND p.date_available <= NOW() AND p2s.store_id = ’0′ AND (p2c.category_id = ’78′)
*************************** 3. row ***************************
USER: megamall_vaqar
DB: megamall_oc1511
STATE: Opening tables
TIME: 0
COMMAND: Query
INFO: SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = ’1′ AND p.status = ’1′ AND p.date_available <= NOW() AND p2s.store_id = ’0′ AND (p2c.category_id = ’77′)
*************************** 4. row ***************************
USER: megamall_vaqar
DB: megamall_oc1511
STATE: Opening tables
TIME: 0
COMMAND: Query
INFO: SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = ’1′ AND p.status = ’1′ AND p.date_available <= NOW() AND p2s.store_id = ’0′ AND (p2c.category_id = ’79′)
*************************** 5. row ***************************
USER: megamall_vaqar
DB: megamall_oc1511
STATE: Opening tables
TIME: 0
COMMAND: Query
INFO: SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = ’1′ AND p.status = ’1′ AND p.date_available <= NOW() AND p2s.store_id = ’0′ AND (p2c.category_id = ’79′)
*************************** 6. row ***************************
USER: megamall_vaqar
DB: megamall_oc1511
STATE: closing tables
TIME: 1
COMMAND: Query
INFO: SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = ’8′ AND pd2.quantity = ’1′ AND ((pd2.date_start = ’0000-00-00′ OR pd2.date_start < NOW()) AND (pd2.date_end = ’0000-00-00′ OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = ’8′ AND ((ps.date_start = ’0000-00-00′ OR ps.date_start < NOW()) AND (ps.date_end = ’0000-00-00′ OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = ’8′) AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = ’1′ AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = ’1′) AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = ’1′) AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = ’1′ GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = ’1′ GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = ’62′ AND pd.language_id = ’1′ AND p.status = ’1′ AND p.date_available <= NOW() AND p2s.s tore_id = ’0′
*************************** 7. row ***************************
USER: megamall_vaqar
DB: megamall_oc1511
STATE: Opening tables
TIME: 0
COMMAND: Query
INFO: SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = ’1′ AND p.status = ’1′ AND p.date_available <= NOW() AND p2s.store_id = ’0′ AND (p2c.category_id = ’79′)
*************************** 1. row ***************************
USER: megamall_vaqar
DB: megamall_oc1511
STATE: closing tables
TIME: 1
COMMAND: Query
INFO: SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = ’8′ AND pd2.quantity = ’1′ AND ((pd2.date_start = ’0000-00-00′ OR pd2.date_start < NOW()) AND (pd2.date_end = ’0000-00-00′ OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = ’8′ AND ((ps.date_start = ’0000-00-00′ OR ps.date_start < NOW()) AND (ps.date_end = ’0000-00-00′ OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = ’8′) AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = ’1′ AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = ’1′) AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = ’1′) AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = ’1′ GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = ’1′ GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = ’86′ AND pd.language_id = ’1′ AND p.status = ’1′ AND p.date_available <= NOW() AND p2s.s tore_id = ’0′
*************************** 2. row ***************************
USER: megamall_vaqar
DB: megamall_oc1511
STATE: closing tables
TIME: 0
COMMAND: Query
INFO: SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = ’1′ AND p.status = ’1′ AND p.date_available <= NOW() AND p2s.store_id = ’0′ AND (p2c.category_id = ’78′)
*************************** 3. row ***************************
USER: megamall_vaqar
DB: megamall_oc1511
STATE: Opening tables
TIME: 0
COMMAND: Query
INFO: SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = ’1′ AND p.status = ’1′ AND p.date_available <= NOW() AND p2s.store_id = ’0′ AND (p2c.category_id = ’77′)
*************************** 4. row ***************************
USER: megamall_vaqar
DB: megamall_oc1511
STATE: Opening tables
TIME: 0
COMMAND: Query
INFO: SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = ’1′ AND p.status = ’1′ AND p.date_available <= NOW() AND p2s.store_id = ’0′ AND (p2c.category_id = ’79′)
*************************** 5. row ***************************
USER: megamall_vaqar
DB: megamall_oc1511
STATE: Opening tables
TIME: 0
COMMAND: Query
INFO: SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = ’1′ AND p.status = ’1′ AND p.date_available <= NOW() AND p2s.store_id = ’0′ AND (p2c.category_id = ’79′)
*************************** 6. row ***************************
USER: megamall_vaqar
DB: megamall_oc1511
STATE: closing tables
TIME: 1
COMMAND: Query
INFO: SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = ’8′ AND pd2.quantity = ’1′ AND ((pd2.date_start = ’0000-00-00′ OR pd2.date_start < NOW()) AND (pd2.date_end = ’0000-00-00′ OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = ’8′ AND ((ps.date_start = ’0000-00-00′ OR ps.date_start < NOW()) AND (ps.date_end = ’0000-00-00′ OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = ’8′) AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = ’1′ AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = ’1′) AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = ’1′) AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = ’1′ GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = ’1′ GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = ’62′ AND pd.language_id = ’1′ AND p.status = ’1′ AND p.date_available <= NOW() AND p2s.s tore_id = ’0′
*************************** 7. row ***************************
USER: megamall_vaqar
DB: megamall_oc1511
STATE: Opening tables
TIME: 0
COMMAND: Query
INFO: SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = ’1′ AND p.status = ’1′ AND p.date_available <= NOW() AND p2s.store_id = ’0′ AND (p2c.category_id = ’79′)

Open connections:

Recent Site Requests:
157.56.92.162 megamall.com.pk /index.php?route=product/category&path=100_102&sort=pd.name
157.56.92.162 megamall.com.pk /index.php?route=product/category&path=60_88&sort=p.sort_or
157.56.92.162 megamall.com.pk /index.php?route=product/category&path=62_102_104&sort=rati
182.182.95.223 megamall.com.pk /function.mysql-connect
39.32.203.36 megamall.com.pk /index.php?route=product/product&path=63_73&product_id=56
39.32.203.36 megamall.com.pk /index.php?route=product/product&path=63_73&product_id=56
65.55.24.234 megamall.com.pk /index.php?route=product/category&path=56_62_102_104&setLis
65.55.52.116 megamall.com.pk /index.php?route=product/category&path=60_88&sort=rating&or
65.55.52.116 megamall.com.pk /index.php?route=product/category&path=62_102&sort=rating&o
65.55.52.116 megamall.com.pk /index.php?route=product/category&path=62_102&sort=rating&o
66.249.73.120 megamall.com.pk /index.php?route=product/special

Thank you and have a great day,
Carl V.
Monitoring Department
Hostgator.com Support

============================================================================
============================================================================