The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

May as well chuck in a few questions then - any answers will be passed on to our forum tech heads.

1. How hard was it to convert the scrip over to using Sybase rather than mySQL?

Installing Sybase on Linux is pretty easy -- you just download 2 RPMs, rpm -ivh sybase.rpm and you're good to go. Then you compile PHP with sybase-ct support and the magic is halfway there. The large portion was spent taking MySQL's proprietary "extensions" to SQL and making them work with Sybase (yeah yeah, even Sybase has it's own flavor of SQL).
For example, one of the most often used add-on in VB is the "LIMIT x,x" clause -- where you can tell MySQL to grab a certain amount of rows starting at a certain place. You can be certain that does not exist in the ANSI 92 SQL standard. Sybase has something like that worked out but for unknown reasons it sometimes fails when you have an ORDER BY clause in the SELECT statement. So, I had to write some SQL code to do that for me in the form of a Stored Procedure.

Things like that extended the initial development phase from VB 1.1.3 to VB Sybase 1.1.3 to around 3 weeks, and I think it wasn’t until a week later when I was confident I had all the bugs / incompatibilities worked out. Obviously now I know what to do / look for I can get it done much faster if need be.

Originally posted by Skunk 2. How significant were the speed enhancements gained?

Well, you don’t purchase Sybase, Oracle, or any Enterprise DB for raw speed – at least for a small user load. That is MySQL’s job, and it excels at that. It has been my experience that MySQL doesn’t excel at three key things:
1. Scalability
2. Reliability
3. Flexibility

MySQL doesn’t scale well at all – on our dual PIII 700 w/1GB of RAM we were dying with 300 or so concurrent users. Performance would grind to a halt and it required great finesse on my part to keep it going. I don’t know if you’re familiar with the forums at BigBigSavings (http://www.bigbigsavings.com/forum) but their DB server is huge – it’s a quad 450MHz CPU Sun Solaris machine (E420R) with 4GB of RAM (that config retails for $49,000 btw), aside from the many computers powering the web end. More than enough for 400 or 500 concurrent users, wouldn’t you think? Well, if you go there, you can see that they’re *still* having problems with it. MySQL, even with an overabundance of hardware, can’t hack it. They’ve tweaked it, but there is still the point, like all things, of diminishing returns. Sybase, Oracle and the like are engineered to scale – and scale they do.

MySQL was totally unreliable for us. Almost on a weekly basis I’d be running ISAMCHK to repair error after error on our tables. One time it even resulted in the loss of over 1000 registered users. That kind of routine corruption is unacceptable in my book. One way that MySQL helps improve performance is by removing some of the safeguards and other tools that the larger RDBMS’ use to ensure your data is written.
For example, MySQL doesn’t support (to the best of my knowledge) raw devices. Raw devices are special file systems that you set up explicitly for the RDBMS to use for the data files. This way, you don’t have the OS getting in the way of your write operations. In a file-based system, such as MySQL, you rely on the OS to write your information to the file. Just about every OS uses some form of read/write caching to help speed up performance. For example, you could open a file in vi, edit it, and then save it to the hard drive. However, the OS will keep the new file contents in a buffer until a convenient time is found to write it. This is a BadThing when it comes to important data, because the OS will tell the RDBMS that it successfully wrote the data to the drive when in fact it may not have. If the machine then suffers some sort of a failure and is restarted, the data would be lost.
Also, MySQL didn’t used to support Transactions (I think the latest version does). Transactions are ways of ensuring that your data is either written to the DB successfully or not. With out them, you can’t be sure that information was written.

Some examples of Sybase usage in the real world:
In Merchant Banks Sybase has long been a tried and trusted favorite because it offers the best characteristics of rapid development, flexibility and performance. This environment is characterized by high volume, time critical, complex mixed transactions, with database models in a continuous state of flux reflecting market changes. Each characteristic is vital, without which their business suffers financially. It's a very had-nosed business where deliverables are of the essence, and non-performance is rapidly discarded. Sybase has been, and continues to be the dominant database server of choice.

New large telco's have gone for Sybase too (MCI WorldCom for example). Because of the nature of the mobile telco business, obviously each call is logged, so transaction rates are running into the millions whilst still handling rapidly changing subscriber profiles and number changes. In their experience Sybase was a rock solid platform, with the performance to match on terabytes of data. Again, these companies can't tolerate downtime -- it's too punitive. They didn't just choose Sybase out of the air. These new multi-nationals went into this with their eyes wide open knowing their whole business would be dependent upon the database platform. After no doubt many exhaustive tests, where only performance counted, and politics didn't get a look in, they chose Sybase, and spent millions on it as the whole data backbone of their companies.

So there you have 2 completely differing usage profiles, both from leading modern business sectors where glitches in performance, flexibility or stability issues cost serious money and where only the best is good enough, regardless of the cost. Sybase dominates these niche markets where performance is of the essence. To me it says a lot.

Also, Distributed.net runs Sybase ASE 11.0.0.3 for their stats. They have a quad PIII 500 Xeon with 1GB of ram. They have gigabytes of information stored, and well over 400,000 users, with around 40,000 active every day.

A testimonial by the lead DBA:
“Thanks to Wim ten Have and the dedicated, industrious folks at Sybase, we're getting closer to being able to deploy statsbox as a FreeBSD machine. Earlier this week, Sybase re-released their Sybase ASE for Linux with a relaxed license that allows it to be run on a FreeBSD machine using Linux ABI compatibility. Plus -- the really big deal -- native libs for FreeBSD which allows us to compile php and all that stuff.

I've spent much of today setting up a scrub installation on a box at my house and so far it looks quite promising. A few hiccups with sqsh and I still don't have php talking, but it's close.

There's no other full-featured sql server around that's free for both development and deployment. It's a hell of a deal.http://www.sybase.com/linux/ase/
(and, since some of those links are broken)http://www.sybase.com/detail/1,3151,1009264,00.html
(Just to be clear, I'm really enthused about this recent development. We certainly don't get anything for endorsing Sybase.)”

Whew. Didn’t answer your question, did I? Well, let me put it this way: for a small number of concurrent users / posts / threads / etc. MySQL on the same hardware will always be faster, because that is what it is designed to be. However, once you “graduate” from the “normal” usage of MySQL is where Sybase really shines. Your options are virtually unlimited in terms of speed, reliability, and growth if you opt for Sybase matched with suitable hardware.

Originally posted by Skunk 3. With hindsight... was it worth it?

Yep, every penny / hour spent. On off-peak times (say at 4AM) when there are only 20 or 30 users online we’d be fine with MySQL. However when “the heat is on” during the day and we can handle the load pretty well I’m thankful that we got it. Granted, we need a new DB server now (with a “better” site than we had before we’re getting quite a bit more traffic now, that and Sybase just wants it ) – I’m looking at either a Dell 6450 (Quad PIII Xeon 700, 4GB RAM, etc.) or the aforementioned Sun E420 with 4 CPUS / 4GB RAM. The transition from a Linux OS to a Sun Solaris environment could be rough, so I’m not sure if we should just stick with Linux and get the Dell or make the switch to Solaris (which we’d do eventually).

I attribute some of the increased performance / concurrent users to the Zend Cache / Optimizer. It has dramatically decreased the CPU load of our web servers (we have two PIII 1GHz load balanced to the DB machine) and made the expansion possible. I suggest trying them as well.

Originally posted by Skunk Very nice forum set up btw - /me bookmarks

Thanks! We’ve spent quite a lot of time, energy, and money on the whole thing, and it’s a great feeling to see it finally out the door.

I guess to sum it all up:
MySQL good for small / medium loads
Sybase better for everything else.

Sorry for the long-winded post – I guess I felt like evangelizing today!

Feel free to drop me a line if there are any other burning "VB Sybase" questions you have!

Yeah cheers m8, I'm a good friend (well i hope :P) of Skunk and a constant user of the gameplay.com forums, and although I've recommended to him they upgrade to Sybase/Oracle I don't know anything about them . Just hope gameplay takes some action and we'll be well on the way to fast forums. Nice one .

Some of this I posted before, other info is "new". I compiled this from my own experience and from some Sybase groups I belong to. I haven't played with Oracle 9i so there could be some new things in that which invalidates the following. Take it with a grain of salt.

Ok, I’ll try and break down what I found into several topics: Performance, Cost of Ownership, and Case Studies.

Performance
PC-C benchmark of Sybase ASE 12.0.0.2 on Sun E-10000 beat Oracle 8i Enterprise on the same platform by 36%.

Also MySQL.com did benchmarks on MySQL vs. several DB vendors. Extrapolating from their results ("Numerical comparison tables between DB2, Informix, MS-SQL, MySQL, Solid and Sybase on NT 4.0" and "Numerical comparison between MySQL and Oracle 8.0.3 running on a NT" ), you can see that Sybase ASE 11.5 beats Oracle 8.0.3 pretty consistently.

Granted, these aren't the latest versions of either DBMS and they are running on WinNT 4.0, but expect approximately the same results on your Linux machine running the latest versions (I would expect similar performance benchmarks between 8.0.3 to 8.1i, but I'd expect quite a bit of gains from ASE 11.5 to ASE 12.0 (and soon to be 12.5)).

Cost of Ownership
The Total Cost of Ownership is made up of several parts, including initial cost, maintenance / licensing fees, downtime, disaster recovery which causes lost revenues / clients, etc.

Initial costs for Sybase and Oracle 8i Enterprise are staggeringly different: Oracle 8i Enterprise for a dual 1000MHz Intel CPU system is roughly $85,000 including support. Sybase would be right around $15,000 for the same set up. Oracle also has a different licensing structure for non-Intel based CPUs, so expect to pay more for that. Sybase ASE 11.0.0.3 is 100% free for any use and any user.

As an SA a key factor is to be able to do DB dumps on the fly. Oracle STILL cannot do this. You have to first offline the 'tablespace' and copy the Unix files around. Oracle's other method is by last dump and transaction log roll-forward.

Space recovery used to be a major issue. Before space from a delete record could be re-used, the whole server had to be offlined, dumped and procedures run to recover the space. Sybase does this on the fly. No weekend working on this one!

Disaster recovery is much better in Sybase compared to Oracle.

Disaster management is a no brainer. Sybase can do full DB backups on the fly, and warm standby using replication server is a no brainer when it comes to 99.999 % availability. Oracle can't do either. It’s easier to customize & tune your Sybase dataserver compared to Oracle. Memory management is much better. Sybase costs less in terms of resources required and software license fees. Sybase 12.0 & 12.5 blow Oracle clean out of the water (I know someone who has personally conducted benchmarks on IBM S boxes, HP V & N boxes as well as Sun's), with disks being EMC & Sharks with SSA. Reliability & stability is a prime reason banks & financial institutions run over 90% of their apps on Sybase. Same goes for HMO's and energy industry.

Case Studies
In Merchant Banks, Sybase has long been a tried and trusted favorite, because it offers the best characteristics of rapid development, flexibility and performance, This environment is characterized by high volume, time critical, complex mixed transactions, with database models in a continuous state of flux reflecting market changes. Each characteristic is vital, without which their business suffers financially. It's a very had-nosed business where deliverables are of the essence, and non-performance is rapidly discarded. Sybase has been, and continues to be the dominant database server of choice.

New large telco's have gone for Sybase too (MCI WorldCom for example). Because of the nature of the mobile telco business, obviously each call is logged, so transaction rates are running into millions whilst still handling rapidly changing subscriber profiles and number changes. In their experience Sybase was a rock solid platform, with the performance to match on terabytes of data. Again, these companies can't tolerate downtime, it's too punitive. They didn't just choose Sybase out of the air. These new multi-nationals went into this with their eyes wide open knowing their whole business would be dependent upon the database platform. After no doubt many exhaustive tests, where only performance counted, and politics didn't get a look in, they chose Sybase, and spent millions on it as the whole data backbone of their companies.

So there you have 2 completely differing usage profiles, both from leading modern business sectors where glitches in performance, flexibility or stability issues cost serious money and where only the best is good enough, regardless of the cost. Sybase dominates these niche markets where performance is of the essence. To me it says a lot.

One person I talked to was contracted for a world-renown client-server computer manufacturer who did an in house comparison of Oracle and Sybase server products. Their in-house database engines at that time ran Sybase, but they wished to be equal handed in the market. In a nutshell, they found that they Sybase product offered a cleaner layered application architecture, a faster and more flexible development environment, and found that they couldn't implement stored procedures (a key feature for performance, quality control and design integrity) on Oracle. The similarly named feature on Oracle just wasn't the same at all.

Also, Distributed.net runs Sybase ASE 11.0.0.3 for their stats. They have a quad PIII 500 Xeon with 2GB of ram. They have gigabytes of information stored, and well over 400,000 users, with around 40,000 active every day.

A testimonial by the lead DBA:
"Thanks to Wim ten Have and the dedicated, industrious folks at Sybase, we're getting closer to being able to deploy statsbox as a FreeBSD machine. Earlier this week, Sybase re-released their Sybase ASE for Linux with a relaxed license that allows it to be run on a FreeBSD machine using Linux ABI compatibility. Plus -- the really big deal -- native libs for FreeBSD which allows us to compile php and all that stuff.

I've spent much of today setting up a scrub installation on a box at my house and so far it looks quite promising. A few hiccups with sqsh and I still don't have php talking, but it's close.

There's no other full-featured sql server around that's free for both development and deployment. It's a hell of a deal.http://www.sybase.com/linux/ase/
(and, since some of those links are broken)http://www.sybase.com/detail/1,3151,1009264,00.html
(Just to be clear, I'm really enthused about this recent development. We certainly don't get anything for endorsing Sybase.)"

Thank you Matt - the information and views you provide are interesting reading even for someone such as myself who has limmited knowledge of databases on this scale. I'm very new to web application development and I've been reading around quite a lot about different platforms and middlewear and databases - and call me paranoid - but I am always asking myself "sounds good but how scalable is it" even though for most sites scalability and handling large numbers of concurrent transactions isn't going to be an issue - you always want to know that you are building on solid foundations.

Just new year I and I joined just to compliment you on your summary. I have never heard of Sybase before and really only thought their was MySQL / PostGreSQL in Open Source Land and Oracle in Pay heaps land.

Can I ask how easy you found it to connect to Sybase using PHP? PHP has built in Oracle commands.... what about for Sybase? Are their any tutorials you started with? Is there much of an Sybase community out there (that's the great thing about PHP/MYSQL!), can you suggest places to go when your stuck --- or have I already found you guys!!

Anyway great work on your article I really enjoyed it and am considering having a go at implementing Sybase.

Originally posted by Zitan Can I ask how easy you found it to connect to Sybase using PHP? PHP has built in Oracle commands.... what about for Sybase? Are their any tutorials you started with? Is there much of an Sybase community out there (that's the great thing about PHP/MYSQL!), can you suggest places to go when your stuck --- or have I already found you guys!!

Anyway great work on your article I really enjoyed it and am considering having a go at implementing Sybase.

Thanks,
Zitan.

Zitan, glad to hear my rantings do not fall on deaf ears! (although if you want I can talk your ear off about certain *ahem* things *cough*mysqlsuxx0rs*cough* )

Sybase PHP functions are almost identical to MySQL ones (e.g. mysql_connect == sybase_connect, etc.). Oracle operates on a different paradigm which is why there are all of the 'parse', 'prepare', etc. things.

I never really used a tutorial; I just jumped in and found the water to be very, very nice (tingles too! ).

LOL – I think thanking the Lord that I exist is a bit much but I appreciate it!

Originally posted by Ckeren Initially what I thought that Oracle was the best DBMS software out there since SAP is using it. But not till I read yours.

Don’t get me wrong, Oracle is not a bad product (or everyone using it would experience routine data corruption, slowdowns, etc.). It’s just not the be-all, end-all product that they like to think they are.

Originally posted by Ckeren I'm not clear just one thing about Sybase do they(or will they) provide the software for free just like MySQL and if not how much do we have to pay to use it, for a small company.

Well, if you visit the Sybase Downloads link in my signature you can download a copy of Sybase yourself. Sybase ASE 11.0.0.3 for Linux is 100% free for anything that you want to do with it – development AND production. The other versions of Sybase for Linux (11.9.2 and 12.5) are development / eval copies ONLY. They operate on a key-based system (e.g. when you install it requests a license key that you get in your e-mail when you download Sybase) but there’s nothing stopping you from downloading a new key every 6 months when it expires.

In any rate, Sybase internet licensing is done on a per-CPU basis (since they can’t do per-seat since who knows how many users will be connected) and retails for about $4500 per CPU. This gives you 8 – 5 EST M-F phone support as well. If you want 24/7/365 support you will have to pay somewhere between $1100 and $4000 (depends on # of CPUs, platform, etc.). You also get free upgrades for 1 year. We purchased 11.9.2 last August and since 12.5 was released last month we get it for free. I called last week and they’re shipped it out to me. It’s just that easy!

Support renewal is like $2200 for 24/7/365 per year. This, again, gives you a year of free upgrades / patches / EBF’s (like NT’s service packs).