SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    faster MySQL connections on Win NT/2000/XP

    wow, i just discovered this yesterday. for testing stuff on my Win2k machine, i've always specified "localhost" as the hostname when connecting to MySQL.

    i quit using persistent MySQL connections after having weird quirks with them. then i noticed that my page creation times were slower (by about 1/100th of a second!), of course, since a new MySQL connection had to be created each time. but on my Linux host, persistent connections are disabled, and it connects to MySQL just as fast as my system did with persistent connections on. i chalked that up to MySQL being faster on *nix systems and that, by default, MySQL connections use Unix sockets.

    yesterday, in the MySQL manual i saw that on Windows if you specify "localhost," it will connect with TCP/IP, but on Win NT if you specify "." as the hostname (or leave it blank), it will connect using named-pipes. i tried it, and it connected as fast as with persistent connections before!

    here's the time it takes to connect with the different methods, according to my tests:

    Code:
    Hostname    Non-persistent    Persistent
    
    localhost       10.5ms          0.4ms
    .                0.6ms          0.25ms
    that's with MySQL's thread_cache enabled. it takes the non-persistent connections ~0.3ms longer to connect with no thread_cache.

    that's amazing, though! just changing that makes it connect 1/100th of a second faster. when it's this fast i don't care about pconnect()'s.

    the couple queries that i've checked are running 0.1-0.4ms faster now too (almost as fast as on Linux).

    it's just on my test machine and it doesn't really matter. i just like things to be super fast. so i thought i'd pass this along (in case anybody's running actual servers on Windows). to use these named-pipe connections, you have to be using NT/2000/XP and be running mysqld-nt or mysqld-max-nt.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  2. #2
    Apache Expert i_like_php's Avatar
    Join Date
    Nov 2001
    Location
    Dallas, Texas
    Posts
    1,342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i know the feeling i love the speed
    i love php

  3. #3
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    update on this: named pipes have been disabled by default in the newest versions of MySQL (since 3.23.50). i just installed 3.23.51 today (never saw 2.23.50 ) and got "Cannot connect" errors with '.'. here's what it says in the changelog:

    Because of problems on shutdown we have now disabled named pipes on windows by default. One can enable named pipes by starting mysqld with --enable-named-pipe.
    i don't know what they mean by "problems." anyway, to re-enable faster named pipes when you're running MySQL as a service, just add

    enable-named-pipe

    to your my.cnf/my.ini under the [mysqld] section.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •