Mantis Bug Tracker

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0007130opensim[REGION] OpenSim Corepublic2014-04-24 07:282015-01-26 05:23
Reporteraiaustin 
Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
StatusnewResolutionopen 
PlatformPCOSWindowsOS Version8.1
Product Versionmaster (dev code) 
Target VersionFixed in Version 
Summary0007130: Asset database growing with very many instances of terrainImage_uuid
DescriptionI can see the MySQL data base for my grid growing quite rapidly... and it could be that there are very many repeats of assets that are named "terrainImage_uuid" for each region (the description in the asset lists the region name.

SELECT COUNT(*) FROM opensim.assets WHERE name LIKE "terrainImage_%";
shows 4000 or so records for a grid with 16 regions.

[Warning - the query might take a long time if you just use * to SHOW the records rather than COUNT(*) to give the count of those present]

I do have Warp3D rendering on and refresh the maps every 20 hours... in OpenSim.ini I have...

    MaptileRefresh = 72000

I thought the setup now in 0.8.0 dev master deleted earlier versions of such "map" images?
TagsNo tags attached.
Git Revision or version number
Run Mode Grid (Multiple Regions per Sim)
Physics EngineBulletSim
Script Engine
Environment.NET / Windows64
Mono VersionNone
ViewerN/A
Attached Files

- Relationships

-  Notes
(0025883)
aiaustin (developer)
2014-04-24 07:28
edited on: 2014-04-24 07:58

Would it be safe every now and then to use this while the grid is not running..

DELETE FROM opensim.assets WHERE name LIKE "terrainImage_%";

(0025884)
smxy (reporter)
2014-04-24 07:34
edited on: 2014-04-24 07:53

You might want to change that "SELECT * FROM" to "SELECT COUNT(*) FROM" ... before it bites someone else ... :) I'm waiting on 6527 rows of results, each full of a gazillion special characters, to slowly print out on my screen, as there appears to be no breaking out of it ...

(0025885)
aiaustin (developer)
2014-04-24 07:46
edited on: 2014-04-25 14:37

smxy, the COUNT(*) just seems to count the records and just shows the count rather than the records themselves in my MySQL editor when I tried it... but feel free though to change the SQL command in the issue above to be a safer legitimate MySQL command.

(0025886)
aiaustin (developer)
2014-04-24 07:54
edited on: 2014-04-24 07:59

I know its a bit off topic.. but as I look at the "assets" table.. which is the one I assume can grow and is rarely pruned except in specific cases (as I thought it was with a commit JustinCC did on map tiles)... I am seeing CreatorIDs that are odd.

Some (especially apparently from IAR imports) I think are okay and are
00000000-0000-0000-0000-000000000000

But some are completely blank. Is that okay?

I see some HG creatorIDs which might be the remains of spam attacks as there are a LOT of them and all seem not to have proper asset names - just UUID style asset names. These two in particular... anyone recognise them as problematic?

http://craft-world.org:8002;Abel [^] Dagostino
http://clonelife.eu:8002;Builder [^] War

(0025887)
smxy (reporter)
2014-04-24 07:56
edited on: 2014-04-24 08:00

It simply returns the count of the rows selected, rather than printing out the content of all the rows:

mysql> SELECT COUNT(*) FROM opensim.assets where name like "terrainImage_%";
+----------+
| COUNT(*) |
+----------+
| 6527 |
+----------+
1 row in set (11.19 sec)

mysql>

(0025888)
aiaustin (developer)
2014-04-24 08:03

Got it. The suggested query modified accordingly and a warning added. Yes, that could be horrendous on busy larger grids. But it seems this is an asset explosion that is really not necessary.
(0025941)
aiaustin (developer)
2014-05-01 01:14
edited on: 2014-10-29 03:28

After a map tile for a region is regenerated by the refresh mechanism (if it is turn on) or even on a region restart where a map tile is produced, it could save on asset data base table growth if the previous map tile was deleted (the description of the data base record holds the - unique? - region name).

I thought that JustinCC some time ago added this optimisation.. but it does not see to be in operation on latest 0.8.0 GIT Master r/24685 using Warp3D for example.

(0026967)
aiaustin (developer)
2014-10-29 03:30
edited on: 2014-10-29 03:31

Just a reminder that map tiles are filling asset data bases unnecessarily. I now only generate a new map tile for each region on our grids once on restarting the server but even so I have thousands of large image "blobs" all of which definitely are not needed after a new map tile is generated.

A simple optimisation of deleting the previous terrainImage_UUID for each tile after the new one is generated would really save on everyone's asset server DB growing.

(0026969)
melanie (administrator)
2014-10-29 12:40

I believe the code does attempt to delete the previous map tile. However, the asset server needs to support asset deletion, which is not on by default.
(0026970)
aiaustin (developer)
2014-10-29 14:53

Ah, thanks Melanie.. I had not appreciated that. Is it an option that can be toggled in the Robust services .ini files somewhere?

Or did you mean its a facility that still needs to be added.

I am using MySQL.
(0026971)
melanie (administrator)
2014-10-29 16:04

It needs to be turned on in the .ini file. It is turned off by default because an asset server exposed to the internet should not have this option on. Once you have secured your server, you then turn it on as an explicit act, rather then it being on and you not firewalling your server, allowing malicious parties to denude your grid.
(0026973)
smxy (reporter)
2014-10-29 16:14
edited on: 2014-10-29 16:16

It might be helpful if you told us what option, in what section, and in what file. Perhaps I'm just blind, but I don't see such an option, nor do I remember ever seeing such an option in the past.

(0026975)
melanie (administrator)
2014-10-29 16:16

I remember only that there is one because of the discussion surrounding it's introduction. Can't say what it is off the top of my head and I'm not anywhere I could look it up easily.
(0026979)
aiaustin (developer)
2014-10-30 02:11
edited on: 2014-10-30 02:12

Got it in Robust.[HG.].in

thanks Melanie... I was interpreting "remote deletes" as meaning deletes via some sort of remote console. I do control all add on OpenSim.exe instances (and hence regions) that connect to our grids. For firewalls, port 8002 is open and port 8003 is restricted to our subnet.

The main AssetServiceConnector is on the restricted port 8002.. but we do have HGAssetServiceConnector running on the open port 8002 of course to let HG work. So the question, for Diva?, is whether this is safe or if a malicious or modified HGAssetServiceConnector could wipe a grid database? If so, we ought to add a comment to the Robust.HG.ini file to say "do not allow deletes if a HGAssetServiceConnector is running on an open port.

Here are the relevant extracts from a default Robust.HG.ini

[ServiceList]

AssetServiceConnector = "8003/OpenSim.Server.Handlers.dll:AssetServiceConnector"
...
HGAssetServiceConnector = "HGAssetService@8002/OpenSim.Server.Handlers.dll:AssetServiceConnector"
...

[AssetService]
    LocalServiceModule = "OpenSim.Services.AssetService.dll:AssetService"
    DefaultAssetLoader = "OpenSim.Framework.AssetLoader.Filesystem.dll"
    AssetLoaderArgs = "./assets/AssetSets.xml"

    ; Allow maptile assets to be remotely deleted by remote calls to the asset service.
    ; There is no harm in having this as false - it just means that historical maptile assets are not deleted.
    ; This only applies to maptiles served via the version 1 viewer mechanisms
    ; Default is false
    AllowRemoteDelete = false

    ; Allow all assets to be remotely deleted.
    ; Only set this to true if you are operating a grid where you control all calls to the asset service
    ; (where a necessary condition is that you control all simulators) and you need this for admin purposes.
    ; If set to true, AllowRemoteDelete = true is required as well.
    ; Default is false.
    AllowRemoteDeleteAllTypes = false

(0027348)
cloneu (reporter)
2015-01-26 05:23

I guess it is still broken

MariaDB [opensim]> SET SQL_SAFE_UPDATES=0;
Query OK, 0 rows affected (0.00 sec)
MariaDB [opensim]> DELETE FROM opensim.assets WHERE name LIKE "terrainImage_%";
Query OK, 3551217 rows affected (1 hour 5 min 27.38 sec)
MariaDB [opensim]>

After which an Export, delete the database, then Import the data. The results were as follows
full size 277.6 down to 103.8
assets 271.8 down to 97.5

- Issue History
Date Modified Username Field Change
2014-04-24 07:28 aiaustin New Issue
2014-04-24 07:28 aiaustin Note Added: 0025883
2014-04-24 07:32 aiaustin Note Edited: 0025883 View Revisions
2014-04-24 07:34 smxy Note Added: 0025884
2014-04-24 07:46 aiaustin Note Added: 0025885
2014-04-24 07:53 smxy Note Edited: 0025884 View Revisions
2014-04-24 07:54 aiaustin Note Added: 0025886
2014-04-24 07:55 aiaustin Note Edited: 0025885 View Revisions
2014-04-24 07:55 aiaustin Note Edited: 0025885 View Revisions
2014-04-24 07:56 smxy Note Added: 0025887
2014-04-24 07:57 aiaustin Description Updated View Revisions
2014-04-24 07:58 aiaustin Description Updated View Revisions
2014-04-24 07:58 aiaustin Note Edited: 0025883 View Revisions
2014-04-24 07:59 aiaustin Note Edited: 0025886 View Revisions
2014-04-24 07:59 aiaustin Note Edited: 0025886 View Revisions
2014-04-24 08:00 smxy Note Edited: 0025887 View Revisions
2014-04-24 08:00 aiaustin Description Updated View Revisions
2014-04-24 08:03 aiaustin Note Added: 0025888
2014-04-25 14:37 aiaustin Note Edited: 0025885 View Revisions
2014-04-25 14:37 aiaustin Note Edited: 0025885 View Revisions
2014-05-01 01:14 aiaustin Note Added: 0025941
2014-10-29 03:28 aiaustin Note Edited: 0025941 View Revisions
2014-10-29 03:30 aiaustin Note Added: 0026967
2014-10-29 03:31 aiaustin Note Edited: 0026967 View Revisions
2014-10-29 12:40 melanie Note Added: 0026969
2014-10-29 14:53 aiaustin Note Added: 0026970
2014-10-29 16:04 melanie Note Added: 0026971
2014-10-29 16:14 smxy Note Added: 0026973
2014-10-29 16:16 melanie Note Added: 0026975
2014-10-29 16:16 smxy Note Edited: 0026973 View Revisions
2014-10-30 02:11 aiaustin Note Added: 0026979
2014-10-30 02:12 aiaustin Note Edited: 0026979 View Revisions
2015-01-26 05:23 cloneu Note Added: 0027348


Copyright © 2000 - 2012 MantisBT Group
Powered by Mantis Bugtracker