How To Enable Mysql Slow Query Log

In my.cnf

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slowquery.log
long_query_time = 1

In SSH

mysqldumpslow /var/lib/mysql/slowquery.log

Following will show top 5 query which returned maximum rows. It can find queries where you missed LIMIT clause. A common performance killer!

mysqldumpslow -a -s r -t 5 /var/lib/mysql/slowquery.log

Following will sort output by count i.e. number of times query found in slow-log. Most frequency queries sometimes turned out to be unexpected queries!

mysqldumpslow -a -s c -t 5 /var/lib/mysql/slowquery.log

How To Grab IP Addresses of Review Spammers for Interspire Shopping Cart

Open /includes/classes/class.review.php, look for:

// Save the review in the database
$newReview = array(
  "revproductid" => (int)$reviewPostData['product_id'],
  "revfromname" => $reviewPostData['revfromname'],
  "revdate" => time(),
  "revrating" => max(1, min(5, $reviewPostData['revrating'])),
  "revtext" => $reviewPostData['revtext'],
  "revtitle" => $reviewPostData['revtitle'],
  "revstatus" => $status
);

Change to:

// Save the review in the database
$newReview = array(
"revproductid" => (int)$reviewPostData['product_id'],
"revfromname" => $reviewPostData['revfromname'],
"revdate" => time(),
"revrating" => max(1, min(5, $reviewPostData['revrating'])),
"revtext" => $reviewPostData['revtext'],
"revtitle" => $reviewPostData['revtitle'],
"revstatus" => $status,
"reviewip" => getIp()
);

Then in your database, alter the isc_reviews table to include a column called ‘reviewip’. You can now check who is spamming you before deleting their nonsense and banning them from accessing your site.

To display IP address in Admin view:

Open /admin/includes/classes/class.review.php, look for:

if ($row['revfromname'] != "") {
	$GLOBALS['PostedBy'] = isc_html_escape($row['revfromname']);
} else {
	$GLOBALS['PostedBy'] = GetLang('NA');
}

Change to:

if ($row['revfromname'] != "") {
	$GLOBALS['PostedBy'] = isc_html_escape($row['revfromname']);
} else {
	$GLOBALS['PostedBy'] = GetLang('NA');
}

if ($row['reviewip'] != "") {
	$GLOBALS['ReviewIP'] = "(".$row['reviewip'].")";
} else {
	$GLOBALS['ReviewIP'] = "";
}

Open /public_html/admin/templates/reviews.manage.row.tpl, look for:

{{ PostedBy|safe }}

Change to:

{{ PostedBy|safe }}
{{ ReviewIP|safe }}

Optimized Apache Global Configuration for HTTPS Sites

For 4-Core CPU, 16GB RAM Server

SSL Cipher Suite

ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:kEDH+AESGCM:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES256-SHA:DHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA256:DHE-RSA-AES256-SHA256:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA:ECDHE-RSA-DES-CBC3-SHA:ECDHE-ECDSA-DES-CBC3-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA:AES256-SHA:AES:CAMELLIA:DES-CBC3-SHA:!aNULL:!eNULL:!EXPORT:!DES:!RC4:!MD5:!PSK:!aECDH:!EDH-DSS-DES-CBC3-SHA:!EDH-RSA-DES-CBC3-SHA:!KRB5-DES-CBC3-SHA

SSL/TLS Protocols

All -SSLv2 -SSLv3

LogLevel

warn

Trace Enable

Off

Server Signature

Off

Server Tokens

Product Only

File ETag

None

Directory “/” Options

ExecCGI
FollowSymLinks 
IncludesNOEXEC 

Start Servers

25

Minimum Spare Servers

25

Maximum Spare Servers

50

Server Limit

1000

Max Request Workers

1000

Max Connections Per Child

0

Keep-Alive

On

Keep-Alive Timeout

100

Max Keep-Alive Requests

Unlimited

Timeout

300

Optimized .htaccess for Interspire Shopping Cart

php_flag magic_quotes_gpc Off
Options -MultiViews +FollowSymlinks +Indexes
IndexIgnore *
ErrorDocument 401 "Unauthorized access"


	SecFilterEngine Off
	SecFilterScanPOST Off



	RewriteEngine On

	RewriteCond %{HTTPS} !=on [NC]
	RewriteRule ^(.*)$ https://%{HTTP_HOST}%{REQUEST_URI} [R=301,L]

	RewriteCond %{REQUEST_FILENAME} robots.txt
	RewriteCond %{REQUEST_FILENAME} !-f
	RewriteRule robots.txt robots_default.txt [L]

	RewriteCond %{REQUEST_FILENAME} !-f
	RewriteCond %{REQUEST_FILENAME} !-d
	RewriteRule . index.php
	
		SetEnv SEO_SUPPORT 1
	



	AddOutputFilterByType DEFLATE text/html text/xml text/css text/plain
	AddOutputFilterByType DEFLATE image/svg+xml application/xhtml+xml application/xml
	AddOutputFilterByType DEFLATE application/rdf+xml application/rss+xml application/atom+xml
	AddOutputFilterByType DEFLATE text/javascript application/javascript application/x-javascript application/json
	AddOutputFilterByType DEFLATE application/x-font-ttf application/x-font-otf
	AddOutputFilterByType DEFLATE font/truetype font/opentype



	ExpiresActive On
	ExpiresByType image/x-icon "access plus 1 month"
	ExpiresByType image/jpeg "access plus 1 month"
	ExpiresByType image/png "access plus 1 month"
	ExpiresByType image/gif "access plus 1 month"
	ExpiresByType text/css "access plus 1 month"
	ExpiresByType text/javascript "access plus 1 month"
	ExpiresByType application/javascript "access plus 1 month"
	ExpiresByType application/x-javascript "access plus 1 month"
	ExpiresByType text/html "access plus 30 minutes"
	ExpiresByType application/xhtml+xml "access plus 30 minutes"



	Header always set Strict-Transport-Security "max-age=31536000" env=HTTPS
	Header unset ETag
	Header append Vary User-Agent
	FileETag None

  
    Header set Cache-Control "public"
  
  
    Header set Cache-Control "public"
  
  
    Header set Cache-Control "private"
  
  
    Header set Cache-Control "private, must-revalidate"
  

Optimized Apache/PHP Modules for Interspire Shopping Cart

Based on Apache 2.4.23 / PHP 5.3.29

Apache

Deflate
Expires
Headers
MPM Prefork
Proxy
UniqueId
Mod Security 2.9.0
Suhosin 0.9.33 for PHP
XCache 3.2.0 for PHP

PHP

CGI
Calendar
CurlSSL
FTP
GD
Iconv
Imap
MBstring
Mcrypt
Mysql
MySQL "Improved" extension
OpenSSL
PDO
PDO MySQL
POSIX
Pear
Phar
Pspell
SOAP
SQLite3
Sockets
Zlib

XCache Optimized Settings

php.ini location for cPanel

/usr/local/lib/php.ini

Settings based on 4-Core CPU

xcache.admin.enable_auth="on"
xcache.admin.pass=""
xcache.admin.user="root"
xcache.cacher="On"
xcache.coredump_directory=""
xcache.count="4"
xcache.coveragedump_directory="/tmp/pcov/"
xcache.coverager="Off"
xcache.gc_interval="0"
xcache.mmap_path="/dev/zero"
xcache.optimizer="On"
xcache.readonly_protection="Off"
xcache.size="128M"
xcache.slots="8K"
xcache.test="Off"
xcache.ttl="0"
xcache.var_count="4"
xcache.var_gc_interval="300"
xcache.var_maxttl="0"
xcache.var_size="0"
xcache.var_slots="8K"
xcache.var_ttl="0"