My CMSMS database was blocked and asked to optmize

[twen88]

Hi there,

I got a message from my hosting provider saying that my cmsms site or a portion or feature of my account has caused an overload on one of their servers. And they gave me 10 days to optmize my database. This is their suggestion:

We appreciate you contacting us to resolve this issue. At this time your datadase has been unblocked so that you can work on it’s optimization. Please, take a look to qeries caused the problem:

To avoid this issue in future I suggest you to folow these recommendations:

1. EXPLAIN Your SELECT Queries. Using the EXPLAIN keyword can give you insight on what MySQL is doing to execute your query. This can help you spot the bottlenecks and other problems with your query or table structures.
2. LIMIT 1 When Getting a Unique Row. Sometimes when you are querying your tables, you already know you are looking for just one row. You might be fetching a unique record, or you might just be just checking the existence of any number of records that satisfy your WHERE clause. In such cases, adding LIMIT 1 to your query can increase performance. This way the database engine will stop scanning for records after it finds just 1, instead of going thru the whole table or index.
3. Index the Search Fields. Indexes are not just for the primary keys or the unique keys. If there are any columns in your table that you will search by, you should almost always index them.
4. Do Not ORDER BY RAND(). This is one of those tricks that sound cool at first, and many rookie programmers fall for this trap. You may not realize what kind of terrible bottleneck you can create once you start using this in your queries. If you really need random rows out of your results, there are much better ways of doing it. Granted it takes additional code, but you will prevent a bottleneck that gets exponentially worse as your data grows. The problem is, MySQL will have to perform RAND() operation (which takes processing power) for every single row in the table before sorting it and giving you just 1 row.
5. Split the Big DELETE or INSERT Queries. If you need to perform a big DELETE or INSERT query on a live website, you need to be careful not to disturb the web traffic. When a big query like that is performed, it can lock your tables and bring your web application to a halt.


And let me note that it is really necessary that you perform some steps on your end to decrease resource usage (on MYSQL server), because your account may be permanently suspended if the same problem reoccurs three times. When 10 days passes since domain reactivation, any new occurrence of MYSQL overload abuse will be counted as new ToS Violation.

Any suggestion on how to deal with this request?

Below is my system info:

CMS Version 1.11.7


Installed Modules
CMSMailer 5.2.1
CMSPrinting 1.0.4
FileManager 1.4.3
MenuManager 1.8.5
MicroTiny 1.2.5
ModuleManager 1.5.5
News 2.12.12
Search 1.7.8
CGExtensions 1.37.2
FrontEndUsers 1.21.18
SelfRegistration 1.8.1
CGCalendar 1.10.4
CustomContent 1.9
TinyMCE 2.9.12


CMS Config Settings
php_memory_limit
process_whole_template
max_upload_size 20000000
url_rewriting mod_rewrite
page_extension .html
query_var page
image_manipulation_prog GD
auto_alias_content true
locale
default_encoding utf-8
admin_encoding utf-8
set_names true
debug false
timezone America/New_York
set_names true

use_smarty_php_tags


PHP Information
Current PHP Version (phpversion) 5.3.28
md5 function (md5_function) On (True)
GD version (gd_version) 2
tempnam function (tempnam_function) On (True)
Magic quotes in runtime (magic_quotes_runtime) Success Off (False)
Is E_STRICT disabled in error_reporting (E_STRICT)
Is E_DEPRECATED disabled in error_reporting (E_DEPRECATED)
PHP Effective Memory Limit (memory_limit) 62M
Maximum Execution Time (max_execution_time) Success 120
PHP output_buffering (output_buffering) 4096
PHP Safe Mode (safe_mode) Off (False)
File uploads (file_uploads) On (True)
Maximum Post Size (post_max_size) 20M
Maximum Upload Size (upload_max_filesize) 20M
Session Save Path (session_save_path) /tmp (1777)
Sessions are allowed to use Cookies (session_use_cookies) Success On (True)
Basic XML (expat) support (xml_function) On (True)
Checking for the XMLReader class (xmlreader_class) On (True)
Checking if the httpd process can create a file inside of a directory it created (create_dir_and_file) Success
PHP register_globals (register_globals) Success Off (False)
disable_functions in PHP (disable_functions) Success
PHP Open Basedir (open_basedir) Success
Test for remote URL (test_remote_url) Success
fsockopen: Connection ok! Success
fopen: Connection ok! Success
Test ini_set (check_ini_set) Success On (True)
Test for the curl library (curl) Success On
Test curl version (curlversion) Caution Test Curl Version version 7.15.5, minimum recommended version is 7.19.7

[Dr.CSS]

You need to upgrade to the latest version of CMSMS and I would maybe look for a better host as CMSMS is optimized to use as few DB queries as possible, sounds like an oversold shared server to me...

[JohnnyB]

What host do you use?

It sounds like BS from a shared hosting provider that doesn't want to allocate the necessary resources.
Yes, there is room for mysql query optimization in the core, but those optimizations are inmho not significant enough to cause a major bottleneck.

Do you have a lot of data records in a particular module? Or thousands of pages? What kind of site is this?

You should also know that your host is just copying and pasting quotes from mysql optimization tips that have been posted on the web for at least the last 5+ years.... it is not something they crafted themselves. It is rare for a hosting company to actually understand what your database queries are actually doing and I can say with confidence that they didn't look at your database, or the CMSMS code base. They just looked at the phpmyadmin logs....


Advice - find yourself another hosting company.

[calguy1000]

Alot of work has gone into the CMSMS core to make the standard usage for core and core modules optimal.

That being said there are a few things that could cause your website to cause extra-ordinary database demand:
a: Alot of load on standard HTML (not session influenced, user influenced or form driven) pages.
(i.e: usually hundreds of thousands of hits per day).
One way to optimize this would be to enable caching at both the browser, and smarty level.
b: In-efficient third party code.

Either way. You will need to know exactly what the problem is, and how your site is causing it, and understand your page traffic in order to get some assistance as to how to solve it.

[twen88]

Thank you all for your response!

I do suspect this is a sales pitch on the hosting side. I am on IXwebhosting, and this is only a membership based site with about 2600 registered users. there is no extra html page outside of the cmsms. The main module used is front-end-users (no self-registraaton), news and cgcalendar. The mysql database backed up for about 50mb in size.

Now they suggest that their new "ix cloud" package will provide more resources to resolve this issue.

[paulbaker]

I am on IXwebhosting

Hmmm, from their website:

Expert Plan:
Unlimited Disk Space
Unlimited Bandwidth
Unlimited Hosted Domains
1 dedicated IP
Cost: USD 3.95 Per Month

If it seems too good to be true, it probably isn't.

I would be finding another host.