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)
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.
|
Tags
