Slashdot Mirror
MySQL on Windows - Good Idea?
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?"
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.
Quality Hosting e3 Servers
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.
"Better to be vulgar than non-existent" -Bev Henson
Actually mysql works fine on windows. I've seen it being used in production and I've run it for about two years for testing purposes. Windows is quite a good choice for running mysql. You get a nice installer which makes configuration easy; there's several good mysql frontends (e.g. mysqladministrator) that make configuration easy and there's commercial support available if you need it.
....). Some of the desktop stuff actually works better on windows (e.g. firefox, eclipse).
In general, most oss stuff that makes linux popular runs on windows as well these days (quite often with very good commercial support available and user communities that dwarf their linux counterparts). Basically all of the commandline stuff is likely to already have at least an cygwin port. The more important packages generally have windows specific versions as well (e.g. apache, mysql, openoffice, firefox, python, perl, gaim, php
I'm a big OSS fan and I use windows almost exclusively. Aside from the OS and office (at work), most stuff I use is open source. I prefer linux for server environments, though, but performance or stability are not the reasons. Managability is the big reason for me.
Despite this I'm pragmatic enough to see that you don't want linux unless you have a capable sysadmin available to run it. Putting linux in an environment with a few windows wannabe sysadmins (i.e. most small companies) is just asking for trouble.
Jilles
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.
Do not meddle in the affairs of sysadmins, for they are subtle, and quick to anger.
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
"Times have not become more violent. They have just become more televised."
-Marilyn Manson
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 conections agaisnt your database you may want to look in to better codding practice.
But it work fine.
Linux modi 2.6.26-2-parisc
I don't know what the error means, I just copy and pasted from the first google hit for "mysql error"
My beef is with websites (forums, image boards) that grovel before the altar of MySQL only to have their site completely hosed for a good hour or two every day because of some error or another. When HTML bugs up, it doesn't also take out every other server in a 40-foot radius with it.
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.
I'm a big OSS fan as well, and administer Windows Servers. I also use every single platform I can get my hands on. I found from experience it can be more difficult to install most OSS Apps on windows than on what they were natively written for. A true geek can use whatever they are given. But back to the point at hand .. MySQL and Postgres runs perfectly fine on Windows. I've been running it in production for about 4 years.
...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 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.
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.
No matter the harware, keeping your database on a different machine than your public-facing web server is always a good idea.
Great ideas often receive violent opposition from mediocre minds. - Albert Einstein
What's germane to the current discussion is that MySQL for Windows does exist, runs fine and fast, and MySQL AB provide both unofficial and official support for it same as for MySQL on most other platforms.
;)
The MySQL win32 mailing list and forum are plenty active, and MySQL AB are generally quite happy to sell you paid support for your servers running their product regardless of the OS that happens to be on them. Even if it's Windows.
MySQL works pretty much the same on Windows as it does anywhere else, the one major exception to this being MySQL Cluster, which is currently supported on Linux, Solaris, and OS X only.
I'd personally rather see people switch to an OSS operating system and run MySQL on that, but that's just my 2 öre. But if you really want to run it on Windows - go for it.
Il n'y a pas de Planet B.
Depending on the features you need, you could probably get away with MS SQL Server 2005 Workgroup Edition, which is ~$800.
m pare-features.mspx
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/co
500GB of disk, 5TB of transfer, $5.95/mo