An RDBMS for CTI System?

cpt_koloth asks: "The company I work for are currently in the process of designing a custom CTI system for a client. A small part of the system is implemented, mostly to familiarize the development team with the telephony API (in our case TSAPI, since the client uses an Ericsson PBX) as a simple click 'n dial application. The main issue is the database system which will be used. We need a database is fast so that it can assign the calls without delays. The present system uses MySQL and is doing great but the numbers of requests will increase exponentially once the 'main' parts of the system are implemented (we have about 60000 requests per day currently most of them being cross table queries but finally they should be seven or eight times this number). Another aspect is the reporting agent, which will operate on the same database and also needs to be fast. We are currently thinking on a system with two databases one for the 'calling part', and one for the reporting part, and we cannot decide on the RDBMS to be used with the way the data will be updated between the two databases. Keep in mind that cost matters a lot. Does Slashdot have any insight to offer?"

First rule of development

[SirCyn]

You have Cost, Performance/Features, and Time. When requesting anything you pick two and the other is determined by what you pick. You have picked all three. You want low cost, fast performance, and in a reasonable time. It doesn't exist. Pick two and try again. There's nothing essentially wrong with MySQL running in a high capacity environment. It would need to by properly maintained, and database design will matter a lot. Speaking of DB design, wanting to separate the reporting and dialing databases, which are functionaly connected, sounds like it rubs against the grain a bit. I don't know much about your specific environment, so take my opinion with a large grain of salt.

Don't use an RDBMS

[chatgris]

From the sounds of it, you are using a RDMBS as a queue. If not, then ignore my message :D

I've worked on a number of systems similar to yours: The end result is that databases aren't meant to be queueing systems, they are meant to *STORE* data. ACID compliance (even the little that MySQL has) is not meant for a queueing system. It's meant for long term data reliability. Replicating databases is generally useless, as you need to delete a queue element once it is complete, and that has to be done on *every* single db server. Replication only helps for select.

My advice? The only thing I've ever seen work are custom controllers. Load up a bunch of id's into memory, work the data in there. Write your own memory allocator, and you'll be surprised at how many elements you can pack into memory when the data resides in a database (but accessible with a one table primary index query) and you keep an id reference in memory. From there, you can implement concurrency, priority, load scaling AND if you really need more performance, you can have different databases, with the controller knowing what resides where.

I see if far too often, the point is a database is NOT a queueing system.

Josh

DB design is the key

[alen]

i'm a dba in a sql2000 shop and we have servers that serve over 1 million hits a day with very little problems

database design is your biggest challenge no matter what you use. you can spend $1 million on orcale, but if you build it wrong then you are wasting your money.

build a read/write design where your writable servers don't serve reporting or similar queries. A long select can cause locks for people trying to update data. force people to use the reporting servers for report and other data generation

use the right data type for the data. don't use bigint's where an int will do since it will cause your storage needs to grow and the data involved in each query will increase as well

build the hardware right. don't use RAID5 on files where you are going to have a lot of writes

Re:My Solution: Two MySQL Databases!

[moderators_are_w*nke]

Queries per second is a meaningless number. It depends on what the queries are doing and how much data you have. Single row selects on primary keys are cheap, 1000 rows accross 8 tables with an order by is less so, it really depends what you're doing.