Beta

×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

An RDBMS for CTI System?

Cliff posted more than 7 years ago | from the chosing-the-right-database dept.

Databases 51

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?"

cancel ×

51 comments

Sorry! There are no comments related to the filter you selected.

Interesting Solution (3, Funny)

locokamil (850008) | more than 7 years ago | (#17379316)

I would recommend a RTFK running on a SMKPR, with a stock GHCCF, and several KKDFL's.

As you can see, this would drastically cut UIUER, and lead to greater LUD which, of course, is what every management team wants!

Re:Interesting Solution (2, Funny)

Anonymous Coward | more than 7 years ago | (#17379322)

I just wish you would STFU.

Re:Interesting Solution (0, Offtopic)

Thansal (999464) | more than 7 years ago | (#17379462)

I honestly can't tell if that was a joke or real advice.

I am kinda frightened, and never want to have anything to do with a telephony set up....

Re:Interesting Solution (1, Funny)

RingDev (879105) | more than 7 years ago | (#17379476)

Unfortunatly, only one of those acronyms are on the Acronym Finder http://www.acronymfinder.com/ [acronymfinder.com]

So while the original poster's text was a bit over-acronymed, I can still understand the basic jist of it. Your post however appears to lack any merrit. I hope it was made in an attempt to ridicule the original poster's excessive use of acronyms, but it would have been significantly better had you used real acronyms.

-Rick

Wow! (1)

uradu (10768) | more than 7 years ago | (#17379636)

Just Wow!

Re:Interesting Solution (2, Interesting)

rgbscan (321794) | more than 7 years ago | (#17379684)

While I can't comment on the DB part, I can give a little insight into the idea behind this having worked on something similar.

CTI is a "common telephony interface". In my particular application, you would call into our helpdesk number and get a automated voice response unit. It would prompt you to type in the asset tracking tag off your PC. Once you entered this number in and pressed the # key you would be parked in the queue waiting for a helpdesk tech to answer. The IVR unit would pass this asset tag into the helpdesk software via CTI, and the helpdesk software would query the asset db so all the PC information would prefill right as the helpdesk agent took the call via a softphone on the pc. This allowed for all the relveant info to be present and sped up the call.

In the event the agent needed to transfer the call to a level 2 rep, they would transfer the call via softphone and the CTI interface would pass along all relevant details to the new party taking the call. This saved us from having each person taking the call to ask for the same basic info over and over again, and if each tech inserted noted on what they did, the client would not have to repeat the problem and the level 2 tech would not have to repeat troubleshooting steps already done.

Long and the short of it, the DB has to be quick enough to pass this info along or its not useful. If the info doesn't prefill immeadiately and it's quicker just to ask, then you haven;t bought yourself anything.

Re:Interesting Solution (1)

Fozzyuw (950608) | more than 7 years ago | (#17380286)

CTI is a "common telephony interface"

Thanks, I was wondering what it was since Wikipedia had not reference to this CTI [wikipedia.org] . However, Google offers some other definitions [google.com] , including "computer telephony integration".

Cheers,
Fozzy

Re:Interesting Solution (1)

pjay_dml (710053) | more than 7 years ago | (#17382486)

What are you talking about: http://en.wikipedia.org/wiki/Computer_telephony_in tegration [wikipedia.org]

And according to the history it's been there for over a year.

Re:Interesting Solution (1)

Fozzyuw (950608) | more than 7 years ago | (#17387314)

Opps, you're correct. I missed it. My Bad.

Cheers,
Fozzy

Re:Interesting Solution (2, Funny)

sampowers (54424) | more than 7 years ago | (#17380514)

... and lead to greater LUD which, of course, is what every management team wants!
I am SO SICK of all these management LUDdite types.

Re:Interesting Solution (1)

Blakey Rat (99501) | more than 7 years ago | (#17382924)

List of acronyms in the summary I don't understand:

RDBMS
CTI
TSAPI
PBX

List of acronyms in the summary I do understand:

API
MySQL

Of course, since I don't know what the hell a CTI *is* (that seems to be the main focus of the question), I can't give any appropriate advice. So instead I'll give some inappropriate advice: Take your developers out to a martini bar.

MySQL Cluster (1)

jorela (84188) | more than 7 years ago | (#17379552)

Sounds like a good fit for MySQL Cluster.

My Solution: Two MySQL Databases! (3, Interesting)

DrZaius (6588) | more than 7 years ago | (#17379604)

Hey,

If mysql is working for you now, you should look at mysql scaling options. For example, if you are worried about reporting queries, replicate the database to a second machine for running the reports against. Mysql replication works great for this sort of application. Also, if your dialer application is only performaning read queries, you can spread those across replicas too.

Knowing the current 'size' of your database would help -- if it's a dual processor box with 1 or 2 gb of ram, there are still a few affordable forklift upgrades before you need to worry too much about one box or mysql's performance (assuming your indexes are set right).

Also, MySQL Cluster was designed by/for the telecomm industry -- the original commissioners were performaning analysis on call records or something of the such.

MySQL can definitely do whatever you want it to. Why switch?

Re:My Solution: Two MySQL Databases! (1)

Pegasus (13291) | more than 7 years ago | (#17380036)

Absolutely ... 60k per day number he mentions seems low to me. I'm comfortably running mysql with 2-3k queries per second ...

Re:My Solution: Two MySQL Databases! (3, Insightful)

moderators_are_w*nke (571920) | more than 7 years ago | (#17382334)

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.

Re:My Solution: Two MySQL Databases! (0)

kpharmer (452893) | more than 7 years ago | (#17388336)

> MySQL can definitely do whatever you want it to. Why switch?

because it is obviously a poor contender?

> If mysql is working for you now, you should look at mysql scaling options. For example, if you are worried about reporting queries,
> replicate the database to a second machine for running the reports against. Mysql replication works great for this sort of application.

Ah, that doesn't address partitioning, parallelism, query optimization, automatic summarization pr query rewriting, does it?

Note that these are standard reporting database features - and are responsible for easily providing reporting queries 40-400x performance improvements when moving from mysql to db2 or oracle.

So, replicating the exact same transactional model to another server - which also doesn't provide partitioning or parallelism is an extremely sad solution. Ideally, you move the data through a lightweight ETL solution to remodel it and then move it into a database that provides all these features. Then you'll not only have performance and scalability - you'll also have additional *standard* reporting functionality (time series queries, point in time reporting, etc).

> Also, MySQL Cluster was designed by/for the telecomm industry -- the original commissioners were performaning analysis on
> call records or something of the such.

Sure, if you want to limit the amount of data you use to what will fit in memory. Other problems are likely (like compatibility with mysql's standard product since they just purchased cluster a few years ago) - but since so few people who know what they're doing are using mysql's cluster product this hasn't yet been well documented.

Re:My Solution: Two MySQL Databases! (1)

DrZaius (6588) | more than 7 years ago | (#17389920)

I'm guessing the administration overhead of oracle or db2 would make it not worth while. Due to the fact he's asking slashdot about his problem, the expensive db's are probably out of reach for him. This is especially true because he says "cost matters a lot."

I made the assumption that the reporting application already exists and runs against the current schema. If it doesn't, ETL is an option with MySQL just as much as any other database. Again, I doubt it will be a problem if cost matters -- they probably have very simple reporting needs.

60k queries/day is less than 1 per second. Even 6-7 times that is still only 6-7 a second. MySQL 5.1 has partitioning. MySQL was originally designed for data warehousing applications (that is what the isam table structure was all about) and there are many documented > 100GB databases in existence.

Re:My Solution: Two MySQL Databases! (0)

kpharmer (452893) | more than 7 years ago | (#17390514)

> I'm guessing the administration overhead of oracle or db2 would make it not worth while. Due to the fact he's asking slashdot about
> his problem, the expensive db's are probably out of reach for him. This is especially true because he says "cost matters a lot."

Note that administration of oracle or db2 may or may not be greater than administration of mysql - if you find yourself having to manage a half-dozen mysql databases all replicating large amounts of data and struggling to support slow & complex queries.

And the licensing cost is not necessarily greater either - if you can go with a smaller server (with db2, I think it's free for less than 4 gbytes of memory), and it's especially cheaper when you consider the hardware savings over mysql.

> I made the assumption that the reporting application already exists and runs against the current schema. If it doesn't,
> ETL is an option with MySQL just as much as any other database. Again, I doubt it will be a problem if cost matters
> -- they probably have very simple reporting needs.

Note that transactional models (using normalized models) are much more expensive to report against than analytical models (using star-schemas): they cost far more to write the reports against, support only limited reporting functionality, and take more hardware to get the equivilent speed. It's only worth going this route if you plan to deliver almost no reporting.

> 60k queries/day is less than 1 per second. Even 6-7 times that is still only 6-7 a second. MySQL 5.1 has partitioning.
> MySQL was originally designed for data warehousing applications (that is what the isam table structure was all about)
> and there are many documented > 100GB databases in existence.

A few thoughts here:
1. a single query that requires a tablescan of 100 million rows on mysql can take 60 seconds, 6000 seconds if the optimizer gets confused (see references regarding impacts of simplistic optimizer2).
2. 60,000 queries probably don't come at one a second, they probably come in an uneven workload with large bursts. So, perhaps you're using 100 queries a second on monday morning, etc.
3. MySQL's partitioning in 5.1 doesn't matter now - since only 5.0.27 is GA. No idea when 5.1 goes GA - or more importantly, when their partitioning is mature enough to want to depend upon in a serious production environment.
4. The fact that mysql claims to originally have been oriented towards data warehousing and yet failed to include any basic data warehousing features is completely consistent with their attempts to market themselves more recently as an enterprise database without basic transactions. Yes, I know they have had transactions and RI for a few years now - but prior to that time they were telling people that nobody really needed it.
5. A 100GB MySQL database is nothing to brag about. I've seen plenty of gigantic databases - that due to misdesign or use of the wrong product could support almost no queries, had very poor query speed, very poor load speed, or whatever. Keeping 100 gbytes of data in a single-threaded, non-partitioning database is hardly an attractive solution - when so much better alternatives exist.

Re:My Solution: Two MySQL Databases! (1)

DrZaius (6588) | more than 7 years ago | (#17405964)

All of your thoughts are valid, but probably not in this case.

Sure, you've worked with all sorts of huge 100GB+ db's. It's really easy to say what a table scan of a 100m table can take on mysql. Again, I'm sure this poster isn't looking for db's in the 100gb scale, especially if he's asking slashdot.

I'm betting a simple solution that will meet the needs of the poster using mysql can be done very quickly and cheaply. I'm also sure that you would end up spending around the same amount on oracle or db2 if the data ever needed to scale up to the 100's of gb range. That's probably not the case here, but I guess we'll never know if the poster doesn't give more details.

First rule of development (2, Insightful)

SirCyn (694031) | more than 7 years ago | (#17379630)

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.

Re:First rule of development (1)

Dan Ost (415913) | more than 7 years ago | (#17379802)

That rule only applies when you're doing the full development yourself.

In this case, they're not interested in developing their own DBMS and so the quality
of what they choose is independent of the time it takes to choose it (the cost depends
on what they choose, but there are free DBMSs out there that, depending on what they
need, will do just as nicely as an expensive DBMS).

Don't use an RDBMS (2, Insightful)

chatgris (735079) | more than 7 years ago | (#17379770)

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

And the problem with MySQL is? (1)

T-Ranger (10520) | more than 7 years ago | (#17379812)

MySQL is fast. If the queries you are doing on it now it can handle, "correctly" (i.e. with indexes.. use EXPLAIN SELECT....) then MySQL will almost definitely be as fast or faster then anything else for reads. Well, on a single machine. And Ill cover even money bets on when there is clustering, when all the data is everywhere. The likes of DB2 and Oracle may be able to do something with complex clustering, e.g. partitioning the data up on different machines. Without knowing about your app, I would think that the working data of any client is going to be about the same as any other client. (Well, the non-reporting parts, anyway). That, and your somewhat confused use of "different databases" indicates to me that you are not talking about that level of clustering.

Again, for reads across a reasonable number of tables, MySQL is blazingly fast. Your app doesn't seem like it would be beyond these current MySQL limits. Some analysis, testing, optimization, and a resulting good design, MySQL will beat the "enterprise" systems. With a shitty design, the enterprise systems will be shit, too.

Is there a problem with MySQL now? Or do you just have an (irrational) fear that it wont scale up to your level of requirements"?

Re:And the problem with MySQL is? (2, Interesting)

dknj (441802) | more than 7 years ago | (#17380282)

it also depends on size as well. we did a digital library project and benchmarked our app with mysql, postgresql, sql server, oracle and db2. the winners? oracle, sql server and db2. postgresql gets an honorable mention. i laugh at mysql.

Re:And the problem with MySQL is? (1)

T-Ranger (10520) | more than 7 years ago | (#17380472)

Details? Across how may boxes? Was it partitioned data? How many tables in a typical query? With mostly-reads, I just cant believe that MySQL was at the bottom of the list. Oh, yeh. What version of MySQL?

Re:And the problem with MySQL is? (0)

kpharmer (452893) | more than 7 years ago | (#17388564)

> MySQL is fast.

Sorry - but relational databases are complex beasts - and too complex for such simplifications. MySQL can be fast - if you're on limited hardware with simple and highly-selective repetitive read-only queries hitting its MyIsam storage engine.

But:
    - if you're doing reporting and querying more than 2-4% of the rows in the database then it wonly use a btrieve-index, and so will do table scans. This will suck, and be far slower than using a database like db2, oracle, informix or sql server.
    - if you're using complex queries (5+ tables) - then its simple optimizer is likely to take the wrong turn and use a nested loop join when it should use a merge join, etc. Then your peformance will suck and you'll end up continually rewriting your query until you get some 400-line monstrousity that barely performs.
    - if you've got a 2+ way SMP - then MySQL's singled-threaded design will fail to split the processing for a single query across multiple processors. That'll suck compared to db2, oracle, sql server, etc, etc, etc.
    - if you're not running the same content-query repetitively - then its content cache won't work for you and you'll be hitting the database all the time. This is of course what most databases do, and what it sounds like this app in particular will do.
    - if you're not using MyISAM - lets say because you want support for transactions, referential integrity, etc. Then in that case you don't get the speed benefit of MyISAM at all, and are operating at roughtly the same speed as Postgresql for all operations. Except when queries get complex or the load gets high - then you're dragging.
    - if you're doing quite a few concurrent writes - in which case MyISAM's table locking will kill your performance and even InnoDB's performance sucks if the load gets high.

Bottom line: MySQL's speed reputation has been discovered for the most part to not apply to most real-world scenarios. Unless you're running a content-management site (like slashdot) then you won't get it. Even then there are quite a few functions that are so slow that you will feel the pain (remember 4 hours to reindex slashdot's 20 million rows?).

Get it right then get it fast (0, Troll)

coyote-san (38515) | more than 7 years ago | (#17379826)

MySQL? Be sure you have a robust database first and work around that. I don't know if MySQL can handle high loads gracefully now, but in the past it's been known to flake out and corrupt the database. Not a good thing if that takes out your phones.

Performance is always an issue, but there are some standard techniques for that. E.g., connection pooling (where a single connection is reused for 100-ish queries or 15 seconds before being discarded), caching read-mostly data, pushing as much of your logic into stored procedures as possible, etc.

(Remember that it's always far cheaper to do things in the database itself than it is to push data to your application and then push the results back. I have some updates that take ~15 minutes in my app optimized to minimize queries... and ~10 seconds when done via a loop in a stored procedure even though the latter uses a less efficient algorithm!)

BTW I like PostgreSQL. Solid referential integrity, triggers, stored procedures, etc. Even support for user-defined types and functions written in native C.

Warning: parent contains FUD (2, Informative)

nule.org (591224) | more than 7 years ago | (#17380288)

That's fine if you happen to like PostgreSQL. I use both that and MySQL a great deal. However, unless you have something to back it up, your comment that:

MySQL? Be sure you have a robust database first and work around that. I don't know if MySQL can handle high loads gracefully now, but in the past it's been known to flake out and corrupt the database. Not a good thing if that takes out your phones.
needs to be taken as pure FUD. I implemented a MySQL solution for the integration department of a major North American hospital (one of the top 5) that logs all electronic medical record transactions between different hospital information systems (some 4+ million a day) to a MySQL database for querying, lookups and other functions, and it's run flawlessly for years on both 4.x and 5.x versions. The only time there was ever any data issue was when the entire data center lost power and you better believe that was the least of their worries.

Anyway, hopefully the mods will see this and -1 mod you and your fanboyism. PGSQL is fine, I'm using it now for a major project, but you needn't attempt to trash talk other solutions. It's simply counterproductive to the original poster's question.

Is there definitely a problem here? (1)

WombatDeath (681651) | more than 7 years ago | (#17379922)

You're anticipating 500,000 requests a day, so let's bump it up to a million to give you a bit of wiggle-room. Assuming a worst-case scenario of those queries being shoe-horned into a typical 8-hour working day, that equates to an average of 35 queries a second, multiplied by some factor to account for peak usage. I have no experience of MySQL specifically but that doesn't sound like an unreasonable ball-park to me - with a decent server, proper indexing and well-written queries I don't see why you should struggle.

(This is obviously subject to the size and structure of your database, but I'm guessing that you don't want to join dozens of tables all containing millions of records).

Before you run out and buy an Oracle licence I would attempt to run some tests on your current set-up. Presumably you have, or can acquire, an idea of the ultimate database structure and data volumes? You may find that you just need to optimise your database and spend a few grand on a better server. As for the reporting question: if you don't need to report on real-time data you can perhaps perform a daily load to your reporting server in the middle of the night when usage is low.

And in any case, your application will hopefully be portable between different database systems without massive effort, so you may be able to defer a final decision until you've got a better idea of what your database needs to be able to do.

Agree--scalability isn't really a problem (1)

WebCowboy (196209) | more than 7 years ago | (#17388848)

Assuming a worst-case scenario of those queries being shoe-horned into a typical 8-hour working day, that equates to an average of 35 queries a second, multiplied by some factor to account for peak usage. I have no experience of MySQL specifically but that doesn't sound like an unreasonable ball-park to me - with a decent server, proper indexing and well-written queries I don't see why you should struggle.

I'd also say from experience that either of the main Free/open source options could handily manage the load you've described here. Besides making sure you have robust hardware there are application design and requirements to consider in the choice of RDBMS as well that relate to "quality and not quantity"

Personally I'd choose PostgreSQL 8.x over MySQL because (again from direct experience) is is head and shoulders above any other Free solution out there when it comes to concurrency and its transactional support is a fair bit more mature than MySQL. The main consideration is how update-heavy your application is. The article poster said that these transactions would be done concurrently with reporting and data analysis. If the transactions are essentially a series of INSERT statements then there wouldn't be much impact on performance in MySQL and I'd say stick with it because it is what you have now (if it ain't broke, don't fix it). The performance gap has closed quite a bit in the last few years, but MySQL still has a bit of an edge when it comes to single-table queries and basic INSERTs. If that is what you are doing most then it is great.

However, if you are doing multi-statement transactions with UPDATEs on one or more existing records, and/or INSERT and UPDATE operations that rely on data retrieved from some moderate to complex SELECT statement and this is within a single transaction then you have to be a fair bit more careful with MySQL than with PostgreSQL to avoid concurrency problems. If one client runs such a transaction on a MySQL database there are more situations where the transaction will lock rows and/or tables and all other clients will e blocked until the transaction is finished. Generally it is good practice (regardless of the RDBMS used) to avoid long-running transactions as much as possible, however at times an expensive transaction may be unavoidable
and that is where PostgreSQL's MVCC handles things much better.

Another consideration is how your business logic is handled. I prefer to make use of the database to make sure business rules are followed as it offers, almost without exception, the best performance by far. It is basically impossible to use tools/languages like PHP, Perl, Ruby or Python to handle ground-floor business logic faster or better than the database can. MySQL's heritage is simplicity and performance and thus there has been little emphasis on data integrity. It is pretty slow and tedious to have to rely on higher-level layers to do things like make sure '0000-00-00' and '2007-02-30' are not accepted as valid dates, or ensure referential integrity, or simulate triggers. If you know the data going into the database is already of high quality (it is validated at a higher-level, or it is acquired through automation such as barcodes or RFIDs or industrial controllers) and you are doing straight SELECTs without processing at the DBMS level then MySQL will present no problems and its performance and small footprint might be worth more than the unused capabilities of a heavier database back-end.

However, if you must validate human-entered data at the DBMS level or need to create queries that do calculations or other data manipulations (aggregate functions, concatenations, getting a timespan from different dates, etc) then MySQL is VERY INFURIATING to a seasoned database admin or programmer. This is because while MySQL behaves consistently, it is often consistently WRONG behaviour (by that I don't mean it spits out 2+2=5 or it works differently from howit was designed--I mean wrong in that it is different from how most people think, from the standards and from how ALL other SQL databases work!). As a result if you ever wanted to move to something else like Oracle in the future (or were made to by a PHB) then you could run into a LOT of migration issues beyond obvious syntax differences. For example, you could have a query that returns a different data set in MySQL than it does on all the other SQL databases! PgSQL behaves more conventionally (plus its PL syntax pretty closely resembles Oracle) so if you anticipate that your app may need to be migrated or support different RDBMSes then go with PgSQL.

If you are still partial to MySQL be very sure to use InnoDB for data integrity (but if you're already familiar with MySQL then this is very obvious advice). Otherwise MySQL is still quite a valid and capable choice for an application of this scale and it is very reliable (as is PgSQL--robustness/reliability is a common characteristic of virtually all mature Free software projects). As you can see, there are other issues to consider that are probably of more importance, and not all of them are strictly technical.

MySQL scales enough (2, Informative)

guruevi (827432) | more than 7 years ago | (#17379938)

You have ONLY 60000 query's per day? That's on a 12 hour working cycle about 1,3 per second. ANY RDBMS should be able to handle this on today's hardware (even on yesterday's hardware).

If you're looking for data safety and recovery etc. you better make sure you use decent table types and optimized queries and that your programmers use database-side transactions (Yes, I've seen programmers implement the transactions on the program side, not a good idea btw) with whatever database system they are using. Check out http://www.developer.com/db/article.php/2235521 [developer.com] for the different table types and the pro/con's about them on MySQL.

Also make sure your hardware is decent. Especially with database systems, you do not want to have downtime because you saved $200 on the hardware. Use RAID5 or even RAID6 if possible, look at optimizing your server with the documentation from the merchant (MySQL AB has some good documentation). Another issue I recently walked into: don't use cheap SuperMicro hardware enclosures you built yourself. I got a hard drive stuck the other day because a power cable slipped under while sliding it in and had to bring the whole machine down to 'operate' it. I have good experience with Apple's hardware (the server hardware), it falls apart quit easily and is easy to maintain without downtime (up to replacing fans and power), you do not have to keep Mac OS X however if you don't want to.

MySQL is definitely an industry-grade solution, especially their latest versions. And they're relatively cheap (free if you want) and have a very good commercial support plan and staff (if you go not-so-free).

No, NOT RAID5! (1)

Dion (10186) | more than 7 years ago | (#17389932)

You do not use RAID5 for anything other than file systems with large files that need only sequential access.

For databases nothing will hurt performance more than RAID5.

A database will update 13 bits here and there, with RAID5 you need to read all of the (potentially multi-MB) stripe into memory to recompute the parity and then write both the data and the parity.

For a database you will need something without parity, but with redundancy, than means: Mirrors.

If you want more space and more performance you add disks in stripesets and then mirror the stripesets (or stripe mirrors).

You enforce your argument about todays hardware being more than fast enough with that RAID5 crazy talk, but only to a point, because if you had worked with databases too large to fit in memory then you'd know that RAID5 is wrong.

I'd rather say that if you have enough money to fit all of your data into memory then you should do so, after you run out of RAM buying cash then you can start adding nice 15k RPM disks in monsterous RAID0+1 configurations.

MySQL, however, is a nice replacement for a csv file, but it's a piece of shit when compared to any RDBMS that takes ACID seriously.

If you can solve your problem with MySQL then feel free to do so, but invest in a UPS and take frequent backups, because you are going to need both.

DB design is the key (3, Insightful)

alen (225700) | more than 7 years ago | (#17380108)

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:DB design is the key (0)

Anonymous Coward | more than 7 years ago | (#17380870)

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 Or use a Multi Genarational Architecture (MVCC Multi Version Concurrent Control) database like Firebird os Postgres. in a MGA/MVCC readers never block writers ! see you !

Re:DB design is the key (1)

BlueWomble (36835) | more than 7 years ago | (#17385602)

This was why we dumped SQL Server in favour of Oracle.

Re:DB design is the key (1)

alen (225700) | more than 7 years ago | (#17390954)

SQL2005 supports this now. We looked at Oracle, but it was too expensive compared to SQL 2005

Re:DB design is the key (1)

BlueWomble (36835) | more than 7 years ago | (#17391590)

Interesting, I found quite the reverse -- SQL Server would have been several times more expensive than Oracle. I guess it depends on what features you require, number of CPUs etc.

Re:DB design is the key (1)

alen (225700) | more than 7 years ago | (#17404782)

sql 2005 everything is in the box

with oracle almost everything is an extra add on from the base product

Re:DB design is the key (1)

BlueWomble (36835) | more than 7 years ago | (#17419100)

Sure is. Bet ya paid more than $5000 for unlimited users though. :)

MySQL should be fine (1)

OnyxRaven (9906) | more than 7 years ago | (#17380152)

"(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)."

MySQL can very happily handle >800 queries per second on a single machine with good indexes. if you've got complicated data in big datasets, go with cluster or reader/writer replication where you can, and have a read-replica for reporting.

Like others have said - any respectable db should be able to do this without problem.

Sounds like you need MySQL Cluster (1)

linuxtelephony (141049) | more than 7 years ago | (#17380342)

MySQL Cluster may be just what you are looking for. Just keep your database schema very simple since there are some gotchas with the cluster back end.

In the end, your real-time call database may need to be mysql cluster for speed and call processing, but other database functions can probably be farmed off to an inno back end or another database entirely (pgsql, sql server, etc.).

Try Oracle XE (1)

1001011010110101 (305349) | more than 7 years ago | (#17380456)

If the supported limits are enough for your app:

Max 4 gb of user data
Using at most a single processor
Using max 1 gb RAM

Oracle its a pretty decent option, and this version its free (as in beer, not as in speech).

Faq here: http://www.oracle.com/technology/products/database /xe/pdf/dbxe_faq.pdf [oracle.com]

I'll answer the easy question. (3, Funny)

Generic Player (1014797) | more than 7 years ago | (#17380672)

"Does Slashdot have any insight to offer?"

No.

First, measure the DB scalability (1)

davecb (6526) | more than 7 years ago | (#17380746)

You control the app, so you can easily log the response time of the database, and measure it with a small but credible test load.

If you send the database a hundred or so requests at a rate that's deliberately slow, you will get an average response time that's a good estimate of the actual internal response time of the database. Let's say it's 1/10 second. With that number (alone) you can predict and plan for the performance you need.

On a uniprocessor, you will get a maximum of 10 transactions per second before a queue starts to build and you start seeing delays due to queuing, so for 1 through 10 TPS, the response time will be 1/10 second.

In fact, as you get closer and closer to 10 TPS, there is a higher and higher probability that two transactions will come in at the same time, and the queue will start to build before you hit 10...

After the queue builds up, every additional request that comes in will need to wait before it get processed. T queue length is calculated using Little's law, Q=XR, where X is offered load and R is response time. A load of 50 requests would yield a queue length of 50* 1/10 = 5, and the average response time at that load would be (40*0.1 + 0.1) = 4.1 seconds

Voila! For a load of 50 TPS and a target time of 1/10 second, you need a five-processor system, easily achieved with 3 dual-core AMDs.

Feel free to send me mail: I have a copy of Neil Gunther's "pdq" queue solver and can easily compute what-ifs based on your measurements and needs. See "Analyzing Computer System Performance with Perl::PDQ", at http://www.perfdynamics.com/iBook/ppa_new.html [perfdynamics.com]

--dave

Does Slashdot have any insight to offer? (-1, Redundant)

Anonymous Coward | more than 7 years ago | (#17381240)

No.

LDAP for call-routing, MySQL for reporting (0)

Anonymous Coward | more than 7 years ago | (#17381612)

If you're comfortable with two databases, consider LDAP (OpenLDAP is free) for the call-routing part and an RDBMS (MySQL is already working) for the reporting part.

With a proper OpenLDAP install, you should be able to handle over 15000 queries per second (PDF reference [openldap.org] ). For redundancy, or if you need more capacity, LDAP replication is straightforward.

As far as keeping the two databases synchronized, it's hard to say without knowing a lot more about your requirements. It may be as simple as periodically dumping the call-routing data from the LDAP server and loading it into the MySQL reporting environment.

Good, Cheap, Quick -- Pick Two (1)

plsuh (129598) | more than 7 years ago | (#17381770)

...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...

We need a database is fast so that it can assign the calls without delays.

...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. Keep in mind that cost matters a lot.

Geez, you aren't asking for much. You have a development team that is inexperienced in the technology, a large anticipated growth factor, complex queries, requirements for high performance on both the incoming call and reporting ends, and low cost. Have you ever heard the phrase, "good, cheap, quick -- pick two"? In this case you have a complex problem domain and high performance requirements, and you want low cost, i.e., "good" and "cheap". Then you're going to have to accept "not quick". I hope you don't expect to deploy this sucker any time soon.

--Paul

Reliability? (1)

AaronLawrence (600990) | more than 7 years ago | (#17384974)

How important is reliability to you? Do you do lots of writes, and if so are they to several tables?

My gut reaction is that this is the kind of situation where you want a "real" database with ACID. But if reliability isn't that important (or you can, for instance, control power down tightly) then maybe something less can do.

From what I read, MySQL is usually considered to be good for situations with lots of reads and a few simple writes (e.g. websites). Is that your situation or not?

Buy proper CTI software (1)

tetrode (32267) | more than 7 years ago | (#17385428)

I'm sorry to tell this to you, but you will be better off buying CTI software such as Altitude (disclaimer: I work for Altitude). You will pay your developers much more implementing all the different CTI possibilities:
  dial, answer, hold, extend, retrieve, conference, transfer, blind transfer, etc, etc.
Also you want to do predictive dialing in the future perhaps, multimedia (web chat, e-mail handling), IVR? It is all in there when you buy it. When you need to write it - a huge amount of work.
When your client wants to switch from Ericsson to Avaya or Cisco? No problem for us, switch a driver and that's it. For you - huge problems.

Convince your client that they buy Altitude + a real database (Oracle or MS SQL Server) and 300K database requests is something we laugh about with the proper hardware.

There still is programming, set up etc to do, but it will be less. And you will end up with a happy client.

Writing your own CTI when you don't have any experience is a recipe for disaster and unhappy clients.

Mark

RDBMS for CTI (1)

ekimminau (775300) | more than 7 years ago | (#17391292)

Several rules to follow: 1) NEVER mix batch and production 2) NEVER mix reporting and production 3) Build on a platform that can scale 4) Do not share your CTI instance with ANY other activity. 5) Explore active:active deployment (such as Oracle 10G RAC) with transactional replication between the active nodes (such as shareplex). Our production CTI database uses a 32CPU cluster with 90GB ram, SRDF/S replicated disk between the sites between 2 facilities and BCV snapshots for performing Backup & Recovery (BUR). We are also taking snapshots hourly to maintain RPO/RTO. YMMV.

Ingres (1)

jregel (39009) | more than 7 years ago | (#17404750)

No-one ever seems to notice that Ingres (not to be confused with Postgres) is now Open Source (GPL) software. As someone who has been using it for several years, I'm somewhat surprised. It's a mature and powerful RDBMS that can scale to very large systems. As an example, we supply systems capable of supporting upwards of 800 users running multiple complex queries on databases that are 80GB+. Of course, this is running on a 4 CPU Sun Fire V890 with 16GB RAM, but the point is that Ingres scales and is used in production for real mission critical applications.

Oh yeah, the obligatory link: www.ingres.com [ingres.com]
Check for New Comments
Slashdot Login

Need an Account?

Forgot your password?
or Connect with...

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>