Limitations to # entries/database size?

[Don Chambers]

I'm wondering what limitations exist with s9y/mysql. I am contemplating the use of s9y for a site that would easily see 100-150 entries/week, each of which could max out the 65k field size limit on entry body - possibly also maxing out extended body as well. Obviously, this is NOT a typical blog. So, how long can s9y & mysql handle this amount of data, or is it just a function of RAM/disc space, and if so, any way to compute it?

[garvinhicking]

Hi!

As for the entry/extended body, you can change the SQL field type to "LONGTEXT" to accomodate this.

As for total DB size: Actually only the autoincrement key wrap around would matter to you. S9y can store INT(11) entries. That is 2.147.483.647.

Of course to manage those entries, your database server needs both CPU and RAM to perform tasks on this. This is hard to say globally, it is very dependant on your server setup. So, I'd simply give it a try.

Regards,
Garvin

[kleinerChemiker]

but he could also increase the autoincrement key to bigint so he can have 18.446.744.073.709.551.615 entries

[Don Chambers]

Am I understanding this correctly? Using default values, s9y can accommodate 2 billion entries?

What exactly should be changed to LONGTEXT,what are the benefits of doing so, how/where do I make this change, and will this change be overridden by upgrading s9y in the future?

How/where do I change the autoincrement key if/when I felt it necessary to increase the # of entries?

[garvinhicking]

Hi!

Am I understanding this correctly? Using default values, s9y can accommodate 2 billion entries?

Yep. It's all a matter of the database. Of course, you wouldn't want to set your frontpage to display 100.000 articles at once.

What exactly should be changed to LONGTEXT,what are the benefits of doing so, how/where do I make this change, and will this change be overridden by upgrading s9y in the future?

The columns serendipity_entries.body and serendipity_entries.extended can be changed to "LONGTEXT". This will inrease the 65k limit. It will not cause you any update trouble, s9y will not change this column really. It's even more probable that s9y will change this column to LONGTEXT in the future.

One of the reasons why longtext is not used by default is table size. When you raise the limit, mysql reserves more space for each row, and thus increases database size. Also by raising the limit, the fulltext indexing will work slightly slower. But this might not even be noticeable. The biggest reason is compatibility, because for SQLite and PostgreSQL the LONGTEXT type was not commonly known before. I'm not even sure how LONGTEXT is available in those two systems nowadays.

How/where do I change the autoincrement key if/when I felt it necessary to increase the # of entries?

That's the serendipity_entries.id column.

You can alter those things easily with phpMyAdmin.

Regards,
Garvin

[Don Chambers]

Thanks SuperGarv!!! If the default is 65k, what does longtext increase it to?

[garvinhicking]

Hi!

Longtext should be 2GB.

(So you'll rather likely hit the HTTP POST limit instead )

Regards,
Garvin

[Don Chambers]

What is the HTTP post limit, and can it be increased?

[kleinerChemiker]

the limit depends on your php-config and can there be altered

http://at2.php.net/manual/en/ini.core.p ... t-max-size

[judebert]

Actually, there's another HTML POST limit: the server limit. Some servers don't allow more than 2MB, some less, some more.

Browsers also limit the size of TEXTAREA entries, so unless you're planning on posting entries some other way, you'll have to respect that limit.