According to this MySQL page here, you can win 30 to 50% more performances using MySQL jdbc named pipe!

Named pipes only work when connecting to a MySQL server on the same physical machine as the one the JDBC driver is being used on.
In simple performance tests, it appears that named pipe access is between 30%-50% faster than the standard TCP/IP access.


As default, when you install mySQL on windows using the installer, TCP IP is the default option. The question remains why nobody seems
to use named pipe, or has write any articles on internet about it. Lorenz and me were trying nearly 2 hours to make this damn things work.
We googled on internet, finding nothing, e try and try until we finally succeed. That is the reason why I write this article now.

We were testing successfuly MySQL 5.0 using named pipe, and what an increase in speed! 50% in the case of this big project
First it may be a good idea to use the latest JConnector driver from MySQL

MySQL Connector/J is a native Java driver that converts JDBC (Java Database Connectivity) calls into the network protocol used by
the MySQL database. It lets developers working with the Java programming language easily build programs and applets that interact
with MySQL and connect all corporate data, even in a heterogeneous environment. MySQL Connector/J is a Type IV JDBC driver and
has a complete JDBC feature set that supports the capabilities of MySQL.

Download Binaries & Source

Just put the file mysql-connector-java-5.0.7-bin.jar in your classpath. You'll have to sligthly alter the my.ini file as following:

[mysqld]
skip-networking
enable-named-pipe
socket=mysql.sock


Add these 3 keys in [mysqld] section of my.ini and restart MySQL. The first key switch the TCP-IP port off, so you wont be able
now to connect to port 3306. The 2 others remaining just activate MySQL named pipe.

Verify that everything has work correctly by firing MySQL Query Browser and connect to your database.
(see details of connection below)
mysql query browser with named pipe

 For the most intrepid of You of if wou want to add named pipe capabilities to previous saved connections, just can also use the menu "tools" - "manage connections" and under the tab "advanced parameters", just add these 2 new keys

NAMED_PIPE    value   Yes
SOCKET_PATH   value    mysql.sock

We are nearly finished, all we have to do is to alter now the JDBC url, and this is where we fight against Windowstm till we find the path to the mysql.sock file handle.
For the unpatient of you, I deliver here the solution:

jdbc:mysql:///database
?socketFactory=com.mysql.jdbc.NamedPipeSocketFactory
&namedPipePath=\\\\.\\Pipe\\mysql.sock


database being the database/schema name

socketFactory=com.mysql.jdbc.NamedPipeSocketFactory  JConnector also supports access to MySQL via named pipes on Windows NT/2000/XP using
the
NamedPipeSocketFactory as a plugin-socket factory via the socketFactory property. If you don't use a namedPipePath property, the default
of '\\.\pipe\MySQL' will be used. If you use the
NamedPipeSocketFactory, the hostname and port number values in the JDBC url will be ignored.
You can enable this feature using:   socketFactory=com.mysql.jdbc.NamedPipeSocketFactory

namedPipePath=\\\\.\\Pipe\\mysql.sock The path to the file socket. Notice how strange the path is looking like under Windowstm (escaping  \ in java is normal).
Under linux we would have write /var/log/mysql.sock and forget everything. In Windowstm  You really have no chance to find it until  you use
FileMon (a SysInternals tool)

FileMon monitors and displays file system activity on a system in real-time. Its advanced capabilities make it a powerful tool for exploring the way
Windows works, seeing how applications use the files and DLLs, or tracking down problems in system or application file configurations. Filemon's
timestamping feature will show you precisely when every open, read, write or delete, happens, and its status column tells you the outcome.
FileMon
is so easy to use that you'll be an expert within minutes. It begins monitoring when you start it, and its output window can be saved to a file for off-line
viewing. It has full search capability, and if you find that you're getting information overload, simply set up one or more filters.
Download it HERE

You can use FileMon to filter file by name, search for mysql* and you'll see that strange url. Note the documentation give some advice about this url
(more or less)  the default of '\\.\pipe\MySQL', would have work if we have name the file MySQL and not mysql.sock

But wait there is more to learn, MySQL is supporting a wide range of parameters when you open the connection. Just read this page, a lot of settings may also speed your application even more.
comments powered by Disqus

You might like also

Mysql database backup in Linux Crontab
Here is the easiest way to run a daily backup of your database using linux crontab. Thanks to our everyday increasing mailbox size (Thanks Gmail) and their nature to be quite safe for saving document, let’s use them to store the full backup of all our Mysql database! Requirements Having a shell access to your linux box Creating a user with limited mysql rights: SELECT and LOCK_TABLES is enough, For example a user backup_user with a password ChhdeqyqUzd75687fOnmYar Installing Mpack: …
2910 Days ago
Recovering/Resetting a MySQL root password
In this small post, I’ll show you how to reset the MySQL password in case you lost it. You’ll need at least the root access of the machine where MySQL run. Reset the MySQL root password Resetting the root password of a MySQL database in case you lost it, is really easy.  # /etc/init.d/mysql stop Now start the database in the background, via the mysqld_safe command. Start MySQL with a flag to tell it to ignore any username/password restrictions which …
3399 Days ago
MYSQL tuning with mysqlreport
I found this interesting tool (beside tuning-primer.sh) while trying to optimizing my server setting for Joomla! mysqlreport mysqlreport makes an easy-to-read report of important MySQL status values. Unlike SHOW STATUS which simply dumps over 100 values to screen in one long list, mysqlreport interprets, formats, and then nicely presents the values in report readable by humans. Numerous example reports are available at the mysqlreport web page. The benefit of mysqlreport is that it allows you to very quickly see a …
3793 Days ago
Optimizing mysql with tuning-primer.sh
Digging into mySQL settings can be time consuming, like with any other component. Most of the time, and just by changing a few settings, you can expect a performance increase. The problem is to change what, and to which value. This is where tuning-primer.sh help you: Get this script, http://forge.mysql.com/projects/view.php?id=44 upload it, unzip it, and install it in your /etc folder. Then run it from the command line by entering ./path-to-file/tuning-primer.sh MySQL Server must run a few days or weeks, …
4253 Days ago
No Thumbnail was found
One of the most important rule is to always backup your data with multiple tools on different medium, just in case of. Just imagine 5 minutes that you lose everything, what you have done in the last 3 months on your homepage..ok still not crying? you loose hundred of hours of work, nightly debugging, customizations, hours of Gimp/Photoshop, lengthy and good written articles... With Joomla! and now with nearly all CMS, the most important thing to backup is the database. …
4253 Days ago
No Thumbnail was found
I publish here some of my server settings in the hope that it will also help others... Server Setup AMD64 1 Gb RAM, Linux OSS 10.0, 7 Joomla instances (one being waltercedric.com with 250'000 unique visitors per months), 1 simple machine forums, 3 gallery2 install All MySQL tables are myISAM (table locking instead of row loacking in innodb, myIsam make sense as ther is more read then insert) # vi in /etc/my.cnf Below the diff command between a standard MySQL …
4253 Days ago