Posts from — March 2009

Many PHP examples out there are designed for a linux/unix operating system. I am going to give some examples of some interesting functionality that only works with php running in a windows environment (IIS or apache).

1) Eject the CD-ROM

2) Read and write from/to the registry

parameters:

• Folder name – (key path past HKEY_LOCAL_MACHINE\SOFTWARE\\)
• key – the key name to read from

parameters:

• Folder name – (key path past HKEY_LOCAL_MACHINE\SOFTWARE\\)
• key – the key name to write to
• value – value that will be written to the key
• type – key type (default: REG_SZ)

3) register and un-register phpscripts as a windows service

# registering a service

win32_create_service(array(
’service’ => ‘myservice’, # the name of your service
‘display’ => ’sample dummy PHP service’, # description
‘params’ => ‘c:\path\to\script.php run’, # path to the script and parameters
));

# un-registering a service

win32_delete_service(’myservice’);

# code run as a service

This uses the windows API Service DLL, which is not enabled by default. Here is how to install it:

• Download the main library (it’s included in the main PECL extension download from php.net)
• extract php_win32service.dll to your ext directory (where your php extension .dlls are located)
• add the following line to your php.ini: extension=php_win32service.dll

4) print pages/data

#this is an example function that will format a host/printer name, for printing to shared printers over the network

function getPrinter($host,$SharedPrinterName) {
return “\\\\”.$host.”\\”.$SharedPrinterName;
}

#this opens the printer
$handle = printer_open(getPrinter(”my computer 2″,”my printer”));

An extensive list of functions for printing can be found here

#this is possible in *nix as well. Here is some example code

function lpr($string,$printer) {
$prn=(isset($printer) && strlen($printer))?”$printer”:C_DEFAULTPRN ;
$CMDLINE=”lpr -P $printer “;
$pipe=popen(”$CMDLINE” , ‘w’ );
if (!$pipe) {print “pipe failed.”; return “”; }
fputs($pipe,$string);
pclose($pipe);
}

This uses the windows API Service DLL, which is not enabled by default. Here is how to install it:

• Download the main library (it’s included in the main PECL extension download from php.net)
• extract php_printer.dll to your ext directory (where your php extension .dlls are located)
• add the following line to your php.ini: extension=php_printer.dll

5) List the current system processes

# list all the current processes running on the system

print_r(win32_ps_list_procs());

other related commands:

# Retrieves statistics about the global memory utilization
print_r(win32_ps_stat_mem());

# Retrieves statistics about the process with the process id pid (if no process id is given, the current process will be used)
print_r(win32_ps_stat_proc(int processid));

This uses the windows API Service DLL, which is not enabled by default. Here is how to install it:

• Download the main library (it’s included in the main PECL extension download from php.net)
• extract php_win32ps.dll to your ext directory (where your php extension .dlls are located)
• add the following line to your php.ini: extension=php_win32ps.dll

The following are five ways to improve queries involving table inserts:

1) use LOAD DATA INFILE when loading data from a text file

This is around 20 times faster than using insert statements.

2) use INSERT statements with multiple VALUES lists to insert several rows at a time

This is many times faster than using separate single-row insert statements. Tuning the bulk_insert_buffer_size variable can also make inserts (to tables that contain rows) even faster.

3) enable concurrent inserts for myisam tables

The concurrent_insert system variable can be set to modify the concurrent-insert processing. By default, the variable is set to 1. If concurrent_inserts is set to 0, concurrent inserts are disabled. If the variable is set to 2, concurrent inserts at the end of the table are allowed even for tables that have deleted rows.

4) use insert delayed

This is useful if you have clients that cannot or need not wait for the insert to complete. This is a common situation when you use MySQL for logging and you also periodically run select and update statements that take a long time to complete. When a client uses insert delayed, the server returns right away, and the row is queued to be inserted when the table is not in use by any other thread. Another benefit of using insert delayed is that inserts from many clients are bundled together and written in one block. This is much faster than performing many separate inserts.

5) lock your tables before inserting (for non-transactional tables)

This benefits performance because the index buffer is flushed to disk only once, after all insert statements have completed. Normally, there would be as many index buffer flushes as there are insert statements. Explicit locking statements are not needed if you can insert all rows with a single insert.

To obtain faster insertions for transactional tables, you should use start transaction and commit instead of lock tables.

The more complex your permissions setup, the more overhead you have. Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements.

• Remove unnecessary parentheses
• COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables. This is also done for any NOT NULL expression when used with only one table.
• If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table

This command defragments a table after you have deleted a lot of rows from it.

For MyISAM tables that change frequently, you should try to avoid all variable-length columns (VARCHAR, BLOB, and TEXT). The table uses dynamic row format if it includes even a single variable-length column.

Use insert delayed when you do not need to know when your data is written. This reduces the overall insertion impact because many rows can be written with a single disk write.

• Use INSERT LOW_PRIORITY when you want to give SELECT statements higher priority than your inserts.
• Use SELECT HIGH_PRIORITY to get retrievals that jump the queue. That is, the SELECT is executed even if there is another client waiting.

Use multiple-row INSERT statements to store many rows with one SQL statement.

Columns with identical information in different tables should be declared to have identical data types so that joins based on the corresponding columns will be faster.

The most powerful command within mysql is explain. Explain can tell you exactly what mysql is doing when you execute a query and with this information, you can find slow queries and minimize execution time, which can significantly improve the speed of your web application.

How to use the explain command

Here is a simple example of its usage:

Database Schema:

(users table)

(address table)

In this simple example, I am selecting a record that is representing a user, based on a userid.

Here is the output:

Variable	Details
	The ID of this table in the query. EXPLAIN will create one output record for each table in the query.
	possible values: SIMPLE, PRIMARY, UNION, DEPENDENT UNION, SUBSELECT, and DERIVED.
	The name of the table MYSQL will read the records from.
	The type of join that mysql will use. Possible values: eq_ref,ref,range, index, or all.
	a list of indexes (or NULL if none) mysql can use to find rows in the table.
	The name of the index MYSQL will use (after checking all possible indexes).
	The size of the key in bytes.
	The columns or values that are used to match against the key
	The number of rows mysql thinks it needs to examine to execute the query.
	extra information about the query

This example is pretty straight forward. Since we are peforming a search based on the primary key (userid), there can be only one record that can possible match (the rows variable is 1).

A more advanced example:

This query is more advanced than the first one. It is performing an inner join on the users and address table based on the userid. The userid is a primary key within the users table, but it is not an index in the address table. The output of the explain command shows us the following:

(users table)

Type: const
Possible_Keys: primary
Ref: const

(address table)

Type: all
Possible_Keys: (none)
Ref: (none)

The first table is optimized. It is using the primary key for this query. The second table, however is not optimized. The type is all and the Possible_keys=(none), which means it is going to have to go through a full table-scan. Adding an index on the user field optimizes this query.

Final output after index is added:

(users table)

Type: const
Possible_Keys: primary
Ref: const

(address table)

Type: const
Possible_Keys: primary
Ref: const

More information on this command can be found Here

It has happened to all of us running a website or application using mysql as its back-end database. Performance is suddenly very sluggish and you have no idea what is causing it. Now there may be other factors that are causing the issue (overloaded CPU, harddrive running out of space, or a lack of bandwidth), but it could also be a query that is not optimized and/or is taking much longer than it should to return.

How do you know which queries are taking the longest to execute? Mysql has built-in functionality for checking this through the slow query log.

To enable (do one of the following):

1) add this to /etc/my.cnf

log-slow-queries=/tmp/slow_queries.log
long_query_time=10

2) call mysqld with –log-slow-queries[=/tmp/slow_queries.log]

long_query_time is the maximum amount of seconds a query can take before it will be logged to the slow query log.

other related options:

–log-slow-admin-statements

Log slow administrative statements such as OPTIMIZE TABLE, ANALYZE TABLE, and ALTER TABLE to the slow query log.

–log-queries-not-using-indexes

If you are using this option with –log-slow-queries, queries that do not use indexes are logged to the slow query log.

If slow query logging has been enabled successfully, you will see “ON” in the VALUE field for “log_slow_queries” (shown above).

Note: Queries handled by the query cache are not added to the slow query log, nor are queries that would not benefit from the presence of an index because the table has zero rows or one row.

You may also run into the case where a query is slow at one time (such as when you are logging it) but not another (if you execute it manually):

• A table may be locked, causing the query to wait. the lock_time indicates how long the query waited for locks to be released
• none of the data or indexes have been cached in memory. This is common when MySQL first starts or your tables have not been optimizied
• a background process was running, making disk I/O considerably slower
• The server may have been overloaded with other unrelated queries at the same time, and there wasn’t enough CPU power to do the job efficiently

Log analysis

MySQL also comes with mysqldumpslow, a perl script that can summarize the slow query log and provide a better idea of how often each slow query executes.