#5546 - cms_stats table takes too much space
-
By Chris Graham
- Added
- 4 views
| Identifier | #5546 |
|---|---|
| Issue type | Feature request or suggestion |
| Title | cms_stats table takes too much space |
| Status | Open |
| Tags |
Risk: Database change (custom) |
| Handling member | Deleted |
| Addon | stats |
| Description | When I designed this table, I didn't realize varchar's reserve space for maximum char lengths, in order for record sizes to be regular.
This query reduces it by about 25%: ALTER TABLE cms_stats MODIFY COLUMN the_page VARCHAR(100), MODIFY COLUMN session_id VARCHAR(15), MODIFY COLUMN operating_system VARCHAR(190); (Composr's database schema check tool will complain about this, but I verified it is okay by checking field lengths in the existing data set) For us to do this properly we'd need to align to Composr's meta-DB field lengths, possibly adding new ones. Or, gasp, allowing custom length values (lots of work). Also, other DB systems store IPs as numeric values as it saves a lot of space. We could consider that. All this said, I actually reduced table usage down to 1/25th the original size because I think the table needed to be re-optimised (changing field types did that as a side effect). So another consideration is to somehow run optimisations automatically. I'm not sure if there's a good way to do that on MySQL without downtime, but a few minutes of downtime every month or so may be okay to stop the site going down like 2 hours due to lack of disk space like it did for me this morning. |
| Steps to reproduce | |
| Funded? | No |
The system will post a comment when this issue is modified (e.g., status changes). To be notified of this, click "Enable comment notifications".
Comments
I think we could use a new custom Composr type for SESSION (VARCHAR(15)) or even just call it TOKEN to also include things like CSRF. It might come in handy especially for the privacy system to auto-anonymise those in the serialise method.
Anywhere else where we could, we could perhaps add a custom DB-meta type as well... perhaps ID_CODE for VARCHAR(100) (if there would never be a case a code name would be > 100 characters... ideally this should never happen as code names are often used in URLs).
I think we should generally scrub DB-Meta and adjust current types to save on space, just making sure for instance we're not using longtext on something that should be short_text. I noticed for example in v11 there were several username fields that were short_text when they should have been ID_TEXT. So v11 could get a meta look-over at least, and v12 implementation of new types.
SHORT_TEXT is varchar(255)
ID_TEXT is varchar(80)
The maximum_username_length option is configurable. It should be capped at 255, and is not, so I'll fix that. Regardless, ID_TEXT is not long enough.
Using LONG_TEXT when we could get away with SHORT_TEXT is not necessarily good. LONG_TEXT uses fewer bytes due to variable-length encoding. But there's a performance penalty, and indexing penalty, to it.
I don't think in general we're in a bad spot. You can buy a 1TB SSD for under $70 now. The stats table is a particular concern because it just grows and grows, stores a lot, and it needs to be very indexable.
I'm untagging as v11 though because it's too late now, and it's not an order-of-magnitude kind of fix.
A SESSION field type does make sense. Reducing length of pages and operating_system in there would require a bit more thought.
Good point about ipv6.