As of Browscap data version 5308, the browscap table consumes about 170MB for MySQL InnoDB and 80MB for MySQL MyISAM. However, the source Browscap INI file is only about 12MB, so the tables are not as small as they could be. It is believed the increase in size is due to the inheritance of properties per user agent prior to inserting them into the database.

Would like to explore whether it is possible to reduce the size of these tables without hurting performance. The large table sizes can be problematic in some installations.

Comments

greatmatter’s picture

greatmatter CreditAttribution: greatmatter commented

I'd like to second this--our browscap table is sucking up 204MB.

DrCord’s picture

DrCord CreditAttribution: DrCord commented

Our table is 161 MB, 5x larger than any other table in our install, this is a PITA. It has over 50,000 records, this seems excessive.

peterx’s picture

peterx CreditAttribution: peterx commented

The inheritance of data during table creation is probably to cut down the number of rows you have to read when looking up data.

AohRveTPV’s picture

AohRveTPV CreditAttribution: AohRveTPV commented

The inheritance of data during table creation is probably to cut down the number of rows you have to read when looking up data.

I think you are right--it is a time-memory tradeoff.

One possible solution to reducing the table size without hurting performance:
- Do not inherit properties at import.
- Inherit properties upon user agent look-up.
- Cache inherited properties for the user agent.

So the most user agent look-ups would hit the cache and only require one query. Only look-ups for less commonly encountered user agents would miss the cache and require multiple queries.

Göran’s picture

Göran CreditAttribution: Göran as a volunteer commented

The DB table size don't make sense in a market view.
The significance is that small companies can not choose Drupal as a solution because the cost of the database does not contain within normal accounts with web hotels!

It is urgent to find a better solution, or at least an alternative that reduces database, even if it on cost of some speed.

Otherwise, Browscap will be, a small tuft that overturning the heavy load, for many
And it is no consolation that we know what is best for the market, when it is the market that decides

Göran’s picture

Göran CreditAttribution: Göran as a volunteer commented

This issue is still important.
My question is if anyone is good enought to come up with a better sulution than having all data in a DB table who is so hughe, fare away bigger than all other database tables together, in Drupal. It seams to me that the content in the table is quite static. If so, why not see another solution? (I am not able to decide if it is possible - but I think so)

I.E. - Could it be possible to let browscap write a file with all this info instead? And set ongoing transactions in Browscap cache table?

And put the file in "Public default" - in my case it is /sites/default/files/pub/ + the new browscap - map

I even believe it will speed up a bit, because normally a web server read files faster than it able to read same amount of data from a database table.

peterx’s picture

peterx CreditAttribution: peterx commented

The table is often read but rarely updated. An option is to switch the table from InnoDB to MyISAM to reduce overheads. In one application, I reduced database overheads 80% by making the same switch.

rivimey’s picture

rivimey
she/her
CreditAttribution: rivimey commented

I'm not up for writing a patch, but it seems to me that the table structure and usage is ripe for use of compression on the 'data' field.

that is, when writing to the DB, json-encode and then compress 'data'; on read, uncompress and json_decode it.

A simple example showed the following:

798 orig_entry
326 orig_entry.gz

so >50% less space used. In my DB the longest 'data' is 811 chars, so 798 is one of the longer ones. Shorter ones will probably be less compressible, so I'd hope for around 50% compression on average.

If the API is sane, this should be a trivial change; if you make a patch, it may be best to include a setting whether to do this or not, should there be issues.

HTH, Ruth

http://php.net/manual/en/function.gzuncompress.php [php version >4]

peterx’s picture

peterx CreditAttribution: peterx as a volunteer commented

Another savings for data. replace(`data`, 's:7:"comment"', 's:1:"c"'). Reduced 62.9 MB to 61.9 MB. Repeat for other common fields.