Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am trying to determine how much RAM is going to be required for processing a database migration (company wishes to purchase more RAM for me). The factors are:

  • Approximately 3 GIG's of data being transferred between Oracle and MySQL
  • Approximately 900 GIG's of File Data being read
  • Using Java for creating my processing script

I'm curious to know if there is any way of mathematically coming up with a formula, or if just ballparking an extra 2 gigs be enough? I'm running with a gig of RAM at the moment.

share|improve this question

migrated from superuser.com Oct 29 '09 at 15:24

This question came from our site for computer enthusiasts and power users.

add comment

2 Answers

One gig is indeed very little for this process to take place without both SQL servers paging frequently to disk. Even more so if it just so happens both servers are on the same machine.

The final dimension of the data on MySQL (3 Gb) isn't particularly worrying. Any modern 32bit 3Gb desktop computer can handle that. It's the queries being ran on the 900 GB source data (Oracle) that will be overkill.

I understand you are worrying about RAM at the moment, but other factors that would greatly increase performance would be HDD speed and number of processor cores. The first because there will be a lot of HDD access as the queries bring data from the database on disk, the second because both sql servers excel at taking full advantage of multicore processors.

But as for RAM:
The more RAM you have, the more data a query can extract from disk. Many, many, many factors will affect how important RAM will be. The most important perhaps being if your queries will include subqueries and similar sql constructs? If so, Oracle engine will extracted data from disk into memory temp tables and run further queries higher on its execution plan. The more data that can reside in memory, the less times oracle needs to repeat this process and the less data needs to be paged back to disk in order to construct the final result.

And yet, your database mass (I'm missing the proper term, but essentially how large the queried tables are on oracle) can reduce some of the requirements if it just so happens the queried tables on oracle aren't anything more than a 1 or 2 GBs in size.

Necessarily the quality of your queries will affect the amount of RAM needed the most. The order of business here is indexes, indexes, and more indexes. Avoid full text search like the plague, for instance, and only resort to it when you must, not when you can. Avoid non-indexed search too and since this is a migration task you are engaged into) it is ok to index more columns of your tables than you normally would. Remember, you will only be reading from Oracle. Finally you must ensure that, on the case of subqueries you optimize them to the best of your abilities so that you don't extract too many unnecessary rows.

How much RAM?
The exact amount of RAM cannot be formulated, I'm afraid. Only after you constructed your queries and were ready to start the process would you be able to have an idea of the amount of data being worked upon in a single transaction.

Pick your most heavy transaction (the one you know will generate the most amount of data while being worked upon). Make an estimate of how many rows it will generate during the most intensive part of the execution plan. Get the total memory needed per row (sum the memory needed by each column) and multiply by the number of rows. This is the amount of free RAM you would ideally need to avoid paging.

One final optimization:
A transaction may look to you like a nice optimization practice. But beware. Your choke point in all this process will not be how fast you can read data from a 900 Gb database, but how much data you can hold in memory before it gets paged. Because transactions will execute as a unit, further memory will be needed to hold temporary results and rollback data. Avoid transactions on those queries where you will be working with large volumes of data.

Also, only extract needed columns during the whole process. SELECT * is really not a good idea unless you do need it.

Conclusion:
I couldn't give you a RAM amount as you requested. You must understand it would be unfair to do so. I would be lying to you. The amount of RAM you need is not only affected by the volume of data being worked on each query, but even by (even if to a less extent) your HDD performance since it is acceptable in your case that some data paging will occur.

Your processor and operating system also have a word on the amount of RAM you can use. As you know, if they both are 32Bit nothing more than ~3.8 will be used. If they are 64bit you can cram 1 Terabyte of RAM in it and not worry about it anymore.

Finally separating the Oracle server from the MySQL server will also reduce your RAM requirements considerably. The Oracle server machine becomes your priority and the 1 Gb RAM MySQL server machine will more than happily process the incoming data.

But one thing I can say to you, do not try this with 1Gb of RAM. If it's a 32bit machine you have there, upgrade it to the full 4Gb.

All the best.

share|improve this answer
    
This is an incredible answer. Thanks! –  Jonathan Kushner Oct 29 '09 at 15:00
add comment

With the current price of RAM your company is not making a very big investment here.
So it would be ridiculous to go for 3GB of RAM and find out that it's not enough.

Go for 4GB of RAM, even though in a 32-bit O/S the video card will eat out a part of the 4th GB.

Non-mathematically speaking, 4GB should be enough. If not, then your company's next investment should be in a 64-bit O/S (and maybe a new computer) with 8 GB.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.