Database Systems
by JustEtc Publications Ltd
Distributed by justEtc |
Firebird as an Alternative to Oracle/MS SQL Server/Interbase/Access
Note:
Firebird can be a very good alternatives to Oracle/MSSql Server/Interbase. First of all, it is free. It can be good enough for upto medium level of enterprises. Supported database size is more than 11 terabytes. For a single table 20GB. It also implements ACID properties well enough. Implements two phase commit and in record level locking and hence can provide more concurrency and connection.
It also implements MGA to provide recovery that also Oracle/MSSQL server imitated from interbase/firebird.
A well designed database and a well designed network using Firebird will be strong enough to support a mid - size company. Also, if the database is designed well and used the clean normalization, and used clean indexing, the query will be faster enough.
Linux platform is more suitable than windows platform for Firebird.
In 32 bit environment, Firebird server version support 2 GB of ram and hence around 450 concurrent connection. This is due to memory addressing in 32 environment, required cache size and for similar reasons.
In Linux 64 bit versions Firebird can provide better service and more concurrent connections. Firebird is not stable and reliable enough to be used with windows platform in 64 bit environment. Though, Firebird project, hopes to work on this.
Firebird, has a dedicated community to support the product. They provide supports through a discussion forum. (May be .. with some other ways). Firebird team also hopes to continue the project to fullfill future demand.
Being opensource project the database system seems will be strong enough and well designed and well featured to meet customer demand.
For details please check:
A detail discussion can be found here.
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_enterprise_firebird
Note:
Firebird can be a good option for a Midsize company in Bangladesh
Features of Firebird
What is firebird:
The software has two main components: the database server, which lives on the same host machine as the databases, and the
application interface, commonly referred to as “the client library”. The client runs in the client pc or in the middleware
for accessing through internet browsers.
The server's executable is less than 1.5 Mb and a full server installation, including all tools and documentation, takes
up less than 10 Mb.
Versions:
1.5 most stable and tested
firebird 2: Mid of year 2006
Vulcan: A parallel project used by SAS to move many of its business applications from Oracle to Firebird.
Purpose: redesigning the threading architecture of the database engine
Is Firebird “Enterprise Capable”?
stability, scalability, availability, capacity, interoperability and autonomy.
Stability
Stable enough, supports strongly ACID properties
Scalable??
Good enough but not extremely scalable
Firebird's upscaling is merely a question of adapting the environment. The same engine comfortably handles anything from
being embedded in a stand-alone client application, through to a classical two-tier client/server LAN of around 750
potential users, to incorporation in a multi-tier solution for thousands of potential clients. Database growth is
effectively limited only by the disk storage available and can be split across multiple hard disks.
Through smart replication and good connection management in the access layers, the workload of a busy system can be
distributed across multiple servers. For example, a well-resourced central server can handle the interactive demands of
LAN, intranet or extranet (or all together) while a replicated server takes care of long-running jobs that need to isolate
a snapshot of data for lengthy periods.
Availability
Among its users, Firebird has a reputation for being bomb-proof.
It uses optimistic locking at record level, drastically reducing the wait-time overheads in comparison to others where
read-write transactions lock entire sets, even tables, pre-emptively. No tuning is ever required to facilitate handling
varying workloads through the day or week. A database does not have to be shut down for backups. It can be replicated or
shadowed for almost instantaneous cutover in the event of disk failure. It is robust and recovers immediately from power
failure, without loss of database integrity.
Supports online backup.
Firebird is a popular choice for enterprises needing continuity of service around the clock. Command-line tools are
distributed with the software for all administrative activities, allowing regular housekeeping to be automated as
scheduled or on-demand jobs. A Services API is also available to wrap admin tasks into a program or service application.
Firebird Databases as the Back-end to Enterprise Software Systems
Who Uses Firebird?
Because Firebird is free, there are no licences to count, no beans to count. It is known, from reputable enterprise surveys, that Firebird is chugging away on hundreds of thousands of production sites around the world. The following is a selection of companies and organisations that are publicly known to be using Firebird:
Broadview Software Ltd, Toronto, Canada, vendor of information and control systems and online services for broadcasters worldwide
Morfik P/L, Hobart, Tas., developers and vendors of WebOS development suite for construction and maintenance of interactive websites, stores web objects in a Firebird meta-layer (system database) as well as Firebird user data.
Communicare Systems Pty Ltd, Perth, WA, vendor of patient management and medical records software for hospitals, clinics, medical practices and mobile health units across Australia.
“The Examiner” newspaper, Launceston, Tas., high availability(24/7) business, information, production and news systems.
U.S. Navy, broad range of management and logistical systems
Frontrange Solutions USA Inc., Colorado Springs, U.S.A, as the back-end of the highly scalable, award-winning integrated CRM, service management and business systems “Goldmine” software suite.
British Rail, U.K., timetabling, bookings, accounting and information systems for national railway passenger network.
Deutsche Presse-Agentur GmbH, HQ in Hamburg, Germany, largest press agency in Germany, provides a worldwide service to newspapers, magazines, TV and radio news networks.
KIMData, Munich, Germany, business intelligence systems and data warehousing for German hospitals.
Comparison: FireBird, MySQL, PostGreSQL
PosTGRESQL Provides better query responsetime than firebird and MySql. Firebird stands somewhere in the middle. A
little longer response time than PostGreSql.
http://benchw.sourceforge.net/benchw_results_open3.html
Several PostGreSQL versions are compared at:
http://benchw.sourceforge.net/benchw_results_postgres_history.html
Several MySQL versions are compared at:
http://benchw.sourceforge.net/benchw_results_mysql_indexes.html
A good comparison among these databases will be found at:
http://www.geocities.com/mailsoftware42/db/
Mentionable comments
PostGreSQL Supports 400+GB DB.
One source: As for each connection it forks new thread, it is bit slower than MySql: Another source: postgresql is faster
than mysql. Seems the truth is: for single connection postgresql can be faster but for multiple connections mysql is
faster. just an information, not sure yet.
New PostGre Features
http://www.postgresql.org/docs/whatsnew
Some mentionable comments: Supports SMP and almost linearly improves performance witth multiprocessor system.
Supports roles, in/out parameters/two phase commit/table partitioning shared row locking(firebird record level
locking), 64 bit shared memory hence supports two terabytes of ram
PostGreSQL capability
4.4) What is the maximum size for a row, a table, and a database?
These are the limits:
Maximum size for a database? unlimited (32 TB databases exist)
Maximum size for a table? 32 TB
Maximum size for a row? 400 GB
Maximum size for a field? 1 GB
Maximum number of rows in a table? unlimited
Maximum number of columns in a table? 250-1600 depending on column types
Maximum number of indexes on a table? unlimited
One limitation is that indexes can not be created on columns longer than about 2,000 characters. Fortunately, such indexes
are rarely needed. Uniqueness is best guaranteed by a function index of an MD5 hash of the long column, and full text
indexing allows for searching of words within the column.
Source:
http://www.postgresql.org/docs/faqs.FAQ.html
4.20) What replication solutions are available?
Though "replication" is a single term, there are several technologies for doing replication, with advantages and
disadvantages for each.
Master/slave replication allows a single master to receive read/write queries, while slaves can only accept read/SELECT
queries. The most popular freely available master-slave PostgreSQL replication solution is Slony-I.
Multi-master replication allows read/write queries to be sent to multiple replicated computers. This capability also has a
severe impact on performance due to the need to synchronize changes between servers. PGCluster is the most popular such
solution freely available for PostgreSQL.
There are also commercial and hardware-based replication solutions available supporting a variety of replication
models.
Important Notes
Server Configuration
A number of postgresql.conf settings affect performance. For more details, see Administration Guide/Server Run-time
Environment/Run-time Configuration for a full listing, and for commentary see
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html and
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html.
Hardware Selection
The effect of hardware on performance is detailed in http://www.powerpostgresql.com/PerfList/ and
http://momjian.us/main/writings/pgsql/hw_performance/index.html.
EnterpriseDB kind of commercial but cheap based on postgressql
PostGreSQL Limitations and solutions
http://searchopensource.techtarget.com/originalContent/0,289142,sid39_gci1172668,00.html
PostGreSQL Windows Installer
http://pgfoundry.org/projects/pginstaller/
Important Links for Mysql, PostGreSQL, Firebird, EnterpriseDB, Ingres
Firebird and postgresql:
http://www.linuxjournal.com/node/7010
Important comment: easy to migrate from oracle to firebird than to postgre or mysql. From interbase to firebird will be better as they are similar.
http://forums.devshed.com/firebird-sql-development-61/mysql--vs--firebird-sql-62269.html : Firebird is far better choice than mysql
As for Performance, MySQL will outperform Firebird on almost all tests on local machine......
On a Network Server a properly designed Firebird Server will outperform MySQL on any Test and the network-Traffic generated by Mysql will be many times higher than Firebird
So you decide the one you need according to your needs .....
This link also compares fb with potgre and favors fb as the author's background is borland
Why Postgress is better than firebird
Firebird is not as good as PostgresQL (Score:0)
By Anonymous Reader on 2004.12.03 2:21 (#102703)
And here is why:
1. Firebird has no temp table support.
2. Firebird uses several SQL modes i.e DSQL which only works client side and PSQL which only works in procs. All sql works in Postgres functions.
Try creating a table or a user in a Firebird proc...ooops can't be done.
3. Firebird databases grow out of control and have to be backed up restored on a regular basis.
4. Firebird has a concept of a OAT (oldest active transaction counter) if this counter gets stuck all transactions get held up and your DB size goes through the roof and performance goes to the basement.
5. Firebird has virtually no built in functions and flakey UDFs must be used. Postgres on the other hand has every function you can think of.
6. Postgres has many rich procedural languages including perl,TCL,Java and C# in the works.
I could go on.... Version 8 of Postgres which will be released soon adds point in time recovery, Try except error handling in functions, table spaces and more.
I converted all my apps from Firebird 1.5 to Postgresql 7.4 and I would never use Firebird again on Unix or Windows
Why PostGress better than FireBird: This comparison may not be accurate though.
Postgres Vs Interbase/Firebird (Score:0)
By Anonymous Reader on 2004.12.03 18:23 (#102740)
http://www.vitavoom.com/postgresql.html
Firebird has not true text type you must use the clumsy blobs and weird blob subtypes.
Postgres is much closer for enterprise use than Firebird.
almost all of this applies to Firebird as well as interbase.
PostgreSQL vs Interbase
PostgreSQL...
* ... has no "dialects" or other weird features.
* ... has no "backdoors" which can compromise your whole company's security and business (check here and here).
* ... supports unlimited row sizes, unlimited datasebase sizes, tables up to 16TB (!!), unlimited number of rows, unlimited indexes for table and 1Gb per field.
* ... has a boolean field.
* ... can cancel a query asynchronously (Interbase 6.5 - the commercial version - seems to support it now).
* ... has a flexible (not fixed) types system, and supports more types then Interbase. You can always add your own types at your wish, or change existent types behavior. PostgreSQL even has geometrical and IPv4 types support (!!).
* ... supports inheritance.
* .. supports flexible full text indexing through OpenFTS.
* ... has a much more sofisticated locking mechanism (MVCC).
* ... has arbitrary precision numeric fileds (numeric type).
* ... is resistant to crashes and power failures (by using it's logging system, MVCC and chepoint)). Although it is technically possible that a database gets corrupt, we at Vita voom never saw it (except for corrupt media storage of course).
* ... supports functions (whose can be used as stored procedures). These functions current can be written on SQL, pl/PgSQL (a language similar to Oracle's pl/SQL), TCL, Perl, and Python, C, C++ (or other compiled languages) but it's not limited to them.
* ... can have functions to define default values for columns (providing ultimate default value flexibility).
* ... supports much more arrays types then Interbase, they are more flexible and can support much more elements.
* ... supports rules.
* ... supports "fetch" SQL command to get only a limited number of rows at a time, making queries more responsive and resource economic.
* ... has regular expressions support (for searches and operations).
* ... has a 'EXPLAIN' command which will show will how it will perform a query, so that you optimize it.
* ... has statistics about database usage which can be used to optimize queries and indices.
* ... has sequences.
* ... has more built-in functions then Interbase (or any other open source RDBMS).
* ... supports indexes on functions.
* ... has broader subselects support then Interbase.
* ... has a more flexible BLOB fields support.
* ... can limit the number of rows retrieved at at time (with the 'LIMIT' keyword).
* ... is more standards-compliant then Interbase.
* ... allo
Speed: Firebird vs. PostGreSql
Please read.
According to this article postgresql faster than firebird. But I also got information/tests where firebird is faster than postgresql.
http://archives.postgresql.org/pgsql-advocacy/2003-06/msg00280.php
However, Speed may not be the only factor to select a database product. We can improve raw speed in many alternate ways, hardware solutions, ram, faster storage, faster CPU. To compare speed issue, efficiency issue, we need to take a look how the query optimizations are implemented in databases. How, the joins, inner joins, outer joins are implemented. How multiple joins are implemented. Also, indexing is a factor that also affects performance issue. We can try creating same database and using a queries that are simple to complex and run on both databases and see, how well they perform.
Postgresql supports SMP (multiple processor system) and provides better efficiency in multiprocessor systems. Firebird, is not still matured in SMP environment.
Firebird supports 32 bit environment, still not stable/reliable enough for 64 bit env. It supports max ram of 2 gb and hence 150 - 450 concurrent users. Postgresql supports 2 TB of ram in a single server. For both of them, we can implement multiple servers with master/slave relation for better performances.
Tools to create ER diagrams from a database dynamically
Microsoft Visio, and dbVisualizer provide features to create ER diagrams from existing database tables.
Why needed?
ER diagram is very useful to understand the total
database structure that helps a new programmer to work on the existing databases more efficiently.
Visio:
While creating Database diagrams, a menu item named database is displayed. Reverse Engineering option under Database menu helps to create ER diagram from
existing database tables. Before that, target database drivers for visio need to be installed. If the database is in db2/mysql/postgresql, then
db2/mysql/postgresql drivers for visio should be installed. From Database/options/drivers, the driver needs to be configured. Afterwards, Reverse Engineering
option may be used to create the ER diagram. There is no suitable driver for postgresql databses for Visio. ODBC drivers may be used for the purpose (the ER
diagram may not be accurate from ODBC driver for postgresql).
dbVisualizer:
In dbVisualizer, when the TABLEs group/option is selected in the left pane in the right/middle pane there will be a references option. When the references
tab is selected, an ER diagram is displayed using all tables. The ER diagram can be displayed as Hierarchical, circular, orthogonal, or organic. There are
options for the purpose.
When a single table name is selected, the table and the associated tables are displyed in an ER diagram.
In the rightest column, selected tables option allows to create ER diagrams only for the selected tables. When 'selected tables' is clicked a list is
displayed in a dialogbox. Tables can be selected from this list (use control key). Build graph option creates and displays the ER diagram.