Forgot your password?
typodupeerror
Databases Programming Software

MySQL on Windows - Good Idea? 61

Posted by Cliff
from the sensible-use-of-resources dept.
mikeballer asks: "We currently run our website from a shared hosting environment, with ASP and MS SQL Server. We will be moving to a dedicated host, and to save money, we are considering transitioning to MySQL while remaining in a Windows environment. I had read the Windows-vs-Unix section of the MYSQL documentation, but what is Slashdot's perspective on the performance of MySQL in a Windows environment?"
This discussion has been archived. No new comments can be posted.

MySQL on Windows - Good Idea?

Comments Filter:
  • by WebHostingGuy (825421) * on Wednesday January 18, 2006 @12:14PM (#14500105) Homepage Journal
    While I believe MySQL works better and has more support for the *nix platform you should have no major problems with running MySQL on Windows. I have seen it done lots of times without problems.
    • While I believe MySQL works better and has more support for the *nix platform you should have no major problems with running MySQL on Windows. I have seen it done lots of times without problems.

      I agree, and in addition I'd like to add that MS Access' GUI is freaking painful to use. I took a course in it once...

      "OK click here, here and here, and type this here while keeping in mind case sensitivity"
      "Or I could just type in the SQL..."
      "SQL doesn't do that!"
      *remote access into professor's insecure computer*
      "It
      • "in addition I'd like to add that MS Access' GUI is freaking painful to use."

        Not really -- not if you don't want to have to keep a dozen tables straight in your mind and text it all out.

        I generally use Access's GUI to mock up my joins and otherwise in MySQL -- of course, you STILL have to know what you are doing because the Access code will not work out of the box 9 times out of 10 on a complicated query but it will get you close enough that the rest is pretty easy to solve.

        Then again, most of the nerds I'v
        • You can't keep the tables straight in your head? Maybe you should stop naming them "table1", "tbl2", etc and use meaningful names - it's not like the length of table/column names slows SQL down or anything...

          Yeah, the relationship view thingie is half handy, but only half, and only if you didn't create a proper ER diagram for this huge complicated database beforehand like you and/or the developer / DBA should have. Yes, if it's too big to keep in your head, it oughtta be written down. In almost any situa
          • The problem I have, is that I'm dealing with several hundred tables -- a lot of which are almost redundant but that they have ONE piece of information different than the other.

            I have to collate data from 8 college campuses, including legacy and current systems -- and it'd be really nice if there were diagrams for this, but it would require administrators from these varying departments and campuses to all be in agreeance about what they need and don't need and how its presented.

            It would be much easier if I w
            • So you use access to "join" data from a bunch of disparate databases, probably mostly designed by people who didn't know what they were doing. You know that an SQL "join" isn't something you do across multiple databases on multiple systems, right? Sure, the verb's the same, but it's not the same thing... If you'd used "collate" to begin with, it would've made more sense.

              Also, working at an academic institution is not the real world. I've worked in IT for a college - it's very much *not* real-world. ;)
              • No -- I use Access to organize my thoughts and nothing else -- sometimes its paper and pen drawing lines. Sometimes its notepad or otherwise after I've dumped the data defs.

                I pull in small extracts of the tables so that I can see what the fuckups are and where the problems are and use it to see how I'm going to write it out for the real applications (which I 'handcode' from the quick and dirty). Its simply a visualization tool as much as VisualBasic or RealBasic are RAD tools (that should never be used to
                • Ohhhh.... That makes more sense (Access as a rapid protyping tool). :)

                  My University IT comment was more directed to the amount of politics involved than the level of technology, if that helps put it in perspective at all. Though, there are certainly several with crummy technology, too.
    • I'll second that. Our database is fairly light usage (in the relative sense), but with MySQL 4 or 5 and the latest admin tools (Administrator, Query Browser) it's very smooth and efficient.

      ...a hell of a lot better than MSSQL, at any rate.
    • ...no major problems with running MySQL on Windows. I have seen it done lots of times without problems.

      I took a MySQL training course last August, and the rented computer training facility (in Portland, Oregon) only had Windows XP available. So we had to run MySQL on Windows XP, whether we liked it or not.

      Not one of the people in the class had ever used MySQL on XP, so there was lots of head scratching over where Windows hid things. We compared notes on our favourite Linux distros and read Slashdot. :

  • I use it (Score:3, Informative)

    by SocialEngineer (673690) <invertedpanda@[ ]il.com ['gma' in gap]> on Wednesday January 18, 2006 @12:14PM (#14500110) Homepage

    I've used it for testing and whatnot, as well as running on dedicated servers (had to have it running in our techlab at college, which is Windows only). In both environments it performs fairly well, however, I must say that we weren't really putting much load on it.

  • by alta (1263) on Wednesday January 18, 2006 @12:48PM (#14500463) Homepage Journal
    I read the doc you mentioned and that 4000 port limit thing seems like it may be killer, depending on the type of connection you have. I know a lot of hosting companies sell both Linux AND windows servers. Have you considered keeping a windows server for the ASP and getting a linux box to run SQL? You'd also have the advantage of seperate servers/seperate functions.
    • ...about the 4000 port issue; your application should be keeping the database connections open rather than closing and reopening constantly anyway. So you should only be using a handful, for whatever number of concurrent connections there are to the DB.

      Note: I don't use ASP myself and I don't actually know how ASP handles this. I _presume_ it doesn't only have an open and close repeatedly option, because that would be braindead.
    • Another possibility is to get a single Windows box with a shit-ton of RAM. 1GB for general testing and maybe 4GB for production. Also get at least 6 physical drives divided between 3 seperate RAID controllers configured for RAID1. Redundant power supplies would be nice, but only if your environment also provides seperate power feeds in a rack.

      Install whatever OS you are comfortable with. Regardless of what Lin or Win fanboys think, you'll get more uptime and beter security with an OS you are used to usi
  • by Randolpho (628485) on Wednesday January 18, 2006 @12:51PM (#14500511) Homepage Journal
    I can't really quote performance stats; I've never dealt with a server that gets enough traffic to make a difference.

    What I can tell you is that there is a major, important consequence to using MySQL for Windows rather than *nix. It stems from the way MySQL stores table data: the name of the table is the filename of the file used to store that table. Ditto for database names.

    On Windows, filenames are case insensitive. The filename "MySQL" is the same as "mysql" is the same as "MysqL", etc. Consequently, table and database names on MySQL windows servers are case insensitive. Case is preserved on *nix, because filenames are case sensitive.

    That's not to say that you shouldn't use MySQL for Windows, just be prepared for portability issues if you happen to migrate from *nix.

    MySQL documentation on the subject [mysql.com]
    • Actually NTFS (filesystem that Windows NT uses) can be case sensitive. It is that by default shell and kernel is configured to be case insensitive. This is due to POSIX compatibility which requires case sensivity.
    • if they're moving FROM sql server TO mysql, then they shouldnt have much of a problem. AFAIK, SQL server doesnt support two tables with the same name, regardless of case. and if they keep their data in tact, case should be preserved. its really not a big deal. actually doesn't MySQL still search a table no matter what case you use? like if you do "select * from releases" it will search the Releases table if its there? i would hardly call it a "major, important consequence", assuming they are half decent dat
  • two cents (Score:2, Interesting)

    by ghinckley68 (590599)
    Having run MySql under both winows2000/2003 and Linux. I prefer Linux. The only real problem i run into is case senistivity, when running on windows i can be more lazy ie

    given a table called UserNames

    under windows i can do select * usernames where as unde *nix i have to UserNames. No big deal just my only problem. Now as for as 4000 socket limit if you run up agisnt it move to db2 or Oracle on a RISC box, x86 hardware is not going to deal with a load like that. Also if you have that many concurent conectio
    • The easiest way to avoid problems with case sensitivity is to use a consistent case rule. The easiest one, from a typing point of view, is "all identifiers are lower case". This seems to be the general rule in all the MySQL examples I've seen, too, so you at least have convention on your side...

      As for the Windows/Linux thing, I'm just starting to use MySQL, and haven;t noticed any real differences. However, I am just running a couple of little websites, not Slashdot or the IRS.
    • Are you typing while drunk, or surfing slashdot from your cell phone?
  • It works pretty well (Score:3, Interesting)

    by rabbit994 (686936) on Wednesday January 18, 2006 @01:27PM (#14501000)
    You may need to modify the my.ini to get better performance out of it but otherwise, MySQL seems to work about the same on both Windows and linux. If your moving from a shared environment to dedicated environment, you might still be able to get MSSQL out of your dedicated server provider for a decent price. Just don't get lazy about the lack of case sensitivy and 4000 connection limit, I wouldn't worry about, if you hit it, you either need to redo your sloppy code and look at upgrading to a beefier DB software.
  • ...all MySQL are belong to us!

    (Yes, it's a lame joke. But seriously, friends don't let friends expose their data...)
  • DROP DATABASE
     
    You cannot drop a database that is in use by some thread.
    ... that in Linux I can drop an open MySQL database?
    • Does this mean...

      DROP DATABASE

      You cannot drop a database that is in use by some thread.

      ... that in Linux I can drop an open MySQL database?

      I think that you are being a smartass (which I respect and it is in keeping with Slashdot tradition), but just in case, the answer would be no.

  • Avoided (Score:1, Redundant)

    by hords (619030)
    We had bad luck with a Windows NT server hosting about 50 websites and running MySQL databases. It was slower than pulling out of Iraq! Man that Pentium 150 processor was smokin!

    Ok, bad example. Haven't tried it since, lol. (Hey, it ran ok on a simular linux server!)
  • As with the C#, C++, VB.NET, etc IDEs, the "Express" edition of MS SQL Server is available [microsoft.com] for free. Since I have no idea what your database usage is like, I can't say whether MS SQL Server Express will meet your needs, but it is worth looking at since it would certainly be easier to transition to than MySQL as you're already using SQL Server.

    • MS SQL Server Express is just the database engine. That's it. It comes with a very basic configuration GUI, so you will be doing all database management directly through SQL or other Microsoft API functions. I tried the Express edition and actually couldn't figure out how to make Access connect to it, let alone make a database.

      You'll save money but you won't save time or effort.

  • by blorg (726186) on Wednesday January 18, 2006 @04:55PM (#14503644)
    ...number one probably being what is your code division between SQL and ASP, e.g. how much of your code is SQL and how much is ASP? Number two would then be whether you use any SQL-Server specific features or other SQL that isn't supported (or doesn't work the same) on MySQL. So the first thing for you to do is to test your application on MySQL and see if it works, (highly unlikely off the bat) or if it doesn't, work out how much fixing is required, and how much will this cost (time/money). This is not specific to a move to MySQL, it would be the same going between any two DBMSes.

    If you are moving from a shared environment, I presume you aren't massively high volume but you should bear in mind that using ASP with MySQL you will have to go through ODBC which will have a performance penalty. With SQL Server you can use a native driver as I believe you can if you use MySQL with certain application servers other than ASP.

    Also remember you can move entirely to Linux while still using ASP [sun.com] if you want.

    You should also look at what you are storing in your database - is it highly transactional, updated continually with absolutely essential information (I am thinking orders/financial transactions) or is it mainly SELECTs on data that is updated infrequently. With the former, data integrity should be top of your shopping list while with the latter you just need to make sure that you back up regularly and you shouldn't lose anything important even in case of a disaster. MySQL 5 is meant to be much better on this matter and many other issues that were problematic for MySQL in the past but bear in mind that v5 is only out a few months.

    Bottom line is - if you have a relatively low-traffic website with relatively simple code, moving shouldn't be too much of a problem. If you have a high-traffic website with complex SQL, moving will likely cost more than a SQL Server license. BTW, SQL Server is a decent database, I wouldn't move off it just for the heck of it.
    • The site is a price comparison engine for books, movies, music, and video games ( http://getcheapbooks.com/ [getcheapbooks.com] ). The site receives about 200k visitors (2M hits) per month. The code split is probably 80% ASP, 20% SQL. For each new (not cached) price comparison, we do insert the price data into the database, so the database access is really a combination of inserts and selects.

      I am actually not that concerned about converting the SQL code, but I am really just concerned about performance. Thanks for the comm
      • I am actually not that concerned about converting the SQL code

        I'm kind of curious about this. A MS-SQL Internet license runs around $2500. That's only about one week of a database developer's time. Unless you were very careful when writing the application, that cost is easily going to get soaked up in QA and development.

        As for performance, there's some load testing packages out there which could simulate your user load on your application on your hardware. But then again, they cost a lot more than MS-SQL do
      • So you do have constant inserts, but you are basically just using the database as a cache. In that case it really wouldn't matter at all if you had to go back to last night's backup, as material would just be cached again as queries came in. So MySQL would look like a good fit there.

        It's difficult to find up-to-date comparisons as the latest versions of both (SQL Server 2005 and MySQL 5) are quite new, but here's an example [databasejournal.com] comparing MySQL 4.1 with SQL Server 2000. MySQL 5 is meant to be a lot better than 4
  • If there is a reason to run it on the same computer, there shouldn't be a problem. I would recommend a dedicated linux server, though.
  • by Halvard (102061) on Wednesday January 18, 2006 @05:51PM (#14504285)
    So why not run MySQL under Cygwin or use a VM and run Linux and/or Windows under the VM. That way one machine is two and you get to separate the services.
  • ... but my company runs several substantial Intranet sites off one Windows box (2k server, IIRC) and all are MySQL-backed. (I myself have been using this box for about 3 or 4 years.) And it's not just reads to build content for the front page--lots of surveys, calendars, inventory databases, etc. If you're concerned about performance, test and benchmark. Overall, I'd say it's fine. It's not like there's some inherent 500% benefit you get from running MySQL under *nix. And, unlike older versions of Postgres,
  • ...essentially the question is: what do you really want?
  • I do web development on the side and I use MySQL and PHP running off of Windows' IIS (WIMP). Only problems I see with portability is making sure the username/password and DB name for the database gets changed between my test environment and the production environment (Linux in most cases) and (in one instance) the code works with an earlier version of PHP that was never upgraded by the hosting provider.
  • by Omega1045 (584264) on Wednesday January 18, 2006 @07:25PM (#14505015)
    At a past company I worked at, we ran ASP pages on an NT machine which connected to MySQL databases on a Linux machine. We used dual NICs on the NT machine, one that was connected to the database machine on a privately addresses network and one faced into the DMZ. This seemed to work fairly well as we were able to use an older server with Linux. All we really needed was a minimal Linux install and plenty of RAM and disk. Back then we also used IPFW to firewall up the database machine. It only allowed SSH connections from a few IP addresses, and database port connections only from the web server. I thought it performed well, but this was a few years ago. I guess a modern solution would probably use something faster that the full-duplex 100 mb NIC we were using. You could probably hook up gigabit over copper with something as simple as a crossover cable from the NT machine to the Linux machine.

    No matter the harware, keeping your database on a different machine than your public-facing web server is always a good idea.

  • Mysql on Windows? .......

    While I would prefer to be running Postgresql on Linux/UNIX, i can personally attest to the performance of Postgresql on Windows.

    I started using Postgresql on Windows when the 8.0 beta releases were coming out. Wow, talk about stable and mature! What was labeled beta software, felt like a stable version instead.

    Rather than going with gotchas, go with Postgresql.
  • by Bazman (4849)
    It's got to be better than having an Access file on a share :)
  • Too expensive? (Score:3, Informative)

    by PornMaster (749461) on Thursday January 19, 2006 @04:01PM (#14512387) Homepage
    Depending on the features you need, you could probably get away with MS SQL Server 2005 Workgroup Edition, which is ~$800.

    Recoding takes time and introduces risk. It's up to you to evaluate those against the $800.

    Here's the edition matrix, in case you're interested.
    http://www.microsoft.com/sql/prodinfo/features/com pare-features.mspx [microsoft.com]
  • If you are looking for a nice, free and easy way to install MySQL for Windows, then I strongly recommend XAMPP. [apachefriends.org] I used it for installing my Open Source Information Asset Register, the Database of Managed Objects. [sourceforge.net]

You can bring any calculator you like to the midterm, as long as it doesn't dim the lights when you turn it on. -- Hepler, Systems Design 182

Working...