Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread EDB performance on large files?
Sat, Feb 9 2013 3:44 PMPermanent Link

Mario Enríquez

Open Consult

Hi everybody,

Currently we're in the development phase of our first EDB application and I want to share some concerns regarding our database design and EDB performance expectations.

In our database we've some tables where we expect a high transactional activity. The first table has 42 columns and a RowSize of 2960 and the second has 32 columns with a RowSize of 1400. The first table is the master of the second.

When I saw the RowSize value of both tables querying the Information.Tables (I assume that this value represents bytes) I did play some number and came with the following:

For table one (RowSize=2960) we expect an increase of 1.6 millions records a year aprox. and for Table two (RowSize=1400) we expect at least twice that with a total of 3.2 millions records.

Projecting the first 5 years of operation, both tables could reach 22GB and 20GB respectively.

Being new to EDB we're worry about this being to much for EDB and performance could suffer.

Does anybody has experience with Tables this size with EDB care to share your experiences?

The application will we used by 20~25 concurrent users.

Regards,
Mario
Sun, Feb 10 2013 7:37 AMPermanent Link

Adam Brett

Orixa Systems

Mario

I have a number of installations in the low gigabyte range, which is only about 10x smaller than your example. I know there are others with far larger systems who may chime in on this thread.

My quick comments would be, with decent indexes no "normal process" (inserting, editing/updating, deleting) will take very long. Even my largest tables don't seem to show any performance on a "select ID from table" if the ID has a key.

If you are needing massive summaries or really complex selects, which might be slow to return (i.e. totals for all of last year's data v. all this year) write a VIEW & those will also return quickly, after the first time they are run.

My main problems with larger DBs in EDB is the backup process, as this does put a bit of a lock on the DB and takes some time.

To get around this I use EDBs replication features, so rather than a backup process I can have 2 sync'd versions of a DB on 2 sites (with update JOBs between the 2), and then use JOBs on the "backup" site to do the actual backup. This can run without any performance hit for the main server.

It is unavoidable that a large db generates a large backup file & most customers want these stored off-site for safety. So doing it as I have described solves 2 problems in 1 hit.

--

Overall I have many more performance issues with network traffic and bottlenecks there than I do with EDB or the EDB Server.

I would be curious to hear comments from others ...
Tue, Feb 12 2013 11:41 AMPermanent Link

Barry

Mario,

The only way to know for sure is to construct the database schema and fill it with data. Then have two or more computers simulate 25 users accessing it. This should be done *before* you write the application. Granted this will take a few days to implement, but you have a greater understanding of the load factor that the database can handle and the hardware required. It will also reveal bottlenecks in your table/index structure. You can then use this software on other projects to test other databases, not just EDB.

If your data is generic enough, there are a few software packages that will generate data for SQL databases, either as CSV files or SQL Insert statements. And there is load testing software, some open source, that can load test the database.

You can then report back to your client with relative certainty, that it will require this amount of hardware to handle this number of users.

Barry
Tue, Feb 12 2013 8:39 PMPermanent Link

Mario Enríquez

Open Consult

Barry,

You're probably right, and we should have made a tougher test of EDB before committing.

The thing is that we did not test to this extent and now we're in at an advance stage of developing where it might get costly to switch to another product.

It is my "unfounded" opinion that EDB should work fine, but I was hoping to read some real word experiences from other forums members who might had reached (or exceeded) this level of stress on EDB to ease my concerns and perhaps share some tips... Wink

regards,
Mario
Thu, Feb 14 2013 11:04 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Mario,

<< Being new to EDB we're worry about this being to much for EDB and
performance could suffer. >>

Well, that's getting up there in terms of size, but EDB should be able to
handle it provided that you are careful with your queries, especially for
reporting.  Just make sure that all/most access is optimized and that you
don't have users executing grouping/summing queries over the entire main
transactional tables every few minutes, and you'll be fine. Wink

The good thing is that there good options for using replication to avoid
concurrency issues, if they arise.  For example, you could easily create a
scheduled job on the server that replicates data to a slave server/database
that is used for general end-user reporting, if that is what you want.  This
is also useful for offloading data for archival purposes when the data is
used for reporting only and isn't useful for the main transactional
application.

<< The application will we used by 20~25 concurrent users. >>

That will certainly help a lot - most of our bigger customers that are using
multi-GB databases are also slinging 100+ concurrent users on a single
server.

Just remember, if you encounter any issues, just contact us and I'll be able
to help you through it.  There's always a solution. Smile

Tim Young
Elevate Software
www.elevatesoft.com


Thu, Feb 14 2013 12:09 PMPermanent Link

Mario Enríquez

Open Consult

Tim,

<<
Well, that's getting up there in terms of size, but EDB should be able to
handle it provided that you are careful with your queries >>

That's what I was thinking, but thanks for your response.

We'll take extra care with queries that's for shure. Wink



Regards,
Mario

Image