Server Admin

Website Server Administration

MySQL DELAYED INSERT and MySQL DELAYED UPDATE

Overview

Performing MySQL queries on your websites or server can cause long delays in a page loading or script executing while the MySQL INSERT or MySQL UPDATE finishes executing. This causes long delays for the user or possibly delays the rest of a script from running until the MySQL query completes (or errors out). Generally the delay on mysql inserts or update is minimal however on large complex queries with lots of join across multiple tables and millions of rows of data (or badly indexed tables), there will be a significant pause while the query completes.

Solution to Long Queries

If there is no need for the user to see the result of the query, such as queries that are involved with logging statistics or building hidden reports, then it would be highly recommended to use mysql delayed insert or mysql delayed update syntax. Using MySQL DELAYED INSERT and MySQL DELAYED UPDATE to send the queries into the queue and run in the background allowing the page or script to continue executing. It is simple to use and can be implemented by adding the `delayed` keyword after INSERT or UPDATE command and the sql query will be queued by the MySQL server to be run in the background and the page while continue loading while the sql query is executed in the background as the example below shows:

MySQL DELAYED INSERT

MySQL Insert statements can be run in the background by using the following syntax:

INSERT DELAYED `some massive time consuming query`;

MySQL DELAYED UPDATE

MySQL Update statements can be run in the background by using the following syntax:

UPDATE DELAYED `some massive time consuming query`;

Conclusion

Now the page will continue to load while the MySQL server continues to execute the query which can drastically speed up page loading speeds or script execution times. Bear in mind that if the user needs to see the result, then you won’t be able to use mysql delayed inserts.

Get Last Day Of Month MySQL And PHP

In order to get last day of month in mysql or php, you can use built in functions. As you will soon see below it is a rather straight foward task. I will show you two examples on how to get the last day of the month in mysql as well as php. In php you can use the date() function and in mysql we will make use of the last_day() method.

Get last day of the month PHP

PHP has a date function which returns a string representation of the date formatted according to the expression passed to it. So the simple way is to use the ‘t’ identifier in the date function which will return the last day of the month.

echo date('Y-m-t',strtotime('2014-01-12')); // echo's 2014-01-31
echo date('t M Y'); // echo's 31 Jul 2014

Get last day of month MySQL

MySQL has a function called last day that will return the last day of the date supplied to it. It can be the date of the current month or any other month.

SELECT last_day(CURDATE() - INTERVAL 1 MONTH) AS last_month;
SELECT last_day(CURDATE()) AS current_month; 
SELECT last_day(DATE('2014-03-12')) AS march_month

Conclusion

As you can see, getting the last day of the month is a simple task. If you get stuck, let me know in the comments.

What is eAccelerator for PHP

Overview

Before we can understand how eAccelerator (http://eaccelerator.net/) will help speed up your websites loading speed, it is important to realise how php scripts are executed and run to power your websites.

Executing PHP Scripts

When you create a php script to do amazing things like pulling information out of a MySQL database or connecting to a web service to perform complex authentication or other wondrous things, the php script must be compiled into byte-code before it can be run by the web server. The result of this is that each time a user accesses that specific php script, usually through your website, that script is then compiled and then executed. When multiple people access the same script, it is being recompiled over and over which can slow down your website and essentially puts an extra bit of strain on the web server it is being run on.

What is eAccelerator for PHP

Now this is where eAccelerator comes into play. eAccelerator is a php extension that you install onto a web server. After it is installed, each php script will then automatically be precompiled the first time they are executed and also stored for later. Now each time the script is accessed, the pre-compiled script is executed without the need of having to compile the script first.

Interested in installing eAccelerator? I have created a quick tutorial on how to install eAccelerator on Debian here.

Install eAccelerator On Debian Linux

After trying eAccelerator and finding out how great and simple it is, I decided to write a quick guide to show you how to install eAccelerator for PHP. If you are unsure on what eAccelerator is, you can view my post describing what it does and how it can drastically improve your website page generation speed.

Step 1 – Pre-requisites

Now to begin installing eAccelerator for php, make sure you have php4 or php5 installed along with autoconf, automake, libtool and m4. Running the below command appeared to installs these programs for me without any issues but ymmv.

apt-get install libtool m4 php5-dev make

If you get the error phpize command not found then make sure you installed php5-dev. It will also install automake and autoconf which are all required to install eAccelerator.

Step 2 – Get the latest version of eAccelerator

Download the latest version from the offical eAccelerator website and extract the contents to a folder such as your home directory and then CD to the extracted folder.

Step 3 – Building and install eAccelerator

Next step is to Install eAccelerator On Debian Linux. In order to do so, run the following commands commands. If you get any errors such as command not found, then ensure you have followed step 1

phpize
./configure
make

Pay attention to any output to make sure there are no errors before running the command:

make install

Step 4 – Edit your php.ini

Add the following lines to your php.ini file. Your php.ini is usually under /etc/php5/apache2/php.ini. You can run the php command “” to see where your php.ini file is located.

extension="eaccelerator.so"
eaccelerator.shm_size="16"
eaccelerator.cache_dir="/tmp/eaccelerator"
eaccelerator.enable="1"
eaccelerator.optimizer="1"
eaccelerator.check_mtime="1"
eaccelerator.debug="0"
eaccelerator.filter=""
eaccelerator.shm_max="0"
eaccelerator.shm_ttl="0"
eaccelerator.shm_prune_period="0"
eaccelerator.shm_only="0"
eaccelerator.compress="1"
eaccelerator.compress_level="9"

Step 5 – Create the cache directory and restart apache2

Create the cache directories. This is were your compiled scripts will be held. My apache user is www-data so replace www-data with the user your apache is run under.

mkdir /tmp/eaccelerator
chown www-data.www-data /tmp/eaccelerator
chmod 0744 /tmp/eaccelerator
apache2ctl graceful

Step 6 – Check the installation

Check to make sure eAccelerator is installed and running by executing the php function “” and scroll down to the modules section and look for eAccelerator.