1

I have a Django application using Postgresql 9.3 that is about to grow in load to about 300 inserts per minute (peak load) and about 6 million rows per month in one table. Also there should be a lot of queries on that same table, nothing complicated, just a sum grouping by and indexed field.

The table looks like this:

        Column        |           Type           | Modifiers                                
----------------------+--------------------------+-----------
 id                   | integer                  | not null default nextval('seq'::regclass)
 commercial_entity_id | integer                  | not null
 commercial_branch_id | integer                  | 
 when                 | timestamp with time zone | not null
 currency_id          | character varying(3)     | not null
 amount               | numeric(10,2)            | not null
 loyalty_account_id   | integer                  | not null
 code                 | character varying(16)    | not null

Can anyone tell me how much hardware should I provide for this load or what would be a good way to handle this?

Right now it is running on an Amazon AWS S3 m3.large with 2 vCPU and 7.5 GB of RAM. I guess this would not be enough but I don't have any real world experience to know better.

Update:

After some help from Meta I reformulate my question as this:

How you should go about determining what sort of hardware would I need to handle this load? Where can I learn another changes that could be done to the database to improve its performance?

  • 6
    an example of the rows and what type of queries may help people try to predict things, if the row is (id, datetime, value) and is used for a basic tracking that things happened at a time, that's not a lot, but if it's got 20+ columns and is being used in lots of joins (potentially on itself) then that drastically changes the requirements you're after – Ste Bov Oct 1 '15 at 11:30
  • I don't think the web server has a problem and I have at least some idea about how to scale it (cache, more instances, load balancing) – F.C. Oct 1 '15 at 12:32
  • 4
    The table definition is an important improvement to the question, but it's still too vague. "How much hardware" ... are you talking about RAM or CPU or something else? And describing just the one table is not enough to decide hardware. Describe your whole situation and what hardware you have (or have in mind) right now. – Erwin Brandstetter Oct 1 '15 at 14:05
3

There are basically two ways to answer your question: speculation and simulation.

For the former you still have not provided much useful information, such as:

  • What is your current workload and how does your server behave today?
  • What are your current data volumes?
  • What are you planning to do with your 6 million new rows a month? Keep them indefinitely? Archive them after 3 months?

As for simulation, it would likely take you only a day or two to clone your existing database, creating a test system; download and install Apache JMeter; write a simple test plan generating your 300 inserts/second plus your queries; and run it to see how the system actually behaves under your projected workload. This will let you (or a temporary consultant, as suggested by others) to identify performance bottlenecks and determine whether they can be resolved by throwing additional hardware at them.

  • Currently I have almost no load, just a couple of insert per hour, because the system is just starting to be deployed. But based on the size of project I would like to have a plan to handle 300 inserts per minute – F.C. Oct 1 '15 at 21:23
  • In that case you have no basis for speculation, and simulation is the only approach that can realistically help you. 300 inserts/minute and 6 million rows a month doesn't sound like particularly high load to me. – mustaccio Oct 1 '15 at 21:24
0
  1. How you should go about determining what sort of hardware would I need to handle this load?
    • Profile your expected load on your current hardware. If the hardware can't handle the load find a way to reduce the load, or upgrade your hardware.
    • Profile your expected load on virtualized/cloud base hardware, scaling as required.
    • Guess based on past experience and then use one of the above options to fine tune.
  2. Where can I learn another changes that could be done to the database to improve its performance?
    • Leverage one of the many resources on the Internet where database optimization is discussed.
    • Take a formal course in your database targeted at learning how to optimize performance.
    • Hire a temporary consultant to give you advice and/or specific training.
    • Hire a senior DBA who has the skills, and can train others.
  • thaks for your response Erik, any advice specific to tables with several millions of rows in a Postgresql database? – F.C. Oct 1 '15 at 21:13
  • 1
    @F.C. Nothing specific because I don't have the information to make a specific recommendation. You would get better answers to your question profiling your current setup under simulated load, and asking questions based on discovered bottlenecks. – Erik Oct 1 '15 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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