Posts Tagged ‘ MySQL ’

Sphinx Search Installation

Sphinx search introduction

507110_binocular

After reading my introduction to full text search or you have read article somewhere else and decided to go with full text search in your next project, but you still confuse what full text search engine to use. One implementation of full text search engine is Sphinx. And I’ll give you a short course on how you installing Sphinx for your full text search engine.

Sphinx is a full-text search engine, distributed under GPL version 2. It is not only fast in searching but it is also fast in indexing your data. Currently, Sphinx API has binding in PHP, Python, Perl, Ruby and Java.

Sphinx features

  • high indexing speed (upto 10 MB/sec on modern CPUs);
  • high search speed (avg query is under 0.1 sec on 2-4 GB text collections);
  • high scalability (upto 100 GB of text, upto 100 M documents on a single CPU);
  • provides good relevance ranking through combination of phrase proximity ranking and statistical (BM25) ranking;
  • provides distributed searching capabilities;
  • provides document exceprts generation;
  • provides searching from within MySQL through pluggable storage engine;
  • supports boolean, phrase, and word proximity queries;
  • supports multiple full-text fields per document (upto 32 by default);
  • supports multiple additional attributes per document (ie. groups, timestamps, etc);
  • supports stopwords;
  • supports both single-byte encodings and UTF-8;
  • supports English stemming, Russian stemming, and Soundex for morphology;
  • supports MySQL natively (MyISAM and InnoDB tables are both supported);
  • supports PostgreSQL natively.

There you go, so fire up your terminal or console, and let’s get thing done.

Installing sphinxsearch

  1. Download sphinx at sphinxsearch.com, for this tutorial, I use Sphinx 0.9.8.1
    $wget http://sphinxsearch.com/downloads/sphinx-0.9.8.1.tar.gz
  2. Open your terminal, extract and install sphinx
    $tar -xvf sphinx-0.9.8.1.tar.gz
  3. sphinx need mysql-dev install, if you use ubuntu linux install this
    $sudo apt get install libmysqlclient15-dev
  4. Install sphinx to your system
    $cd sphinx-0.9.8.1/
    $./configure
    $make
    $sudo make install

    Note if you want to use sphinx with PostgreSQL, configure with this argument –with-pgsql

    $./configure --with-pgsql
  5. Test your installation

    $search

    This should come up in your terminal

    Sphinx 0.9.8.1-release (r1533)
    Copyright (c) 2001-2008, Andrew Aksyonoff
    
    Usage: search [OPTIONS] [word2 [word3 [...]]]
    
    Options are:
    -c, --config 	use given config file instead of defaults
    -i, --index 	search given index only (default: all indexes)
    -a, --any		match any query word (default: match all words)
    -b, --boolean		match in boolean mode
    -p, --phrase		match exact phrase
    -e, --extended		match in extended mode
    -f, --filter  	only match if attribute attr value is v
    -s, --sortby 	sort matches by 'CLAUSE' in sort_extended mode
    -S, --sortexpr 	sort matches by 'EXPR' DESC in sort_expr mode
    -o, --offset 	print matches starting from this offset (default: 0)
    -l, --limit 	print this many matches (default: 20)
    -q, --noinfo		dont print document info from SQL database
    -g, --group 	group by attribute named attr
    -gs,--groupsort 	sort groups by
    --sort=date		sort by date, descending
    --rsort=date		sort by date, ascending
    --sort=ts		sort by time segments
    --stdin			read query from stdin
    
    This program (CLI search) is for testing and debugging purposes only;
    it is NOT intended for production use.

Well done. You have Sphinx at your service. But before you can play with this full text search engine you have just installed, you have to understand how Sphinx works.

Sphinx installed 4 program in your environment, but most of the time we will only use indexer, search and searchd. To begin with, we have to create an index for our source. Let’s create a file name sphinx.conf, and here is a sample of sphinx.conf look like.

source book
{
    type            = mysql
    sql_host        = localhost
    sql_user        = root
    sql_pass        = root
    sql_db          = library
    sql_port        = 3306# optional, default is 3306
    sql_query       = SELECT id, title, summary, author from library
    sql_query_info  = SELECT * FROM library_book WHERE id=$id
}

index book
{
    source          = book
    path            = data/book
    docinfo         = extern
    charset_type    = sbcs
}

indexer
{
    mem_limit       = 32M
}

searchd
{
    port            = 3312
    log             = log/searchd.log
    query_log       = log/query.log
    read_timeout    = 5
    max_children    = 30
    pid_file        = log/searchd.pid
    max_matches     = 1000
}

For more information about sphinx configuration, please go to sphinx documentation.

Create log folder for our searchd log file and another folder named data for our index data. Run indexer to index our database.

$mkdir log
$mkdir data
$indexer --all
Sphinx 0.9.8.1-release(r1533)
Copyright (c) 2001-2008, Andrew Aksyonoff

using config file ./sphinx.conf'...
indexing index 'book'...
collected 12 docs, 0.0 MB
sorted 0.0 Mhits, 100.0% done
total 12 docs, 10319 bytes
total 0.018 sec, 571436.48 bytes/sec, 664.53 docs/sec

You can use search program to test search index you have just created. Assuming you have book with title contain PHP in your database, then run search PHP will give you some results.

$search PHP

Create subdomains using PHP?

subdomainform.php

<html>
<head>
<title>create a subdomain</title>
</head>
<body>
<form name “form1” method “post” action “addsubdomain.php”>
<h1>create Sub Domains</h1>
Sub Domain Name : <input type “text” name “subdomain”><br />
<input type “submit” name “Submit” value “Submit”>
</form>
</body>
</html>

addsubdomain.php

<?php

ini_set(‘display_errors’ 1);

$host “domainname.com”; // your domain name without the www
$port 2082;
$path “/frontend/x/subdomain/doadddomain.html?domain “.$_POST[‘subdomain’].”&rootdomain “.$host; //or .dll etc. for authnet etc.

// these lines are changed
$cpaneluser “username”;
$cpanelpass “pass”;
$authstr “$cpaneluser:$cpanelpass”;
//****************************

// Setup the Auth String
$pass base64_encode($authstr);

$fp fsockopen($host $port $errno $errstr $timeout 30);

if(!$fp){
//error tell us
echo “$errstr ($errno)n”;

}else{

//send the server request

fputs($fp “POST $path HTTP/1.1rn”);
fputs($fp “Host: $hostrn”);
fputs($fp “Authorization: Basic $pass rn”);
fputs($fp “Content-type: application/x-www-form-urlencodedrn”);
fputs($fp “Content-length: “.strlen($poststring).”rn”);
fputs($fp “Connection: closernrn”);
fputs($fp $poststring . “rnrn”);

//*************************************
// Remove this to stop it from displaying the output fron the CPanel
//*************************************
//loop through the response from the server
/*
while(!feof($fp)) {
echo fgets($fp 4096);
}

*/
while(!feof($fp)) { fgets($fp 4096); }
//close fp – we are done with it
fclose($fp);
}

*****************************

  1. In you main httpd.conf add the following line at the bottom:Include /path/to/subdomains.conf

file name subdomains.conf

@mkdir ( /path/to/subdirectory/ .$name );

  1. $vhost
  2. ServerAdmin yourname@yourserver.com
  3. DocumentRoot /path/to/subdirectory/ .$name.
  4. ServerName .$name. .yourserver.com
  5. ErrorLog /path/to/logs/ .$name. _error_log
  6. ;
  7. $fo fopen ( /path/to/subdomains.conf a );
  8. $fw fwrite ( $fo $vhost );
  9. fclose ( $fo );

******************************

Bookmark and Share

Upload files using PHP

Consider the html form:

<html>
<body>
<form action=”upload_file.php” method=”post” enctype=”multipart/form-data”>
<label for=”file”>Filename:</label>
<input type=”file” name=”file” id=”file” size=”20″ /><br />
<input type=”submit” name=”submit” value=”Submit” />
</form>
</body>
</html>

Script for saving the uploaded file://upload_file.php
<?php
if (file_exists(“upload/” . $_FILES[“file”][“name”]))
{
echo $_FILES[“file”][“name”] . ” already exists. “;
}
else
{
move_uploaded_file($_FILES[“file”][“tmp_name”],”upload/” . $_FILES[“file”][“name”]);
echo “Stored in: ” . “upload/” . $_FILES[“file”][“name”];
}
?>

The script above checks if the file already exists, if it does not, it copies the file to the specified folder.This example saves the file to a new folder called “upload”

 

 

MySQL Improved Extension (mysqli)

Overview

This section provides an introduction to the options available to you when developing a PHP application that needs to interact with a MySQL database.

What is an API?

An Application Programming Interface, or API, defines the classes, methods, functions and variables that your application will need to call in order to carry out its desired task. In the case of PHP applications that need to communicate with databases the necessary APIs are usually exposed via PHP extensions.

APIs can be procedural or object-oriented. With a procedural API you call functions to carry out tasks, with the object-oriented API you instantiate classes and then call methods on the resulting objects. Of the two the latter is usually the preferred interface, as it is more modern and leads to better organised code.

When writing PHP applications that need to connect to the MySQL server there are several API options available. This document discusses what is available and how to select the best solution for your application.

What is a Connector?

In the MySQL documentation, the term connector refers to a piece of software that allows your application to connect to the MySQL database server. MySQL provides connectors for a variety of languages, including PHP.

If your PHP application needs to communicate with a database server you will need to write PHP code to perform such activities as connecting to the database server, querying the database and other database-related functions. Software is required to provide the API that your PHP application will use, and also handle the communication between your application and the database server, possibly using other intermediate libraries where necessary. This software is known generically as a connector, as it allows your application to connect to a database server.

What is a Driver?

A driver is a piece of software designed to communicate with a specific type of database server. The driver may also call a library, such as the MySQL Client Library or the MySQL Native Driver. These libraries implement the low-level protocol used to communicate with the MySQL database server.

By way of an example, the PHP Data Objects (PDO) database abstraction layer may use one of several database-specific drivers. One of the drivers it has available is the PDO MYSQL driver, which allows it to interface with the MySQL server.

Sometimes people use the terms connector and driver interchangeably, this can be confusing. In the MySQL-related documentation the term “driver” is reserved for software that provides the database-specific part of a connector package.

What is an Extension?

PHP’s mysqli Extension PDO (Using PDO MySQL Driver and MySQL Native Driver) PHP’s MySQL Extension
PHP version introduced 5.0 5.0 Prior to 3.0
Included with PHP 5.x yes yes Yes
Comes with PHP 6.0 Yes Yes Yes
MySQL development status Active development Active development as of PHP 5.3 Maintenance only
Recommended by MySQL for new projects Yes – preferred option Yes No
API supports Charsets Yes Yes No
API supports server-side Prepared Statements Yes Yes No
API supports client-side Prepared Statements No Yes No
API supports Stored Procedures Yes Yes No
API supports Multiple Statements Yes Most No
Supports all MySQL 4.1+ functionality Yes Most No

In the PHP documentation you will come across another term – extension. The PHP code consists of a core, with optional extensions to the core functionality. PHP’s MySQL-related extensions, such as the mysqli extension, and the mysql extension, are implemented using the PHP extension framework.

An extension typically exposes an API to the PHP programmer, to allow its facilities to be used programmatically. However, some extensions which use the PHP extension framework do not expose an API to the PHP programmer.

The PDO MySQL driver extension, for example, does not expose an API to the PHP programmer, but provides an interface to the PDO layer above it.

The terms API and extension should not be taken to mean the same thing, as an extension may not necessarily expose an API to the programmer.

What are the main PHP API offerings for using MySQL?

There are three main API options when considering connecting to a MySQL database server:

  • PHP’s MySQL Extension
  • PHP’s mysqli Extension
  • PHP Data Objects (PDO)

Each has its own advantages and disadvantages. The following discussion aims to give a brief introduction to the key aspects of each API.

What is PHP’s MySQL Extension?

This is the original extension designed to allow you to develop PHP applications that interact with a MySQL database. The mysql extension provides a procedural interface and is intended for use only with MySQL versions older than 4.1.3. This extension can be used with versions of MySQL 4.1.3 or newer, but not all of the latest MySQL server features will be available.

Note: If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead.

The mysql extension source code is located in the PHP extension directory ext/mysql.

For further information on the mysql extension, see MySQL.

What is PHP’s mysqli Extension?

The mysqli extension, or as it is sometimes known, the MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer. The mysqli extension is included with PHP versions 5 and later.

The mysqli extension has a number of benefits, the key enhancements over the mysql extension being:

  • Object-oriented interface
  • Support for Prepared Statements
  • Support for Multiple Statements
  • Support for Transactions
  • Enhanced debugging capabilities
  • Embedded server support

Note: If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use this extension.

As well as the object-oriented interface the extension also provides a procedural interface.

The mysqli extension is built using the PHP extension framework, its source code is located in the directory ext/mysqli.

For further information on the mysqli extension, see Mysqli.

What is PDO?

PHP Data Objects, or PDO, is a database abstraction layer specifically for PHP applications. PDO provides a consistent API for your PHP application regardless of the type of database server your application will connect to. In theory, if you are using the PDO API, you could switch the database server you used, from say Firebird to MySQL, and only need to make minor changes to your PHP code.

Other examples of database abstraction layers include JDBC for Java applications and DBI for Perl.

While PDO has its advantages, such as a clean, simple, portable API, its main disadvantage is that it doesn’t allow you to use all of the advanced features that are available in the latest versions of MySQL server. For example, PDO does not allow you to use MySQL’s support for Multiple Statements.

PDO is implemented using the PHP extension framework, its source code is located in the directory ext/pdo.

For further information on PDO, see the PDO.

What is the PDO MYSQL driver?

The PDO MYSQL driver is not an API as such, at least from the PHP programmer’s perspective. In fact the PDO MYSQL driver sits in the layer below PDO itself and provides MySQL-specific functionality. The programmer still calls the PDO API, but PDO uses the PDO MYSQL driver to carry out communication with the MySQL server.

The PDO MYSQL driver is one of several available PDO drivers. Other PDO drivers available include those for the Firebird and PostgreSQL database servers.

The PDO MYSQL driver is implemented using the PHP extension framework. Its source code is located in the directory ext/pdo_mysql. It does not expose an API to the PHP programmer.

For further information on the PDO MYSQL driver, see MySQL (PDO).

What is PHP’s MySQL Native Driver?

In order to communicate with the MySQL database server the mysql extension, mysqli and the PDO MYSQL driver each use a low-level library that implements the required protocol. In the past, the only available library was the MySQL Client Library, otherwise known as libmysql.

However, the interface presented by libmysql was not optimized for communication with PHP applications, as libmysql was originally designed with C applications in mind. For this reason the MySQL Native Driver, mysqlnd, was developed as an alternative to libmysql for PHP applications.
PHP’s mysqli Extension PDO (Using PDO MySQL Driver and MySQL Native Driver) PHP’s MySQL Extension
PHP version introduced 5.0 5.0 Prior to 3.0
Included with PHP 5.x yes yes Yes
Comes with PHP 6.0 Yes Yes Yes
MySQL development status Active development Active development as of PHP 5.3 Maintenance only
Recommended by MySQL for new projects Yes – preferred option Yes No
API supports Charsets Yes Yes No
API supports server-side Prepared Statements Yes Yes No
API supports client-side Prepared Statements No Yes No
API supports Stored Procedures Yes Yes No
API supports Multiple Statements Yes Most No
Supports all MySQL 4.1+ functionality Yes Most No
The mysql extension, the mysqli extension and the PDO MySQL driver can each be individually configured to use either libmysql or mysqlnd. As mysqlnd is designed specifically to be utilised in the PHP system it has numerous memory and speed enhancements over libmysql. You are strongly encouraged to take advantage of these improvements.

Note: The MySQL Native Driver can only be used with MySQL server versions 4.1.3 and later.

The MySQL Native Driver is implemented using the PHP extension framework. The source code is located in ext/mysqlnd. It does not expose an API to the PHP programmer.

Comparison of Features

The following table compares the functionality of the three main methods of connecting to MySQL from PHP:
PHP’s mysqli Extension PDO (Using PDO MySQL Driver and MySQL Native Driver) PHP’s MySQL Extension
PHP version introduced 5.0 5.0 Prior to 3.0
Included with PHP 5.x yes yes Yes
Comes with PHP 6.0 Yes Yes Yes
MySQL development status Active development Active development as of PHP 5.3 Maintenance only
Recommended by MySQL for new projects Yes – preferred option Yes No
API supports Charsets Yes Yes No
API supports server-side Prepared Statements Yes Yes No
API supports client-side Prepared Statements No Yes No
API supports Stored Procedures Yes Yes No
API supports Multiple Statements Yes Most No
Supports all MySQL 4.1+ functionality Yes Most No

PHP’s mysqli Extension PDO (Using PDO MySQL Driver and MySQL Native Driver) PHP’s MySQL Extension
PHP version introduced 5.0 5.0 Prior to 3.0
Included with PHP 5.x yes yes Yes
Comes with PHP 6.0 Yes Yes Yes
MySQL development status Active development Active development as of PHP 5.3 Maintenance only
Recommended by MySQL for new projects Yes – preferred option Yes No
API supports Charsets Yes Yes No
API supports server-side Prepared Statements Yes Yes No
API supports client-side Prepared Statements No Yes No
API supports Stored Procedures Yes Yes No
API supports Multiple Statements Yes Most No
Supports all MySQL 4.1+ functionality Yes Most No
Bookmark and Share

PHP5 new features

1. PHP 5 introduces the Standard PHP Library (SPL), which provides a number of ready-made classes and interfaces.
2. Access Modifiers are added in PHP5
3. PHP5 has built-in exception classes that makes it very easy to create your own customized exceptions through inheritance.
4. PHP 5 introduces the mysqli (MySQL Improved) extension with support for the features of MySQL databases versions 4.1 and higher. So use of prepare statements are allowed
5. PHP5 comes with PDO which is a common interface for different database systems is only made possible by the new object model.
6. SQLite is a database engine incorporated in php5 which can be used to develope faster, leaner and more versatile applications.
7. In PHP 5 all Extensible Markup Language (XML) support is provided by the libxml2 XML toolkit.
8. The reflection classes included in PHP 5 provide ways to introspect objects and reverse engineer code.
9. In addition to built-in classes, PHP 5 also offers built-in interfaces. Iterator is the most important, as a number of classes and interfaces are derived from this interface.
10. PHP 5 introduces a number of new “magic” methods. Magic methods begin with a double underscore, and this requires changing any user-defined methods or functions that use this naming convention.
11. The major change to PHP in version 5 relating to OOP is usually summed up by saying that objects are now passed by reference.

 

Sphinx Search Engine Performance

The following is a summary of some real-world data collected from the Sphinx query logs on a cluster of 15 servers. Each server runs its own copy of Sphinx, Apache, a busy web application, MySQL and miscellaneous services.

The dataset contains 453 million query log instances from 180 Sphinx indexes, collected over several months, using Sphinx version 0.9.8 on Linux kernel 2.6.18. The servers are all Dell PowerEdge 1950 with Quad Core Intel® Xeon® E5335, 2×4MB Cache, 2.0GHz, 1333MHz FSB, SATA drives, 7200rpm.

Keep in mind, though, that this is real world data and not a controlled test. This is how Sphinx performed in our environment, for the particular way we use Sphinx.

The graph below displays the response time distribution for all servers and all indexes, and shows, for example, that 60% of queries complete within 0.01 secs, 80% within 0.1 secs and 99% within 0.5 secs. Response times tend to occur in 3 bands (corresponding to the peaks in the frequency graph) – <0.001 sec, 0.03 sec and 0.3secs, which partly relates to the number of disk accesses required to fulfil a request. At 0.001 sec, all data is in memory, while at 0.3 secs, several disk accesses are occurring. Whilst the middle peak is not so obvious in this graph, the per-server or per-index graphs often have different distributions but still tend to have peaks at one or more of these three bands.
Sphinx Query Response Times Total for all servers, all indexes

The next observation is that query word count affects performance, but not necessarily in proportion to the number of query words, as shown in the graph below. 1-4 word queries consistently offer best performance. The 6-50 words range is consistently the slowest, most likely because the chance of finding documents with multiple matches is high so there is extra ranking effort involved. Above 50, there is presumably a higher chance of having words with few matches, which speeds up the ranking process.
Sphinx Query Response Time by Query Word Count

Finally, we see that the size of the inverted index (.spd files) also affects performance. The three graphs below show how the response time distribution tends to move to the right as the index size increases. The larger the index, the higher the chance that data will need to be re-read from disk (rather than from Sphinx-internal or system buffers/cache), hence this is not unexpected.
Sphinx Query Response Times for Index Sizes 1MB - 3MB
Sphinx Query Response Times for Index Sizes 3MB - 30MBSphinx Query Response Times for Index Sizes >30MB

Here is a PDF summary of Sphinx performance for this dataset, including many additional graphs of the data by server and by index.

Install Sphinx on Ubuntu

apt-get update
apt-get install libmysql++-dev make gcc+ g++

Go to a directory of your choosing

Download The Sphinx Binary (latest as of this writing)
http://sphinxsearch.com/downloads/sphinx-0.9.8.1.tar.gz

tar zvxf sphinx-0.9.8.1.tar.gz

cd sphinx-0.9.8.1

./configure

make

make install (you will need superuser rights for this last step)

The binaries will be install in /usr/local/bin

/usr/bin/install -c ‘indexer’ ‘/usr/local/bin/indexer’
/usr/bin/install -c ‘searchd’ ‘/usr/local/bin/searchd’
/usr/bin/install -c ‘search’ ‘/usr/local/bin/search’
/usr/bin/install -c ‘spelldump’ ‘/usr/local/bin/spelldump’

some config files will drop into /usr/local/etc

/usr/bin/install -c -m 644 ‘sphinx.conf.dist’ ‘/usr/local/etc/sphinx.conf.dist’
/usr/bin/install -c -m 644 ‘sphinx-min.conf.dist’ ‘/usr/local/etc/sphinx-min.conf.dist’

or

tar xvzf sphinx-0.9.8.1.tar.gz
cd sphinx-0.9.8.1/
./configure --with-mysql-includes=/usr/include/mysql --with-mysql-libs=/usr/lib/mysql

 Make and Install Sphinx Run the standard linux commands to install Sphinx.
make
sudo make install
Bookmark and Share