Real-time data warehousing
We are warehousing real-time data. The data is received at up to T1 speeds, and is broken up and stored into the database in approximately 25 different tables. Currently MySQL is doing terrific, we are using MyISAM tables and are storing 24 hours worth of data but we don't have any users and we need to store 72 hours worth of data. Our concern is that when we start letting our users (up to 200 simultaneous) hit the database, we won't be able to keep up with ingesting and serving data with the MyISAM locking scheme. We have tested Oracle and PostgreSQL which fell behind on the ingest. The current production system uses regular ISAM files, but we need to make a certification which requires a relational database. Also, the current production system doesn't have the feature list the new system has. Is there a better database solution or do you think MySQL can handle it? If MySQL can handle it, would we be better off using InnoDB or MyISAM tables? Thanks, Brad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Real-time data warehousing
Hi, My opinion: If your DataBase is designed for OLTP then MySQL ver 3.23.4x with MyISAM can be a good choice and safety. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Brad Teale [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 17, 2002 6:49 PM Subject: Real-time data warehousing We are warehousing real-time data. The data is received at up to T1 speeds, and is broken up and stored into the database in approximately 25 different tables. Currently MySQL is doing terrific, we are using MyISAM tables and are storing 24 hours worth of data but we don't have any users and we need to store 72 hours worth of data. Our concern is that when we start letting our users (up to 200 simultaneous) hit the database, we won't be able to keep up with ingesting and serving data with the MyISAM locking scheme. We have tested Oracle and PostgreSQL which fell behind on the ingest. The current production system uses regular ISAM files, but we need to make a certification which requires a relational database. Also, the current production system doesn't have the feature list the new system has. Is there a better database solution or do you think MySQL can handle it? If MySQL can handle it, would we be better off using InnoDB or MyISAM tables? Thanks, Brad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Real-time data warehousing
Brad Teale wrote: We are warehousing real-time data. The data is received at up to T1 speeds, and is broken up and stored into the database in approximately 25 different tables. Currently MySQL is doing terrific, we are using MyISAM tables and are storing 24 hours worth of data but we don't have any users and we need to store 72 hours worth of data. Our concern is that when we start letting our users (up to 200 simultaneous) hit the database, we won't be able to keep up with ingesting and serving data with the MyISAM locking scheme. We have tested Oracle and PostgreSQL which fell behind on the ingest. The current production system uses regular ISAM files, but we need to make a certification which requires a relational database. Also, the current production system doesn't have the feature list the new system has. Is there a better database solution or do you think MySQL can handle it? If MySQL can handle it, would we be better off using InnoDB or MyISAM tables? Thanks, Brad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Brad, We're in process of evaluating mysql vs our current Oracle 8 system. Importing data is much faster in mysql than oracle according the numbers we're getting. However, from our benchmarking, Oracle seems to be faster on the queries (no writes to db during query time). The table were running our queries against has 46 coulmns and 14 indexes (some columns indexed twice in multi-column indexes). All queries are based on indexed columns. We've also run into some issues trying to delete indexes, 14+ hours before we killed the db and reloaded data, but I may be something stupid. One note on Oracle, $30,000+ for a single processor licence. From our testing, it looks like the bottleneck is disk I/O not processing power. With Oracle, you have better control over which disks your data resides on which lets you balance disk I/O better. However, for $30k, you can buy 10 15,000 rpm drives, stripe them, and then buy another server for replication of data and still have $25K left over. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Real-time data warehousing
I forgot to mention, we have Oracle in-house, and the machine the MySQL database will reside on is a 2 proc Sun box with 1.5G of RAM. The Oracle databases reside on a 16 proc Sun box with 10G of RAM. The decision to go or not go with MySQL is not based on money, it needs to be based on performance. We currently use Oracle in-house for everything, but its speed hasn't been its selling point, and for this application we need lots of speed. That is why we are leaning toward MySQL, but were not sure if it could keep up with the addition of the user community. I had one other question, how much of a performance hit would we take with MySQL if we connected through MyODBC? Thanks again, Brad -Original Message- From: walt [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 11:47 AM To: Brad Teale Cc: '[EMAIL PROTECTED]' Subject: Re: Real-time data warehousing Brad, We're in process of evaluating mysql vs our current Oracle 8 system. Importing data is much faster in mysql than oracle according the numbers we're getting. However, from our benchmarking, Oracle seems to be faster on the queries (no writes to db during query time). The table were running our queries against has 46 coulmns and 14 indexes (some columns indexed twice in multi-column indexes). All queries are based on indexed columns. We've also run into some issues trying to delete indexes, 14+ hours before we killed the db and reloaded data, but I may be something stupid. One note on Oracle, $30,000+ for a single processor licence. From our testing, it looks like the bottleneck is disk I/O not processing power. With Oracle, you have better control over which disks your data resides on which lets you balance disk I/O better. However, for $30k, you can buy 10 15,000 rpm drives, stripe them, and then buy another server for replication of data and still have $25K left over. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Real-time data warehousing
On Friday 17 May 2002 12:58 pm, Brad Teale wrote: I forgot to mention, we have Oracle in-house, and the machine the MySQL database will reside on is a 2 proc Sun box with 1.5G of RAM. The Oracle databases reside on a 16 proc Sun box with 10G of RAM. snip How are your apps written? We use OTL libaries from http://members.fortunecity.com/skuchin/home.htm which are compiled into our C/C++ code. Moving our apps from oracle to mysql only requires changing 3 or 4 lines per call to the db in the code. Its not odbc compliant, but still allows our apps to be farily portable and fast. We debated rewriting our apps to be ODBC compiant, but figured that was one more layer for bugs and we'd have to switch db platforms 4 times for it to be cost effective. Have you tried Oracle on Linux? We did some testing before Oracle told us the cost of migrating our licence from Oracle8/Solaris to Oracle8i/Linux. We benchmarked our current db server, Sun Ultra single processor 768MB ram, against a 600Mhz 500MB ram Intel/Linux box. The Linux./8i/Intel box smoked our current db server. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Real-time data warehousing
We have used the predecessor to the OTL for many of our apps and were planning to use the OTL for the new system. I thought the OTL used ODBC to make its connection with databases other than Oracle. I know the OTL supports Oracle natively. Sadly we cannot move to Linux. We managed to get our web servers on Linux, but the big iron will always be Sun here (Company policy). There has been talk of getting Oracle 9i? because Oracle has told us it is much faster, but we are not holding our breath. Thanks, Brad Teale -Original Message- From: walt [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:27 PM To: Brad Teale Cc: '[EMAIL PROTECTED]' Subject: Re: Real-time data warehousing How are your apps written? We use OTL libaries from http://members.fortunecity.com/skuchin/home.htm which are compiled into our C/C++ code. Moving our apps from oracle to mysql only requires changing 3 or 4 lines per call to the db in the code. Its not odbc compliant, but still allows our apps to be farily portable and fast. We debated rewriting our apps to be ODBC compiant, but figured that was one more layer for bugs and we'd have to switch db platforms 4 times for it to be cost effective. Have you tried Oracle on Linux? We did some testing before Oracle told us the cost of migrating our licence from Oracle8/Solaris to Oracle8i/Linux. We benchmarked our current db server, Sun Ultra single processor 768MB ram, against a 600Mhz 500MB ram Intel/Linux box. The Linux./8i/Intel box smoked our current db server. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Real-time data warehousing
In the last episode (May 17), Brad Teale said: We are warehousing real-time data. The data is received at up to T1 speeds, and is broken up and stored into the database in approximately 25 different tables. Currently MySQL is doing terrific, we are using MyISAM tables and are storing 24 hours worth of data but we don't have any users and we need to store 72 hours worth of data. Our concern is that when we start letting our users (up to 200 simultaneous) hit the database, we won't be able to keep up with ingesting and serving data with the MyISAM locking scheme. You probably don't want to be doing your archiving and reporting on the same database. Set up replication, and do your queries on the slave while the master gets the updates. Moving to InnoDB tables might help with table contention, since it's got row-level locking. If there are only updates and few changes, then even moving to MyISAM will get you a big win, since appending a row doesn't lock the table. We have tested Oracle and PostgreSQL which fell behind on the ingest. The current production system uses regular ISAM files, but we need to make a certification which requires a relational database. Also, the current production system doesn't have the feature list the new system has. Is there a better database solution or do you think MySQL can handle it? If MySQL can handle it, would we be better off using InnoDB or MyISAM tables? ISAM is deprecated and only included for compatibility with old files. You should switch to MyISAM at minimum. http://www.mysql.com/doc/I/S/ISAM.html http://www.mysql.com/doc/M/y/MyISAM.html -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
