MySQL MySQL

By Mister Acacia

There's a new release of MySQL, the database system that SecondLife most likely uses for its monstrous database system, and one new feature raises the number of of I/O requests that each AIO helper thread could process from 32 to 256 (Windows folks are still stuck with 32). This could significantly improve inventory fetching by a simple factor of 8. But ...

Why is the inventory fetched every single time you log on? Why isn't it just updated? Well, it is. But the only way to do is to do a full query of every object that belongs to you. And your objects are a set of records in a huge database cluster, along with everyone else's. Your viewer does a query, looking for every record that shows you as the owner. And if LL's database is anything like OpenSim's, then the record complexity is another factor:

+------------------------------+------------------+------+-----+--------------------------------------+
| Field                        | Type             | Null | Key | Default                              |
+------------------------------+------------------+------+-----+--------------------------------------+
| assetID                      | varchar(36)      | YES  |     | NULL                                 |
| assetType                    | int(11)          | YES  |     | NULL                                 |
| inventoryName                | varchar(64)      | YES  |     | NULL                                 |
| inventoryDescription         | varchar(128)     | YES  |     | NULL                                 |
| inventoryNextPermissions     | int(10) unsigned | YES  |     | NULL                                 |
| inventoryCurrentPermissions  | int(10) unsigned | YES  |     | NULL                                 |
| invType                      | int(11)          | YES  |     | NULL                                 |
| creatorID                    | varchar(36)      | NO   |     | 00000000-0000-0000-0000-000000000000 |
| inventoryBasePermissions     | int(10) unsigned | NO   |     | 0                                    |
| inventoryEveryOnePermissions | int(10) unsigned | NO   |     | 0                                    |
| salePrice                    | int(11)          | NO   |     | 0                                    |
| saleType                     | tinyint(4)       | NO   |     | 0                                    |
| creationDate                 | int(11)          | NO   |     | 0                                    |
| groupID                      | varchar(36)      | NO   |     | 00000000-0000-0000-0000-000000000000 |
| groupOwned                   | tinyint(4)       | NO   |     | 0                                    |
| flags                        | int(11) unsigned | NO   |     | 0                                    |
| inventoryID                  | char(36)         | NO   | PRI | 00000000-0000-0000-0000-000000000000 |
| avatarID                     | char(36)         | YES  | MUL | NULL                                 |
| parentFolderID               | char(36)         | YES  | MUL | NULL                                 |
| inventoryGroupPermissions    | int(10) unsigned | NO   |     | 0                                    |
+------------------------------+------------------+------+-----+--------------------------------------+

As you can see, just one object has a complex record. That points to the object's location in the asset storage cluster, among other things. As you can see, there's a Group-Owned field, which would only be set if the object were rezzed. So the database interacts with the sim, too. And everyone who sees it interfaces with the object and this record.

And there are trillions of records. Every single object, including those within objects, every texture, every script, every landmark your giver hands out, has a record.

It's a convoluted mess, and in this writer's mind, poorly trained in SQL and database administration, it would be expensive to fix. But perhaps the multiple thread fix can help speed things up. One can only hope.