Could it be an issue with the in-memory database when there is a lot of data?

[hieu-n]

Hi, I'm looking for a static site generator (SSG) for my project in which I need to extract a large amount of data from my database and provide it to the SSG to generate the static site. Statik seems to be exactly what I want. However, there is one thing concerns me:

From the wiki:

Statik allows you to define your own data models in YAML format, and instances of those data models either in YAML or Markdown. This is all loaded into an in-memory SQLAlchemy SQLite database when rendering your views.

I wonder if there is a lot of data (e.g. >20G), would the in-memory database consume all my ram and freeze my computer?

[thanethomson]

Interesting question! See http://stackoverflow.com/questions/784173/what-are-the-performance-characteristics-of-sqlite-with-very-large-database-file

It could possibly work, depending on how you define your models. Otherwise this would be a good case to integrate with a larger relational database (e.g. PostgreSQL or MySQL).

Unfortunately Statik doesn't support those databases just yet. But it'd definitely be possible (Statik uses SQLAlchemy under the hood to connect to th database, and works with most relational databases).

The trouble is, Statik uses an ephemeral database for a specific reason: I wanted extreme flexibility in the database structure. Every time you run Statik, it creates a database structure, imports all of your data, builds your site, and then drops the database.

I'd have to give it some thought as to how best to integrate with other relational databases for large datasets. Could be tricky.

Is it possible to describe your use case in a bit more detail?

[hieu-n]

Interesting question! See http://stackoverflow.com/questions/784173/what-are-the-performance-characteristics-of-sqlite-with-very-large-database-file

Not sure if I missed something, but that mostly talks about sqlite as a file based database. Statik use sqlite as an in-memory database. The situation is worse than using writing to file, because ram size is often more limited.

Is it possible to describe your use case in a bit more detail?

I maintain an ecommerce website for my friend's business. The website is heavy and slow with a lot of products (> 5000 products) and thousand documents/articles. Most customers don't use the buy/checkout function. They go to our website to see the product pictures and information, then contact our sale via online chat or phone. So I'm planning to convert our website to a static site with 3rd party services for online chat/discussion and even for ecommerce functions (with services like https://www.marketcloud.it or https://www.moltin.com).

[thanethomson]

Your use case sounds, to me at least, like more of a case to use caching servers like Varnish, to be honest. Doesn't sound like the sort of thing where a static site generator would be useful (in fact, it'll just complicate things significantly).

[eitch]

If i may, how many customers do you have? If it isn't in the thousands concurrently, then perhaps the following can be of help. You say you have around 5000 products. In my experience when there is a performance problem with such "few" records, then there might be other forms of optimization:

• check your SQL queries, are there many joins? If yes, are they cartesian products, i.e. multiplying entire tables with each other? Can you simplify your joins? Perhaps less normalization, thus less joins?
• do you have all the important indices set? This is mostly the first thing people do, but perhaps this is a case for you?
• What DB are you using? Depending on how you use your schema, some DBs are better than others. I've seen cases where using PosgreSQL lead to problems (using Java and Hibernate), and just switching to MariaDB solved everything in one go. This might not be possible for you, but is certainly worth looking into.
• Use your DBs query optimizer and find queries which are performing poorly.
• And then a layer higher up, perhaps your code can be optimized? This is of course the most difficult of all optimizations.

Varnish is a really good thing, but personally i think one should first check these things before looking into caching systems, which can bring with them their own issues.

[hieu-n]

Thank you @thanethomson and @eitch