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
Lame. Very, very lame. A lame joke based on out-dated and irrelevant issues long since addressed, many of which are not unique to MySQL. Funny thing, when examples of MySQL implementations by major players in heavy load environments is brought up, the cliché wielding nay-sayers become strangely quiet.
"Who are in control, they are not in control of anything - they don't even control themselves!" - Glen Beck
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
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.
...all MySQL are belong to us!
(Yes, it's a lame joke. But seriously, friends don't let friends expose their data...)
"I don't know, therefore Aliens" Wafflebox1
Putting linux in an environment with a few windows wannabe sysadmins (i.e. most small companies) is just asking for trouble.
Having an environment with a few Windows wannabe sysadmins is asking for trouble no matter what you're running. If you can't afford one decent Linux admin to replace your 5-10 Windows admins -- for instance, you only had 1 wannabe Windows admin in the first place -- you're much better off outsourcing the whole thing anyway.
Don't thank God, thank a doctor!
As with the C#, C++, VB.NET, etc IDEs, the "Express" edition of MS SQL Server is available 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.
...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.
...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.
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.
Wonder what the public key field is for?
"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)"
often? Except for the Mozilla projects and OpenOffice I really kind of doubt that. Your right about a lot of OSS being available for Windows but I find that most still have a better linux user community.
See my blog http://ilovecookes.blogspot.com/ for light hearted technical information.
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, it doesn't even require Cygwin or anything weird like that. Runs native, runs fine, and has for years.
Dear Slashdot: next time you want to mess with the site, add a rich-text editor for comments.
...essentially the question is: what do you really want?
"Nae Kin! Nae Quin! Nae laird! Nae master! We willna be fooled again!"
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.
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
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. :-)
...laura
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.
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.
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.
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 comparing MySQL 4.1 with SQL Server 2000. MySQL 5 is meant to be a lot better than 4; SQL Server 2005 is also better but it is more of an incremental thing over 2000 compared to the advances with MySQL.
You should also consider SQL Server Express, which is free. Just bear in mind the limitations: 1 CPU, 1gb RAM, 4gb database size (data files, not including logs.) Other than this I believe it has the performance of the full version (e.g. it would be as fast as the full version running on a 1 CPU machine with 1gb RAM.)
To be honest if you are planning on just a single machine which is going to share the web server, application server and database the Express edition will likely be more than enough. The 1 CPU/1gb RAM limitation is what SQL Server is limited to use; if you have 2 CPUs/2gb RAM the rest of the system will still be using these.
The one to watch is the 4gb max database size; you would want to be a good bit below this (half?) to allow for expansion - and your application sounds like something that would potentially produce a very large amount of data to store (although maybe not as I presume you need to flush stuff out regularly to keep the info fresh.) Also, the 4gb is per database; you can have as many as you like.
You'll also be more familiar with SQL Server administration though, so staying with that might be easier than trying to migrate to MySQL now. What I would probably do myself would be to stick with SQL Server (if the free Express edition is enough) for _this_ move (you will have other issues with the move rather than trying to complicate it) and then when you have expanded to the point where you need a seperate database machine (which is very much to be recommended anyway) look into MySQL on that then. You can set up MySQL on that new machine independently while your app runs happily on the other, and only switch over when you are sure everything is working OK. MySQL (v5+, which many would think the first version that is a real DBMS) will have been around for a bit longer then as well which can only be an advantage.
To be honest performance is likely to depend a lot more on your database design, good SQL (e.g. thinking in sets, reducing queries per ASP page), proper use of indexes, appropriate caching, etc. You can speed up a badly implemented database by several orders of magnitude looking at these things, whereas either actual engine will be in the same ballpark. I'd say either DBMS will do in that regard.
It's got to be better than having an Access file on a share :)
Oh I do agree with just about everything you said except the point about the windows OSS communities. Running MySQL on windows for development kind of makes some sense since you can also run Apache and PHP on windows. For deployment I am a big fan of keeping the database server on a seperate box.
If cost is a problem then the recipe for a good cheap MySql server is this.
One old P3 800+ Mhz is good enough.
As much ram as you can afford to stick in it.
A cd-rom.
A small boot hard drive.
To good sized HDs for the database.
One copy of CentOS
Set up the two good sized drives as a RAID 1 and us that for the Database. You can use the Linux Software RAID to save some money.
This should work for MySQL, Postgres, or Firebird. I personally prefer postgres over MySQL and I have used both but that is just me. YMMV.
A minimal install of CentOS will make for a quick and inexpensive database server that should be as secure as a well run windows sever with a lot less effort. I find maintaining and updating Linux server to be easier than Windows Boxes. SSH and yum make it very easy and since Linux seems to have fewer remote exploits and is targeted less I find it more secure.
See my blog http://ilovecookes.blogspot.com/ for light hearted technical information.
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
If you are looking for a nice, free and easy way to install MySQL for Windows, then I strongly recommend XAMPP. I used it for installing my Open Source Information Asset Register, the Database of Managed Objects.
Paul Gillingwater
MBA, CISSP, CISM