Re: Sybase limitations

} } Mark Sigler (msigler@informix.com) wrote: } } [ stuff deleted ] } } : Having worked with all the major databases, I can give you a brief } : overview of why Sybase doesn't scale well. } } : Sybase was the first vendor to use a mulit-threaded model, but it was } : targeted to uni-processor systems prior to the availablity of SMP systems. } : The basic problem is that the first process, Engine 0, handles the bulk } : of the I/O which is the biggest bottleneck in RDBMS's. Engines 1-n handle } : mostly network I/O or user connections. This results in a great deal of } : context switching by the OS for Engine 0 and causes blocking. This is } : readily evident if you use UNIX utilities to examine context switches. } : Furthermore, the threads in Sybase are relatively heavy, thus thread creation } : is expensive. Since Engine 0 is a bottleneck, the other engines diminish } : in value as the number increases since they are so dependent on Engine 0 } : for services. This results in scalability problems beyond 4-6 CPU's. } : System 10 has a very similar architecture to v4.9 and did not address } : these issues. There is a great deal of concern by the industry analysts } : that System 11 will not resolve the problems as well, this is shared by } : many users of System 10 that found out the hard way that it didn't live } : up to the promises made by Sybase, resulting in the recent financial } : diaster in which Sybase lost over 40% in stock values. } : MicroSoft SQL-Server for NT has these problems as well since it is } : based on the v4.x Sybase kernel. } } : Oracle has a similar probelm in that the architecture is heavy in } ^^^^^^^^^^^^^^^ } This is misleading. You imply that Oracle, like Sybase, cannot scale } beyond 4-6 processors. } Similar in the respect that the architecture is HEAVY. } } Oracle scales linearly (at most things) to high tens of processors (and } I suspect beyond, but have never tried it). } I have done benchmarks of Oracle v7.1 on a 20 CPU Sun with 500GB of disk. You have to load it up with lots of users/processes to take advantage of an SMP box. In this DSS benchmark simulating 10-20 users we couldn't use all of the CPU resources. Matter of fact, the machine spent lots of time context switching... due to the multi-process architecture. } } Oracle's problem is that their scalability line is not as steep as } yours (IMHO). } Due to the MP architecture and the lack of data partioning. } On the other hand, until you get 8.0 out, you cannot scale to as many } processors as Oracle. } Don't speak too soon... } } : multiple processes that block and context switch. Oracle is a } : mulit-process, not multi-threaded architecture, that uses a multiplexing } : scheme with a dispatcher. Oracle uses more system resources to accomplish } : it's tasks resulting in less efficient use of the hardware resources. } : On Very Large DataBases (VLDB) scalability is limited due to the lack } : of data partitioning, which drastically reduces the amount of disk I/O, } : again the most prevalent bottleneck. } } On the other hand, they can do parallel query on non-fragmented tables, } unlike OnLine. Making PDQ work well requires more administrative effort } than Oracle7 PQO. } Not so! PDQ is based on the concept of iterators and exchange iterators which execute subtasks and pass the results to the next set of exchange iterators. Visualize it as a B-Tree. Partitioned tables facilitate the process since it eliminates so much disk i/o contention, a common bottleneck. As for administrative effort... PQO requires that you establish default parallelism (//) parameter in the config file or in the DDL at the table level or thru "hints" in the DDL/DML comments. I had to hand edit dozens of files over and over again to fine-tune the //ism. PDQ has a couple of config params and then you write one script per db that does the ALTER TABLE statements to set the fragmentation to an appropriate method. You would dbspace the chunks eitherway. The point is that to take *real* advantage of PQO requires MUCH more effort than PDQ at the kernel level. PQO offers no option to minimize disk contention in an intelligent fashion... excuse me they have "dynamic partitioning", RAID 0 striping based on the OS LVM which is NOT database aware. They won't be able to scale until they eat those words. } : Both vendors are trying to re-architect their products to match the } : lightweight multi-threaded model that is core to the Infomix DSA } : OnLine product. The MT model allows for the internal parallelism } : that is winning rave reviews by the industry analysts. Coupled with } : data partitioning to reduce disk I/O and you have a product that } : scales from uni-processor to large numbers of SMP CPU's. } : Note that DSA was developed in conjunction with Sequent, a pioneer } : in large SMP UNIX systems. DSA uses the concept of "Virtual Processors" } : that are UNIX processes that are individually specialized for RDBMS } : functions such as CPU, network I/O (NIO), asynchronous I/O (AIO) } : logging I/O (LIO) and so forth. Each VP executes threads for particular } : tasks in a non-blocking fashion. This provides very efficient use of } : hardware resources with minimal context switching. } } : The fundamental architectures of the RDBMS kernel are very important } : to addressing scalablity and performance. } } : I hope that this sheds some light on the differences in the current } : RDBMS products in the market. Obviously I have a bias, but the facts } : remain and are evidenced by the performance or lack thereof in key } : benchmarks such as the TPC-C which are critical for un-biased } : assessment of RDBMS products. } } I have relatively limited experience of OnLine 7.1 compared to Oracle7 } with PQO, but so far I find it impressive. I hope 8.0 works as well on } MPP, where the architecture should provide *real* advantage. } I believe that MPP will have a great story in scalability and performance for the limited cases where it will be required. SMP still has the most bang for the buck going, is simple to administer and mets or exceeds most requirements. } } -- } graeme } -- [cut] } Hope this isn't considered SPAM since I feel I'm backing up my arguments. BTW... I heard Pyramid got bought... by whom? -- //////////////// Mark Sigler ////////// // Client Services Engineer ////// / /// Informix Software, Inc. ///// // //// 8675 Hidden River Parkway TBD //// // ///// Tampa, FL 33637 Fort Lauderdale, FL /// // ////// Voice: 813.971.0010 407.883.9319 // // /////// Fax: 813.632.9582 407.883.9319 / /////////// Voicemail: 800.688.4217 x2147 407.883.9319 //////////////// Email: msigler@informix.com WWWeb: http://www.informix.com --