Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2015
    Posts
    3

    Please suggest a database for my needs.

    Hello

    I run a simple price tracking website, that was running on MySQL before the database got irreversibly corrupted (backups didn't help).
    So taking this change to migrate to a different , more appropriate , more reliable database.

    My Requirements / setup:

    Hardware :
    512MB RAM
    2 core cpu
    VPS Open VZ,

    Software: PHP

    Table Schema:
    7 tables(max columns 20)
    1 table is big, about 4 GB with about 80million rows.

    Hits: low. never expected to go above 50,000/day
    Writes:
    a) max 2000 one row writes( insert/update) spread throughout the day, initiated by users
    b) A script that runs for 3-4 hours, almost continuously inserting/updating 500 row queries that take about a second. Sleep between queries is also 1 second. If it matters the CPU/memory usage of this script is okish on my server. Not a problem.

    Options I am thinking about:
    SQlite : I thought it was ideal, the one file one database was really attractive. But it doesn't seem to to be designed with concurrent writes in mind at all.
    Postgresql : Not sure, seems like an overkill for my small site. Is it ?

    Any Other: ?

    Thanks

    Please ask if any more info is needed. And I hope this isn't just another which db is BEST post.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    different, more appropriatre more reliable.......
    well MySQL is pretty good on all 3 categories
    did you investigate why
    ..the original corruption occurred, what caused it? was it a hardware fault, a software fault, an application software fault
    ...why your backups were no use. there is absolutely no reason why properly taken backups in MySQL cannot be relied on. But far to many people don;t verify their backups are any good before having to do so in anger. if you do switch DB storage then make certain that the new installation can be reliably backed up AND restored from those backups. if your backup AND restore procedures aren't proven and verified then swtiching to another db storage mechanism is just leaving yourself just as exposed.

    given the volume of writes you are proposing 2000 over 24 hours then I doubt you will have a significant issue with SQLite's db lock on writing. its only going to lock for a few milliseconds or so. good applciation design shoudl be able to handle that in software (ie detect a lock, wait for say a seconds and retry to write).
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2015
    Posts
    3
    ..the original corruption occurred, what caused it
    Spent days on it, couldn't figure it out, even had my VPS provider do a fsck . The same backup worked in my PC however.. so I just don't know..

    make certain that the new installation can be reliably backed up AND restored from those backups
    Yup I learned this lesson the hard way I guess.

    given the volume of writes you are proposing 2000 over 24 hours
    Please also see
    b) A script that runs for 3-4 hours, almost continuously inserting/updating 500 row queries that take about a second
    . When this script is running, then concurrent writes become a problem.

    detect a lock, wait for say a seconds and retry to write
    I don't think sqlite provides any mechanism to detect a lock. There is an intransaction method, but it doesn't detect in multiple connections

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Very quick maths suggests that your big old script is only really throwing in 60K records (250/s, 4 hours).
    Any reason why this takes so long?
    60K is pretty small fry and you should be able to handle that in a single (or couple of) batch(es).


    If the backup works on your PC, then the backup isn't corrupted, surely? If you restore to your PC, then back this up, can the result be restored?
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2015
    Posts
    3
    Sorry I wasn't accurate at all with those stats .
    Actual stats : million products - checked for stock and updated accordingly, prices updated for half a million products.
    Followed by another half a million updates to the table after some analysis. This dataset for the analysis is kind of large for my server, so it has to be broken into segments.


    If the backup works on your PC, then the backup isn't corrupted, surely? If you restore to your PC, then back this up, can the result be restored?
    I know its highly probable that I am missing something, but I had tried so many combinations and permutations of restore / backup etc, I can't anymore. I did check the md5 of the backup file on the server and the desktop though...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •