h a l f b a k e r yQuis custodiet the custard?
add, search, annotate, link, view, overview, recent, by name, random
news, help, about, links, report a problem
browse anonymously,
or get an account
and write.
register,
|
|
|
To explain the concept that I am proposing, I need to explain a similar but different technology.
Today 3d graphics cards are extremely cheap and common. Their origin has it's history in graphics libraries like Open GL. As 3D software routines became more standard, so these routines were hardwired
into the first 3d graphics cards. Now what makes this technology interesting is twofold.
1st - The incredible rendering speed increase. (Hardware versus software rendering)
2nd - The scalability - Not all cards can do the same. Some can do more of the rendering than others. Your budget determines your performance.
So here is my suggestion:
Hardwire the most commonly used SQL algorithms into a chip and putt it into a SQL expansion board (SQLEB).
Transact SQL is the standard Database technology today. Microsoft SQL, Oracle, MYSQL etc. use mostly the same SQL syntax and share standard language compatibilities such as SQL-92.
Algorithms such as B-tree and common indexing methods are well established and are used in all SQL engines.
Here are some characteristics of this proposed technology:
1. The most common SQL algorithms are hardwired into the SQL Expansion Boards (SQLEB). Systems such as DMA are used to do high speed data transfer to and from the card.
2. Multiple cards should be able to co-exist. Why? To handle high volume parallel SQL transaction loads. For example: Just like multiple processors in a SQL Server will increase the per second transaction handling capability, so multiple SQL Expansion Boards (SQLEB) would also provide better performance.
3. Just as better algorithms and hardware methods were developed over time for the 3D graphics industry, so an initial set of algorithms should be hardwired, and over time new methods and technologies would emerge to improve performance further. The clients budget would determine which card/s they can afford.
4. The SQL server engine (MYSQL, ORACLE, MSSQL etc.) will need to use a common set of libraries (like a DirectX for SQL – how about a SQLCOMMON library), so that these can either be software emulated or hardware executed. You don’t need buy in from all vendors, just one. If the technology proves successful the market would shift to it eventually anyway.
Another idea would be to create SQL Memory (SQLMEM) cards for specific use by SQL engines. To elaborate, memory (RAM) is much faster than hard disk. If you could run the transaction log files of a SQL database in RAM, the speed increase would be significant for databases with high update and insert volumes. Now the problem is if you were to lose power to the PC server then the SQL transaction log would be lost and the database integrity would be compromised. This is why you do not currently get Log devices in RAM in products like MS-SQL for example. But what if you had a card that had a cash battery (Like the technology used in SAN systems). If the SQL engine was SQLMEM card aware for example, the first let’s say 500 megabytes of a transaction log for a certain production database could be allocated to the SQLMEM card and the rest to hard disk. The SQL write performance increase could be significant.
Now perhaps this suggestion is hot air, and perhaps it could take SQL performance to a new level, I don’t know but I do know this though: Even if it was viable, it would probably cost hundreds of millions of dollars to develop and would take a number of years to get a workable model.
Well it doesn’t mean much now, so maybe it’s meant for the future…..
Kind regards
Etienne de Villiers
South Africa
Already Done
http://sbc.winsyste...cts/sbcs/index.html
There are many makers of SBCs and designers frequently offload SQL and other functions onto them. [Ronx, Jan 01 2007]
NetDMA will make it more unbiquitous.
http://www.microsof...guy/cg0606.mspx#EEE
[Ronx, Jan 01 2007]
http://www.firebirdsql.org/
[pertinax, Jan 02 2007]
MySQL
http://dev.mysql.com/downloads/
MySQL is the most common freeware SQL with over 6 million users on many platforms. There is a commerical version too. [Ronx, Jan 02 2007]
[link]
|
| |
I have to agree with the penultimate paragraph; it would be an expensive thing to develop. However, I can see that there would be a lot of organisations who would benefit from this sufficiently to make the high cost of the cards worthwhile. [+] |
|
| |
Hey there,
Thanks for all the feedback so far. Here's some comments to the posts: |
|
| |
To Ronx:
Hi Ron, I checked out single board computers. Pretty amazing. It could cut the 'proof of concept' hardware costs quite significantly. Truly amazing development systems.
I also checked out NETDMA.
Thanks a bunch. |
|
| |
To David S:
Yep, it would probably take a large chunk out of Oracle or Microsoft's SQL development budget. They'd have to sell much more Office 2007 and Oracle business suites to compensate :-) |
|
| |
To 21 Quest:
Hey, thanks for the welcome. I saw halfbakery for the first time yesterday. What a neat idea.
------ |
|
| |
So tomorrow it's back to the grindstone for me, so i'll only come and check for some more posts in a day or two. Thanks again to all. |
|
| |
Q: Do you know of any fully open source SQL engines. I guess you know why I'm asking ;o) |
|
| |
Firebird (see link)? It's open source, but I'm not sure what you mean by 'fully' open source. You've already mentioned MySQL = is that not 'fully' open enough? |
|
| |
No matter how good the SQL engine and
how specialised you make your add-on
cards, I think the limiting factor is how fast
you can read the data from the disk on
which it is stored. |
|
| |
st3u is exactly right. I have done multiple timings for work -- you might save something like 0.001% of your speed doing this. |
|
| |
The other 99.999% of your speed is taken finding the data the query is after. Your best bet is simply more memory, faster disks, less data, and making sure you use and have the best indexes. |
|
| |
update stats being run will also help you on your way. And my secret tip of the week -- if you really have confidence in your front end, and your DBAs can be trusted -- turn off all your databases RI!!! If your front end is doing its job and nothing is trying to c*ck data up -- this'll give you maybe a 'free' 30% speed upgrade. |
|
| |
(I am used to working on databases with 6000 tables, some with maybe 25,000,000 rows in them. Obviously your personal DVD collection database would enjoy a much more significant speed increase) |
|
| |
Final word -- SQL memory? Just buy the fastest memory you can and software 'assign it' to the SQL engine. That way if you feel the urge in 3 years time, you can go back to using it as 'normal memory'! |
|
| |
By the way welcome here, didn't mean to come down on you or anything just decided to join discussion .. so welcome here - stick around! |
|
| |
I agree with [britboy], you don't need special SQL memory. You just need a dedicated machine hosting your SQL engine. |
|
| |
I see there are many justified critical ideas against the concept of a SQL plug-in card. That is good. Every idea needs to be tested for it's merit, don't you agree? Makes a person consider ideas more carefully. |
|
| |
Well anyhow, I do have some possible answers. So here goes ;-) |
|
| |
To Pertinax on Open Source SQL: Pertinax, you are quite correct that MYSQL is fully open source. I did not phrase the requirement correctly. Here is the dillemma. MYSQL do not allow alternate distributions of their software (According to my knowledge). In other words it's not like Linux that allows your own distributions. Close co-operation and buy in of MYSQL AB would be required to allow the distribution of such software modifications. So developing a SQLCOMMON library using the MYSQL source would probably invite litigation. |
|
| |
Your suggestion of Firebird sounds pretty neat. They are a non-profit organization (As far as I can see) and not a commercial venture. I suspect they'll be much more willing to co-operate (allowing the usage of their source) on a project of such a nature. If the project is worth while :-) |
|
| |
Oh by the way here is a footnote from the Firebird site which I quite like:
The software is completely free of any registration, licensing or deployment fees. It may be deployed freely for use with any third-party software, whether commercial or not. |
|
| |
(They don't mention source usage but I figure they might be more inclined to help) |
|
| |
Anyhow thanks for the firebird suggestion. |
|
| |
P.S The next post will be about the SQLMEM card critisisms. |
|
| |
Now about the SQLMEM card idea. |
|
| |
An OLTP scenario without a SQLMEM card:
Lets assume that that your SQL server is top of the line. 4 X Itanium 64 processors, 16 Gigs of RAM, Data Disks in Raid5, Log systems in mirror (All fibre channel). If you were running MS SQL2005 I believe they benched it at 1 million transactions per minute. Pretty fast. Swooosh! |
|
| |
Well you work for Paypal, Amazon or a huge bank. An organization that has a lot of transactions going through per second. The transactions are also time sensitive. In other words. The SQL system needs to react sub second (Like credit card transactions etc.) |
|
| |
I'll assume were using an MS-SQL engine. MSSQL uses a two stage commit for INSERT and UPDATE transactions. The first writes the Data to the log files. This is sequential writes, hence the usage of mirrored disks for optimization. The final write to the data disks are done using a process called Lazy writer.
This is done at regular intervals. (Checkpoints etc.) After the lazywriter has written the data to the DATA disks, the log file is truncated.
NOTE that transactions are viewed as COMPLETED when it is commited in the transaction log file, NOT the data file (The Lazywriter process does this at leasure :-) |
|
| |
So the OLTP transaction speed will be directly proportional to the speed that the data can be written to the log disks. Right? |
|
| |
So can any speed increase be achieved with such a mega machine?
Perharps. |
|
| |
An OLTP scenario with a SQLMEM card:
The same hardware as in the above scenario, save the SQLMEM plug-in card which is now installed. Microsoft SQL is now SQLMEM card aware through some magic co-operation between halfbakery and Microsoft which produces the SQLCOMMON Library :-) |
|
| |
After you plug the card in, you configure the Log file of the OLTP database to have a log file of 500 Megabytes on the SQLMEMCARD (So the transaction log-file is now in memory),the second log file is your mirrored disk as normal. |
|
| |
The machine is put back into production. As the INSERT and UPDATES commands are processed they are written to the first log file, in memory. No longer is transaction completion dependant on physical disk writes but on MEMORY SPEED! Swoooooosh. WARP SPEED MR.SULU |
|
| |
Note1: Now the reason you can't just go and create a virual hard disk in conventional memory and create the log-file there is because if you were to lose power to your megamachine, the log files are lost and you have an instant suspect database. |
|
| |
The proposed SQLMEM card would use a cache battery, you could lose power and after the lights are turned back on, the LOG system would still be intact and ready for action. |
|
| |
Note2: The size of the logfile on the SQLMEM card would have to be large enough so that transactions don't grow into the hard disk log-files. |
|
| |
Note4: The speed optimizaion is primarily for INSERT and UPDATE operations. In other words OLTP transactions, not warehousing or business inteligence queries. For that the SQLEB algorithms would come into play. When I have some time i'll give the shpeal why I think hardware algorithm optimization could influence large select query speed. For the better :-) |
|
| |