FoxPro is ten times faster than MSSql
-
I/O is 99999/100000 times the slowest part of a DB system.
This is a major reason why 500MB is a big smell to me on a query that's taking an hour. But it also sounds like there are much deeper structural issues with the DB.
-
The point was to keep them all the same to help compare, and because MSSQL will slurp up all the ram until there's nothing left for the other's
...wait, so you're running all of them on the same server at the same time? I'm no DB expert either, but this doesn't seem right...
-
The 5 databases we test on all support ANSI JOINs, and ODBC provides syntax to support outer joins in the rare occasion that we need that.
Wut? Outer joins are part of the ANSI standard.
-
I've never used a DB that goes beyond 3NF (I even automatically design them in 3NF); I really don't see the advantage in going beyond that.
This. 3NF is good enough. Anything above is usually someone fresh out of university and/or jerking off. Or a really really special snowflake database.
-
Anything above is usually someone fresh out of university and/or jerking off.
Do you use lookup tables (Code, Description)? Those aren't in 3NF...
-
This is a major reason why 500MB is a big smell to me on a query that's taking an hour. But it also sounds like there are much deeper structural issues with the DB.
I think I mentioned before that it was because MSSQL didn't like the string concat in the lookup, which was in there for a Foxpro speed boost, and happened to work acceptably on other DBs. It took just as long when running it on a production server, solely dedicated to MSSQL (32gb ram, SSD, rackspace somethingarather). Changing it to be a proper, non-concat lookup sped it up for everyone (except Foxpro, so that gets to keep the old query)
Everyone seems to be hung up on the 500MiB ram thing (even though mentioned repeatedly that MSSQL isn't even trying to use all of it - which is a pain in the butt) so I'll see if I can rearrange my schedule a bit and get our benchmark tester loaded up on a production-equivalent server this week, with a dataset much larger than the tiny 18GiB we play with. Then we can get some production-equivalent numbers.
-
Wut? Outer joins are part of the ANSI standard.
https://msdn.microsoft.com/en-us/library/ms714641.aspx has more info about it. It's mostly for older, crappier ODBC drivers *coughfoxprocough*
-
MSSQL isn't even trying to use all of it
It might have certain size increments and 500MB might be just a bit under the point where it can take the next step up.
OTOH, if the speed difference is persisting with more memory (and it might well be that) it's almost certainly due to something else. As if something somewhere is doing a linear table scan when every other DB is happy to use an index; even if the table is entirely in memory, having to scan each time in some inner loop would suck. I've never tuned a MSSQL deployment so I can't really help too much.
-
Everyone seems to be hung up on the 500MiB ram thing (even though mentioned repeatedly that MSSQL isn't even trying to use all of it - which is a pain in the butt)
Yeah, this just makes it sound like you are doing some kind of insane shit. Which goes along with the string concat stuff.
-
ODBC smooths over a lot of the implementation quirks
Yeah, and it also prevents you from using most of the nice features and performance that databases offer.As I see it, you have 2 major problems:
- a total lack of knowledge about the different databases
- odbc and this standard-sql nonsense
You can improve it in 2 ways:
- get an expert in, choose one type of database, use it's features and phase out odbc, or
- stick with what you know, i.e. FoxPro
Summary: You're doing it wrong!
-
Everyone seems to be hung up on the 500MiB ram thing
Is it really 500MiB, or is it 512MiB i.e. 0.500GiB? Inquiring minds must know.
-
Everyone seems to be hung up on the 500MiB ram thing
Well, I'd argue that's because your original comment of "Each database is restricted to 500 meg of ram just to see how it performs in equal conditions" is grossly flawed reasoning and the assertion sticks out like a sore thumb. Simply put, there's a tremendous difference between SQLite being restricted to 500MB and SQL Server being restricted to 500MB. Calling that "equal conditions" is like putting a indy car and a sprint car on a dirt track and calling that a fair test. [Note: I mean no disrespect to SQLite. It's a very good DB. It's just that MS SQL Server not built to compete with SQLite.] The minimum for SQL Express is 500MB, and the recommendation is 1GB. For other versions, the minimum is 1GB and the recommendation is 4GB. (These are Values that haven't changed from SQL Server 2008 to 2016.) SQLite is built to be run on as little as a 4KB stack and 100KB heap. Expecting them to perform comprably in a 500MB environment is absurd.
I wouldn't be quick to blame MS's handling of ODBC, assuming you're using a modern ODBC driver. Microsoft won't even update the Ole DB Driver anymore because they're moving everything to ODBC. The current ODBC driver is the driver you're supposed to use now for new development.
Bottom line is that you're seeing very weird performance issues with an RDBMS that should perform as well as or better than several others on your list. This suggests you're confuguration is seriously messed up, or you're doing something truly bizarre. In other words, TRWTF ain't SQL Server.
-
As an aside - How large is your database on disk? RAM is cheap, and if you can just load the whole damn thing into RAM, you'll be set.
I was taught to do this as a rule of thumb. There's a very good chance your data fits in RAM, anyhow.
-
There's a very good chance your data fits in RAM, anyhow
My data doesn't fit.
Not that I actually give a fuck. The metadata fits trivially (except we split it over several servers so that we can have specialist tools that understand what it means) and the data usually only needs to be processed by a few tools.
-
Damn, 6tb per server. I'm impressed. Now buying one is likely never happening.
http://www.dell.com/en-us/work/shop/productdetails/poweredge-r920
-
http://www.dell.com/en-us/work/shop/productdetails/poweredge-r920
i do not think those are the servers you are looking for....
-
Ur doin it wron.
-
i clicked the link you posted. that's what i got.
don't blame me if your link doesn't go to the right place for all users!
:fakeanger:
-
$10k doesn't seem all that bad for such a beefy server.
-
$10k doesn't seem all that bad for such a beefy server.
It probably depends on what you configure it with. That price sounds like it is with minimal memory; I didn't find prices for the r920 as that's now effectively listed as “$Call”, but the r930 when configured with 1.5TB is around $32k. A quick finger-in-the-air guess indicates that I'd expect to pay around $120k or so for 6TB…
-
As @dkf said, that's with one CPU and like 32GB memory.
With 4 cpus and 6tb memory it might be a bit more expensive.
-
But think about it - with a full setup, we could finally get two nines on Discourse!
-
280.000 USD.. Damn..
no disks,nic or power cord, only the required 4 cpus and 96 sticks of 64 GB ram and 2x1100w PSUs
-
Damn, 6tb per server. I'm impressed. Now buying one is likely never happening.
http://www.dell.com/en-us/work/shop/productdetails/poweredge-r920
At my previous job, we bought eight HP DL980s with 4TB of RAM to make an MSSQL cluster.
http://www.it-hisco.com/UploadFiles/HP_DL980_G7.jpg
We replaced over seven hundred servers with these eight.
-
That's fucking impressive, must have saved a lot of money, just in power/hosting costs
-
But think about it - with a full setup, we could finally get two nines on Discourse!
I'd be happy with one nine on Discourse.
-
You'd be happy with 9%?
-
We replaced over seven hundred servers with these eight.
Nice!
must have saved a lot of money, just in power/hosting costs
Ease of administration would be another saving. Cutting even one FTE can save a huge amount over a few years.
-
We replaced over seven hundred servers with these eight.
Nice.
These cases are where even Azure might not be able to beat dedicated hardware. Though given the SQL Server license rabbit hole...
Azure G5 Instance running SQL Enterprise runs about $6500 / month. That's a 500GB RAM and 6TB SSD storage. And no calls back and forth for weeks between MSFT license reps and your procurement team (and then when you need a new server, you just spin up a new VM). For the largest applications, probably doesn't suffice, though in those cases I'd seriously consider scaling horizontally instead of vertically.
-
Though given the SQL Server license rabbit hole
When you are paying 280k usd for a server, what's another 110k in sql server licensing ? /s
(7k USD per core 4 cpus with 4 cores each)
-
what's another 110k in sql server licensing
At a minimum. This is one thing I love - people will buy a cheap $6000 server and then run $14000 software on it. I've seen SQL server 1 TB databases with 32 GB of ram. Insanity.
-
Because 32 GB of ram was max in standard maybe? And standard is "only" 2k/core now IIRC
-
32 GB of ram was max in standard maybe
I thought it was 64 (SQL 2014 changed it to 128!). 32 was probably the server admin's team "default". God forbid you treat a database server differently from a file and print server.
-
And no calls back and forth for weeks between MSFT license reps and your procurement team (and then when you need a new server, you just spin up a new VM).
We got special treatment from MS. We reported the number of production licenses needed once per year and paid that. We got an agreement in writing that we could use MSSQL for non-production purposes free of charge without having to do anything special like MSDN licensing.
As for license costs, the best processors to run MSSQL on don't come in low core counts. My most recent implementation was on Xeon 2690s, which are 10 cores each. To get the best bang for your licensing buck, you have to be very careful about processors now that they went to per-core licensing instead of per-processor.
We actually go to the point of running VMware, but locking one OS instance to the physical box, all so we can mask out cores for licensing purposes. As an instance scales up, we gradually add cores (4 at a time) to keep licensing costs under control.
-
I've seen SQL server 1 TB databases with 32 GB of ram. Insanity.
That doesn't sound very insane to me. Depending on what the database is being used for, obvs.
-
I suppose if the queries are quite selective and are time boxed to only the past month of data... But for queries that need to do heavy joins or sorts in memory, last thing you want is to spill to disk. Also you want as much data as you can sitting in the buffer...
-
As for license costs, the best processors to run MSSQL on don't come in low core counts. My most recent implementation was on Xeon 2690s, which are 10 cores each. To get the best bang for your licensing buck, you have to be very careful about processors now that they went to per-core licensing instead of per-processor.
When does it become economical to change RDBMS? You can do a lot of dev on say postgres for the ~550K USD it costs to license a 8 cpu server (at list prices - i presume bigger companies pay way less)
-
I've seen a lot of (basically) write-only databases used for logging purposes. 32 GB of memory is actually overkill for that use, 99.9% of the time. (The 0.1% of the time you actually want to pull data out, well. That can be a problem.)
-
@Jaime said:
As for license costs, the best processors to run MSSQL on don't come in low core counts. My most recent implementation was on Xeon 2690s, which are 10 cores each. To get the best bang for your licensing buck, you have to be very careful about processors now that they went to per-core licensing instead of per-processor.
When does it become economical to change RDBMS? You can do a lot of dev on say postgres for the ~550K USD it costs to license a 8 cpu server (at list prices - i presume bigger companies pay way less)
I have two answers for this.At mega-big-company that I used to work at, we were the MSSQL team and the corporate policy already was "don't use MSSQL unless it's the only choice". Under this policy, we ended up with nearly a thousand installs of MSSQL mainly because many people purchased software that required MSSQL. So, someone may have been doing this math, but if they chose something else, they disappeared from our radar.
At the current job, I'm involved in a lot more of the big picture. We usually buy SQL Standard. If we need more RAM, we just run multiple instances on the same processors - it doesn't add to the licensing cost. We've yet to run into a situation where a single database required more than 64GB of RAM. SQL Server costs us a little over $50 per core per month. To fully license one of our big boxes (two ten core XEON 2690s) it costs about $1000 per month, or around $40k over the life of the machine. That's much less than the cost of the hardware, so changing platform would require the alternate platform to not require any additional hardware and for the switching costs to be pretty damn low.
In my experience, MSSQL requires less hardware for most big general purpose loads than all of the open source alternatives out there. It also has excellent management features that make it less expensive to manage.
-
nearly a thousand installs
we just run multiple instances on the same processors
And that doesn't cause performance problems?about $1000 per month, or around $40k over
the life of the machine
That does change the equation significantly.
-
@Jaime said:
we just run multiple instances on the same processors
And that doesn't cause performance problems?
No, it solves them. Most of our boxes are limited by RAM, not CPU. So, if we only installed one instance of SQL, the CPU would rarely get over 10%. Most of our boxes have 384GB of RAM, so it takes at least six instances of SQL 2012 Standard to use the RAM on the box.
-
Pretty much every SQL box I ever saw was limited by I/O first, RAM second, CPU last.
-
Well then you need me! I only do cartesian joins, we don't want that poor cpu to feel useless, right?
-
Our test system that we do the benchmarking against is only 13GiB, it's pretty small compared to the live systems.
I'm amused somewhat by the observation I can make here: back when I worked for a large provider of financial information services, they had (dozens of) machines where that 13 GiB would fit into RAM a dozen times over and still leave loads of room for the OS. Their total data set increased in size by, someone said, about 4 GiB a day.
-
Pretty much every SQL box I ever saw was limited by I/O first, RAM second, CPU last.
I skipped I/O because we put all data on SANs and we can scale that pretty much infinitely. It's still a typical bottleneck due to cost, but the point was that RAM almost always bottlenecks before CPU. Since SQL is commonly licensed by CPU, the key is to get as much work as you can out of every core you license.
-
boo to SAN! ISCSI needs to die a painful death. Ok supposedly you can get great performance. But I've never seen it in practice. Usually you end up arguing with the storage people nonstop because they don't understand how starved DBs are for IO and anyway we didn't do capacity planning for your new database....
In your case, if you have full control, then more power to ya :)
-
Did you get this done?
Tbh I don't know anything at all about foxpro, it's just one of those technologies I hear people talking shit about all the time, like php.
-
boo to SAN! ISCSI needs to die a painful death.
All our important SQL deployments are clusters, so SAN is a given. iSCSI is OK if you use the proper dedicated NICs. However, those cost more than FibreChannel cards and defeat the whole point of iSCSI anyways. All of my deployments are FC - I get good enough performance from our EVA and 3PAR SANs. Whenever they put something on one of the no name SANs, it comes back to bite us.
AlwaysOn High Availability is great, but it's not worth the price hike to go to SQL Enterprise to get all the bells and whistles. Also, it's sometimes a pain to use when an application insists on creating its own database. Clustering "just works".
-
Tbh I don't know anything at all about foxpro, it's just one of those technologies I hear people talking shit about all the time, like php.
Unlike PHP, FoxPro development stopped ten years ago. The last release was in 2004 and the last service pack in 2007. It was good enough that Microsoft bought it so they could use its technology to speed up MS Access. The plan was to buy it, incorporate the good parts into Access, get all the users to switch to Access, then retire it. Microsoft retired it, but the users refuse to let go.
-
I've just finished up moving our benchmarking rig to The Cloud last night and was waiting for the first batch of result times to come back before commenting. The new rackspace server comes with quad core Xeons, 4GiB ram and some SSD drive (a massive step up from the dual core 32bit Pentium something it used to be running on), it also has a newer version of MSSQL (v12... which is 2014?) with all the latest service packs. I haven't yet updated MySQL and Postgres to the newer versions yet, so they're still running Postgres 9.4 (one version older than latest) and MySQL 5.6.23 (very old now!). It'd also be interesting to try with the latest MSSQL (v14 2016?) for comparison.
There was, unsurprisingly, across the board improvement. All of the benchmarking used to take 6 hours to run on the old server, and only 2 hours on the new server. I removed the ram limits on the new server. The new benchmarking times (to the nearest second) were:
- Foxpro 613s
- MySQL 847s
- MSSQL 1,382s
- SQLite 945s
- Postgres 617s
We have had an MSSQL contractor working with us for the past few weeks, mostly to set up some 'real world' benchmarking (simulating load, rather than just blindly running each report once like the benchmarking I had set up) but he's also been looking into some of our bottlenecks that we're having with MSSQL. The main one is that the cursors we were using were being inefficient (something about running
sp_cursoron the server a lot), by switching to forward-only cursors we were now running the queries on the server rather than one cursor lookup per row - maybe that makes more sense to MSSQL peopleSo switching to forward-only cursors gives us about a 20% improvement on MSSQL from what we've seen so far, but it's at the penalty of rewriting all our queries to make sure that all TEXT columns are the last columns in the SELECT, a not-small undertaking - and also means we can't count up the number of results using ODBC functions, we have to do a separate
SELECT COUNT(*)query. So we are testing speeds with forward-only cursors too, but because there's too many errors the numbers aren't very reliable.
