How reliable is SQLite in production?

[Panupat Chongstitwattana]

Hi everyone.

I've been thinking about switching database of my pipeline tools from PostgreSQL to SQLite but still uncertain how reliable it is?

The reason is that we're having a co-production going on between a few studio and to get PostgreSQL working on their end has not been easy... Most studio in my country do not hire IT so there's no one to set up the database nor install the necessary modules on client machines.

I expect the maximum database users to be 60-70 at most.

Thanks.

~Panupat.

[Justin Israel]

Is your application write heavy? If so, sqlite may be a significant performance decrease over postgres, because of the database locking during concurrent write access or a write happening in combination with readers.

--
You received this message because you are subscribed to the Google Groups "Python Programming for Autodesk Maya" group.
To unsubscribe from this group and stop receiving emails from it, send an email to python_inside_m...@googlegroups.com.
To post to this group, send email to python_in...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Panupat Chongstitwattana]

For this specific task (storing ID and material relations) I think it will be very read-heavy. But from what you said it probably will not scale well if I expand it to cover other area... hmm

Thanks again Justin :)

[Justin Israel]

In terms of reliable and production quality, you can find many people saying it works just fine for small to medium web services where the clients are mainly reading down data.
It would just be something that might topple over if you use it on a render farm where all the nodes are pounding it for data on fast connections while other clients are trying to write to it.

[Jay Goodman]

Are you planning to connect to the DB from within maya? There are (or were) issues with maya and sqlite: from the dll not being included with the maya install to memory access issues. I thought I read this was fixed as of 2012, but I'm stuck on 2009, and haven't verified.

If anyone has resolved the memory access issues (which are very rare and random)--I would be interested in that solution.
thanks

[Joe Weidenbach]

I've not seen any issues from Maya. I've been using sqlite in a limited
fashion for my user-tools on a project I'm working on for about two
months and no problems. If the memory access issues are rare though,
that won't rule them out though--the tool in question is just a
character loader.

[Joe Weidenbach]

That's for 2012+ by the way.

On 9/15/2013 8:49 PM, Jay Goodman wrote:

[John Patrick]

I'd advise strongly against this.  There is a worse problem than performance that you'll encounter eventually - concurrent writes can corrupt the database.

Like Justin mentioned, sqlite uses a file-locking mechanism to control concurrent writes, which seems to work 99.9% of the time.  The time it doesn't will corrupt your database.  I've seen it happen. 

IMO, sqlite is great for storing single-user application data and as a test database, but if you're intending to store critical data that will be written to by multiple machines over a network, I would bite the bullet with PostgreSQL, MySQL, etc.

--
You received this message because you are subscribed to the Google Groups "Python Programming for Autodesk Maya" group.

To unsubscribe from this group and stop receiving emails from it, send an email to python_inside_maya+unsub...@googlegroups.com.
To post to this group, send email to python_inside_maya@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--
John Patrick
404-242-2675
jspa...@gmail.com
http://www.canyourigit.com

[Joe Weidenbach]

John,

Are there any other decent cross-platform embedded solutions?  In my case, this is a distributed project, so it's all single user, and they're only reading the database--the TD's (me mostly) load it up with rig data for loading the characters on the animation end, and the animators just install the tool, load the project, and use the tool to load the characters.  In a production situation, I'd go MySQL in a heartbeat, but I don't know of any other reliable options for this scenario.

What other options would you suggest?

Thanks,

Joe

On 9/16/2013 9:24 AM, John Patrick wrote:

I'd advise strongly against this.  There is a worse problem than performance that you'll encounter eventually - concurrent writes can corrupt the database.

Like Justin mentioned, sqlite uses a file-locking mechanism to control concurrent writes, which seems to work 99.9% of the time.  The time it doesn't will corrupt your database.  I've seen it happen. 

IMO, sqlite is great for storing single-user application data and as a test database, but if you're intending to store critical data that will be written to by multiple machines over a network, I would bite the bullet with PostgreSQL, MySQL, etc.

On Sun, Sep 15, 2013 at 8:55 PM, Joe Weidenbach <scd...@gmail.com> wrote:

That's for 2012+ by the way.

On 9/15/2013 8:49 PM, Jay Goodman wrote:

Are you planning to connect to the DB from within maya?  There are (or were) issues with maya and sqlite: from the dll not being included with the maya install to memory access issues.  I thought I read this was fixed as of 2012, but I'm stuck on 2009, and haven't verified.

If anyone has resolved the memory access issues (which are very rare and random)--I would be interested in that solution.
thanks

--
You received this message because you are subscribed to the Google Groups "Python Programming for Autodesk Maya" group.

To unsubscribe from this group and stop receiving emails from it, send an email to python_inside_m...@googlegroups.com.
To post to this group, send email to python_in...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--
John Patrick
404-242-2675
jspa...@gmail.com
http://www.canyourigit.com

--
You received this message because you are subscribed to the Google Groups "Python Programming for Autodesk Maya" group.

To unsubscribe from this group and stop receiving emails from it, send an email to python_inside_m...@googlegroups.com.
To post to this group, send email to python_in...@googlegroups.com.

[Justin Israel]

Is your application running queries against the data, or simply retrieving a payload of rig data for a given key?

[Joe Weidenbach]

As they say, when you have a hammer, all you see is nails.  In my previous life I was a database programmer, hence my proclivity towards using them.  Mostly, I use this tool for finding rig paths in an easy fashion.  The tool loads a list of characters that it knows about (one query), and then loads the known control and envelope rigs for those characters (another query), finally using that info for another query to actually feed the file paths into a referencer to make the appropriate constraints.  It would be simple enough to write a text parser for a flat file, but I like SQL a lot better than string parsing.

That's most of it :)  However, on a much larger tool I'm working on, it's going to be managing studio-wide settings and preferences.  I was planning on using sqlite for the single-user version, and MySQL for the enterprise.  Is that the wrong approach?

[Justin Israel]

For the purely single-user type tool, I was wondering about the data requirement to see if suggesting the python Shelve module would be an appropriate replacement for Sqlite. That would be a suitable replacement if you access your data in a key-value approach, where the value can be any serialized data structure. 

But if you need the query support, then I suppose sqlite would need to be the solution. And for studio-wide settings, you could either use MySQL, with your client API hitting it directly (2 tier solution), or an sqlite solution, made available over a REST api that serializes all access to the database (3 tier solution). That is, clients would hit the REST api, and a single worker is performing all writes (and I assume its fine that multiple workers could serve read requests). 

The same thing could be done with MySQL, in a 3 tier fashion, if you want more speed and reliability over the sqlite backend. 

[Joe Weidenbach]

Hmm.  I actually like the shelve option, now that I've seen it.  Like PyQt, the Python library is large enough that I definitely haven't scratched the surface on all that it has available, so I certainly appreciate the tip.  That actually solves some of my other non-interface issues too.  Thanks!

[John Patrick]

Sorry Joe, my reply was intended for the OP. I think the embedded single-user or read-only case is where SQLite makes sense.  

To clarify, I would not recommend sqlite in a situation requiring multiple users writing to a single db over a network.  

Sent from Gmail Mobile

[uri.a...@gmail.com]

We're trying using SQLite in a production environment, but our use-case is a little different - the assumption is that we are in a totally _read only_ environment. see my blog post: http://uri.agassi.co.il/2014/10/using-sqlite-for-production.html

[Justin Israel]

I thought the intro was a bit misleading. It wasn't clear to me why it seemed like such a joke to consider Sqlite for a production application. I was always under the impression that sqlite was production grade, but that it is meant for small to medium traffic. I am pretty sure sqlite has been used in production for quite a long time, right? The way it was phrased in the blog post kind of sounded like you were out to prove that it isn't just a toy.

--
You received this message because you are subscribed to the Google Groups "Python Programming for Autodesk Maya" group.
To unsubscribe from this group and stop receiving emails from it, send an email to python_inside_m...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/python_inside_maya/900f345b-1819-4206-afbd-b3e09bfad9e4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Uri Agassi]

Hi Justin,

Thanks for reading my post!

The company I work in creates large-scale solutions (rather than small-to-medium). Furthermore, the ad-server use case is a high-throughput use case (should scale to north of 100Ms of requests a day), so SQLite initially did not seem like a viable solution.

I guess I should mend the intro to make this point more clear.

Thanks again,

Uri.

--
You received this message because you are subscribed to a topic in the Google Groups "Python Programming for Autodesk Maya" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/python_inside_maya/ADU9TCDs-LM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to python_inside_m...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/python_inside_maya/CAPGFgA18Dxk%3DpRuDu-B3vNF1zzGniWYWCC6XP1iByedVEpt%2BiQ%40mail.gmail.com.

[Justin Israel]

Ya I think that might have been the issue. It wasn't really clear upfront about your company and the profile of the application. So it seemed like in general you were proving sqlite is viable for any production application.

Thanks for sharing!

To view this discussion on the web visit https://groups.google.com/d/msgid/python_inside_maya/CAGzLmH1Y73QDidiAtKhhKoT%2B6sbqTLB4FMcY7V9c3UB3pXwDEQ%40mail.gmail.com.

[Uri Agassi]

I have mended the intro.

I will keep posting a drill down on our design and difficulties, and I would love your input on them :)

Uri

To view this discussion on the web visit https://groups.google.com/d/msgid/python_inside_maya/CAPGFgA2xX80hxKvN970zMpi74WJwD8VytDKu6xkV3hKrd4mjQQ%40mail.gmail.com.