What is Query Cache and How it works?

One of the best ways to speed up your web application is to enable query caching in your database, which caches commonly used SQL queries in memory for virtually instant access by the next page that makes the same request.

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.

How to enable caching in MySQL?

Query Cache Can be Enabled by two ways
1) Using MySQL Queries
2) Using MySQL Configuration file i.e. my.cnf

1) Enabling Query Cache by executing query:

Login to MySQL prompt

[root@server ~]# mysql -u root –p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 16 to server version: 

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>

Now setup cache size 16Mb (16*1024*1024=16777216):

mysql> SET GLOBAL query_cache_size = 16777216;
Query OK, 0 rows affected (0.00 sec)

You can check the cache size by executing following query:

mysql> SHOW VARIABLES LIKE 'query_cache_size';
 +------------------+----------+
 | Variable_name    | Value    |
 +------------------+----------+
 | query_cache_size | 16777216 |
 +------------------+----------+
mysql>

2) Enabling Query Cache in MySQL Configuration (my.cnf)

You can setup them in /etc/my.cnf (Red Hat) or /etc/mysql/my.cnf (Debian) file. Open my.cnf uing vi or pico editor and insert following into it:

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

In above example the maximum size of individual query results that can be cached set to 1048576 using query_cache_limit system variable. Memory size in Kb.

First check the current version of Perl

# perl -v

If the older version of Perl is running on server, you get an output something like,

This is perl, v5.8.7 built for i686-linux

Upgrade perl on the Cpanel server
1) Download the latest perl installer from cPanel site.

# wget http://layer1.cpanel.net/perl588installer.tar.gz

2) Untar the file as,

# tar -zxvf perl588installer.tar.gz

3) Change the directory to perl588installer

# cd perl588installer

4) Now run the installer file as,

 # ./install

5) Check perl modules

 # /scripts/checkperlmodules

6) Force a cPanel update

 # /scripts/upcp –force

7) Check the new installed perl version using “perl -v” and you get an output something like,

This is perl, v5.8.8 built for i686-linux

What is PowerDNS

PowerDNS is a versatile DNS server, written in C++ and licensed under the GPL. It runs on most Unix derivatives and on Microsoft Windows. PowerDNS features a large number of different backends ranging from simple BIND style zonefiles to relational databases and load balancing/failover algorithms. A DNS recursor is also included as a separate program. (Definition as per wikipedia.org)

Installation of PHP, MySQL other required modules

Installation of PowerDNS and PDNS requires certain modules which are PHP(if you are going to install PowerAdmin web client too) Mysql, DB and MDB2. you can install all these modules using following commands:

yum install mysql mysql-devel mysql-server -y
yum install php-devel php-imap php-pear php-ldap php-gd php-mbstring php-ncurses php-xmlrpc php-domxml php-mysql php-odbc
pear install DB
pear install pear/MDB2#mysql

Creating Database and Tables

Once you install these modules, next step is to create Database for PDNS. To do that login to MySQL Console using mysql root password and execute following query to create database:

mysql> create database power_admin;

You can create any user as database user for power_admin. Here I have used root.

Next Step is to create Empty tables inside this database. Following are the SQL Queries to create tables

use power_admin;

create table domains (
id INT auto_increment,
name VARCHAR(255) NOT NULL,
master VARCHAR(128) DEFAULT NULL,
last_check INT DEFAULT NULL,
type VARCHAR(6) NOT NULL,
notified_serial INT DEFAULT NULL,
account VARCHAR(40) DEFAULT NULL,
primary key (id)
);

CREATE UNIQUE INDEX name_index ON domains(name);

CREATE TABLE records (
id INT auto_increment,
domain_id INT DEFAULT NULL,
name VARCHAR(255) DEFAULT NULL,
type VARCHAR(6) DEFAULT NULL,
content VARCHAR(255) DEFAULT NULL,
ttl INT DEFAULT NULL,
prio INT DEFAULT NULL,
change_date INT DEFAULT NULL,
primary key(id)
);

CREATE INDEX rec_name_index ON records(name);
CREATE INDEX nametype_index ON records(name,type);
CREATE INDEX domain_id ON records(domain_id);

create table supermasters (
ip VARCHAR(25) NOT NULL,
nameserver VARCHAR(255) NOT NULL,
account VARCHAR(40) DEFAULT NULL
);

GRANT SELECT ON supermasters TO power_admin;
GRANT ALL ON domains TO power_admin;
GRANT ALL ON records TO power_admin;

Installation and Configuration of PDNS

PDNS can be installed using YUM or using by the RPM provided by PowerDNS. Following is the command to install PDNS using yum

yum install pdns pdns-backend-mysql

Now we have to configure PDNS to work with MySQL Server. Open file /etc/pdns/pdns.conf which is configuration file for PDNS and search for “launch=bind” and change
FROM:

#################################
# launch        Which backends to launch and order to query them in
#
launch=bind
bind-example-zones

TO:

#################################
# launch        Which backends to launch and order to query them in
#
#launch=bind
#bind-example-zones
launch=gmysql
gmysql-host=127.0.0.1
gmysql-user=root
gmysql-password=root_password
gmysql-dbname=power_admin

Save and exit from the file.

Now restart the pdns Service using command “service pdns restart”. Now check if the server is responding to port 53 i.e. Port for DNS service. You can check that using telnet i.e. “telnet localhost 53″.

If you get something like above image then pdns has been installed sucessfully on your Server/VPS.

You can go through This Article for installing and configuring PowerAdmin.

Error :-
Getting following error whenever you access PhpMyAdmin either from WHM or cPanel.

Warning: session_start() [function.session-start]: SQLite: failed to
open/create session database
`/var/cpanel/userhomes/cpanelphpmyadmin/sessions/phpsess.sdb' - unable
to open database:
/var/cpanel/userhomes/cpanelphpmyadmin/sessions/phpsess.sdb in
/usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/session.inc.php on
line 86

Fatal error: session_start() [<a
href='function.session-start'>function.session-start</a>]:
Failed to initialize storage module: sqlite (path:
/var/cpanel/userhomes/cpanelphpmyadmin/sessions/phpsess.sdb) in
/usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/session.inc.php on
line 86

Cause :-
This is usually a bug in the PhpMyAdmin configuration on cPanel server.

Solution :-
Manually edit /usr/local/cpanel/3rdparty/etc/phpmyadmin/php.ini file as follow:

1) Login to shell of the server using root login details and open the file “/usr/local/cpanel/3rdparty/etc/phpmyadmin/php.ini” using vi or any other editor.

2) Search for the [Session] variable & underneath it, change the following
Replace:
——————————-
session.save_handler = sqlite
session.save_path = /var/cpanel/userhomes/cpanelphpmyadmin/sessions/phpsess.sdb
——————————-
To:
——————————-
session.save_handler = files
session.save_path = /tmp
——————————-
By default, PhpMyAdmin uses sqlite as as the file handler, but it needs to be changed to files & the location of the session files to be stored needs to be changed to /tmp which is the standard/default place to store temporary files.

3) Once all changes done, save & exit the php.ini file & restart the apache using “/scripts/restartsrv_httpd“.

4) Also ‘chattr’ the file “/usr/local/cpanel/3rdparty/etc/phpmyadmin/php.ini” so that it doesn’t get reverted back to original whenever cPanel updates run.

CRON
Cron is a scheduling daemon process on Linux and other Unix variants, it enables unix users to execute commands or scripts (groups of commands) automatically at a specified time/date. Cron is very useful to run backup scripts.

Cron Files/Directory
/etc/crontab
/etc/cron.d
/var/spool/cron/(username)

/etc/cron.hourly
/etc/cron.daily
/etc/cron.weekly
/etc/cron.monthly

Crontab Restrictions
Cron has a built in feature of allowing you to specify who may and who may not use it. It can done by using “/etc/cron.allow” and “/etc/cron.deny” files.
If the username exist in the file “/etc/cron.allow“, that user is authorize to execute cron. If “/etc/cron.allow” file does not exist, you can use crontab if username does not appear in the file “/etc/cron.deny“. If only “cron.deny” exists and its empty, than all users can use crontab. If neither file exists, than only the root user can use crontab. The “allow/deny” files consist of one user name per line.

Crontab Options and Explanation
A crontab file has five fields for specifying time, day and date followed by the command to be run at that interval. Below is Crontab syntax,

*  *  *  *  * command to be executed
-  -  -  -  -
| | | | |
| | | | +—– day of week
| | | +——– month
| | +———– day of month
| +————— hour
+—————— minute
Minute (0-59),
Hour (0-23),
Day of the month (1-31),
Month of the year (1-12),
Day of the week (0-6 with 0=Sunday).

* Is treated as a wild card. Meaning all possible value.
*/5 is treated as ever 5 minutes, hours, days, or months. Replacing the 5 with another numerical value will change this option.
2,8,12 “,” Treated as an “OR“, so if placed in the hours, this could mean at 2, 8 or 12 o-clock. Or if put in days it would be on 2,8 and12. “,” is used to define more options [date, time, day]
6-10 Treats for any value between 6 and 10. So, if placed in day of month this would be days 6 through 16. Or if put in hours it would be between 6 and 10. “-” is used to define the range [date, time]

Crontab Commands
crontab -e Edit your crontab file, or create one if it doesn’t already exist.
crontab -l Display your crontab entries.
crontab -r Remove your current crontab entry..
crontab -u username -e This option allows you to modify or view the crontab file of a user, only super user can use this option.

What is udev?

udev – /dev/ and hotplug management daemon

udev is a daemon which dynamically creates and removes device nodes from /dev/, handles hotplug events and loads drivers at boot time. It replaces the hotplug package and requires a kernel not older than 2.6.12.

Install udev in Debian

NOTE :: After installing this you need to reboot your machine.

When you install the Udev package in VPS based on Debian-4.0 template, you can may the following message:

Setting up udev (0.105-4) …
/var/lib/dpkg/info/udev.postinst: line 125: echo: write error: Operation not permitted

The problem is postinstall script tries to write to /proc/sys/kernel/hotplug file, and this operation is prohibited in a VPS.

To work around this problem ::

What is FUSE :: Filesystem in Userspace

The FUSE kernel module and the FUSE library communicate via a special file descriptor which is obtained by opening /dev/fuse. This file can be opened multiple times, and the obtained file descriptor is passed to the mount syscall, to match up the descriptor with the mounted filesystem.

With FUSE it is possible to implement a fully functional filesystem in a userspace program.

Features include:

FUSE was originally developed to support AVFS (A Virtual File System) but it has since become a separate project.

FUSE Installation on VPS :: [FUSE Module need to be enabled on the Main Hardware Node]

If your sites are using php as the major scripting & the server load is highly around 10+, PHP Cachers like APC , Xcache or Eaccelerator can are really help to minimize the load to 1-2.

APC: The Alternative PHP Cache (APC) is a free and open opcode cache for PHP. It was conceived of to provide a free, open, and robust framework for caching and optimizing PHP intermediate code.

XCache: XCache is a open-source opcode cacher, which means that it accelerates the performance of PHP on servers. It optimizes performance by removing the compilation time of PHP scripts by caching the compiled state of PHP scripts into the shm (RAM) and uses the compiled version straight from the RAM. This will increase the rate of page generation time by up to 5 times as it also optimizes many other aspects of php scripts and reduce serverload.

Eaccelerator: It is a free open-source PHP accelerator, optimizer, and dynamic content cache. It increases the performance of PHP scripts by caching them in their compiled state, so that the overhead of compiling is almost completely eliminated. It also optimizes scripts to speed up their execution. eAccelerator typically reduces server load and increases the speed of your PHP code by 1-10 times.

Operations running 24×7 are the key objectives of business continuityin any organization. It is a best practice to  establish data centers & sites in different geographic regions with replicated applications and data using geographic redundancy.

Implementing geographic redundancy is the main task which can be done in different ways. Deploying multiple sites & using some software to duplicate the data is the significant investment. Now a days IT professionals  build redundant sites as a backup and manually manage data replication and failover to the secondary site when needed. By virtualizing data center resources at both sites, you can turn non-performing assets into an ongoing available asset.

A better practice is to virtualize your server and application resources-a much more cost effective and a better overall architecture. Instead of deploying that very expensive mid-range system, virtualize multiple, low-cost, high-performance servers with applications and data, so when one server fails you are not impacted. This gives you the opportunity to achieve high availability and performance without breaking the bank.

How to converting MYSQL table from InnoDB engine to MyISAM

I have problem of table repair in InnoDB so I need to convert InnoDB to the engine MyISAM.

I converted a MySQL table’s InnoDB to the engine MyISAM.

The code I found for conversion:

ALTER TABLE Tablename ENGINE=MYISAM

You can replace MYISAM engine to InnoDB, ISAM, HEAP and etc.