0

Previously, I did a simple web application for an enterprise client using PHP and MySQL.

After some time, it has grown to a point where I realized after talking with people here that I need to treat it as two separate apps OLTP and OLAP.

I have tentatively decided that

  1. the OLTP will still continue to run using the current PHP codebase and MySQL
  2. the OLAP will use PostgreSQL
  3. both should appear as a single app to the end user. This is to avoid inconsistencies and too many logins.
  4. the OLAP should help with sending out scheduled reports via email

I want to avoid a "big bang" approach. At the same time, I don't want to do the OLAP as an isolated app.

I want to slowly evolve to the point where transaction processing and the analytic processing functions are modular and consistent.

How do I approach this and bearing in mind, I currently have only 1 physical server that acts as both database and application server at the moment?

I am most familiar with PHP and am okay to use Python.

The physical server is running ubuntu 14.04 and nginx as web server.

The normalized MySQL currently is at 20mb and growing at a rate of 1 mb every month or so.

EDIT

My server shows 8 when I run grep -c processor /proc/cpuinfo

Each processor (I think?) shows the following stats:

vendor_id   : GenuineIntel
cpu family  : 6
model       : 44
model name  : Intel(R) Xeon(R) CPU           L5630  @ 2.13GHz
stepping    : 2
microcode   : 0x13
cpu MHz     : 1600.000
cache size  : 12288 KB

When I type free -m

             total       used       free     shared    buffers     cached
Mem:          7972       3052       4920         46        226       2039
-/+ buffers/cache:        785       7186
Swap:            0          0          0

When I ssh in

System load:  0.28                Processes:          229
  Usage of /:   13.9% of 126.32GB   Users logged in:    1
  Memory usage: 10%                 
  Swap usage:   0%
  • 2
    Using two different databases is a huge complexity leap. Your database is "tiny", why do you want a second db engine (of a different type too)? – Mat Feb 2 '15 at 7:07
  • MySQL database is for the current transaction processing. The new PostgreSQL for the Analytic Processing as highlighted by my previous question in dba.stackexchange.com/a/90765/56827 – Kim Stacks Feb 2 '15 at 9:04
  • 1
    I understand that, and the answers given - but you need to take into account the size of your database, the time you have to implement this (=> cost) and maintain it. If you were starting from scratch with an "Enterprise" target in mind (terabytes of data), then the added complexity of two separate (and possibly very different) tools for OLTP vs OLAP make sense. But for a small-ish (alsways hard to define) setup, the added complexity is an enormous overhead. (Switching everything to a single Postgres database if you can't do it with MySQL would make more sense to me. That's a big "if".) – Mat Feb 2 '15 at 9:15
  • I appreciate an alternate viewpoint. Suppose I do both OTLP and OLAP functions from the same database as you suggest, how would you recommend I add more OLAP functions assuming I prefer PHP? – Kim Stacks Feb 2 '15 at 13:23
  • I'd be rather surprised if it wasn't easier to either (a) do your OLAP work in MySQL too, or (b) port the whole lot to PostgreSQL. Porting across DBs is generally pretty simple for small apps... and of course you have good regression test suite coverage already, right? – Craig Ringer Feb 2 '15 at 13:40
1

OK - I'm going to slightly revise my advice in the previous thread.

If you have only 1 physical server, you're always going to have problems with OLTP conflicting with OLAP - i.e. disk bottleneck. It would be far easier to use PostgreSQL as the server for both than the other way round - see my previous comments on the richer PostgreSQL SQL functionality.

We also don't know what sort of activity your app has, nor do we know your CPU, RAM and disk config (RAID?).

In the light of what you have written above, my advice is to use PostgreSQL for both - maybe two separate schemas on the same instance of PostgreSQL - or possibly two instances of PostgreSQL on the same machine. This is more or less what @Mat and @CraigRinger said. The logic behind separating the schemas is that when and if you get a second server (which you should), parititioning the functionality between the two machines will be trivial.

If most of the functionality of your app to date is of a CRUD nature, then porting it to PostgreSQL should be relatively painless, but, again, YMMV.

  • Disk bottlenecks? With a 20MB database? Even a low-end server nowadays can have 50 databases like this cached entirely in RAM. – Daniel Vérité Feb 3 '15 at 1:37
  • I should have prefaced my comment by saying that (as you point out) it would not apply to a "tiny" database, but is more of a general principle. Point taken. – Vérace Feb 3 '15 at 1:55
  • Thank you. I have added in the server stats in the question. Would that help? – Kim Stacks Feb 3 '15 at 6:11
  • What about the outputs of vmstat and iostat during a period of average usage? At first glance, it appears that your system is under a light load, at least CPU wise - need other data for RAM and I/O. – Vérace Feb 3 '15 at 6:23
  • How do I measure vmstat and iostat during a period of average usage and what other data do you need for RAM and I/O? – Kim Stacks Feb 5 '15 at 0:05

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.