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?"
Nothing on Windows is a good idea :-). Conversely MySQL on anything is a fair idea, though I admit to a Postgres tendency. If any of that meant something to you, you are sick.
I have nothing to hide. So, why are you spying on me?
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
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.
...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
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 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?
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
Depending on how big your database is, SQL Server 2005 Express Edition may be a 'free' alternative. While there is nothing wrong with switching to MySQL ( I mean hey, this is /., if I didn't give OSS a plug, well... ), if you want to stay with MS technology, while not having to pay for the DBMS, then this might fit the bill. The only drawback with this solution is that you're basically limited to a 4GB database. Try it out, you might like it.
We're all hypocrites. We all have hidden parts, it's the contrast between them that make us more a hypocrite than others
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.
I've benchmarked my MySQL servers with Quest's Benchmark Factory using the TPC-C and Scaleable Benchmark Tests and find very little, if any, varying results on my WIN and Linux (Red Hat) boxes. MySQL is very easy to use on WIN and runs very well! And no slam on MS - I like SQL Server...
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 :)
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