In the past I experienced those problems when one of the tables (assets)
became corrupted and lost its primary key. I still don't know what
prompted that to happen; it may have been a DB crash followed by a
restore from backup that didn't go well. All I know is that the assets
table suddenly didn't have a primary key. That made the entire thing run
awful.
On 4/6/2012 10:53 AM, Gudule Lapointe wrote:
I experience lot of timeout problems. I checked every side of the
installation, and I suspect the database to be the bottleneck.
The main question is: how can I clean up the database? Detail
description below...
Any advice on any part of the problem is welcome.
Current setup comes from an initial test installation, and changed a
lot before going to prod (versions changes, server changes, oar save
and load, etc).
However it has been working quite fine for more than 3 months, since
latest big change.
- version: 0.7.3-post-fixes
- robust server, with 7 simulators, for a total of 56 regions
- From these region, I would say 15 à 20 are really active, others are
placeholders, without content.
- About 20 registered users. Usually 3 or 4 concurrent users
- Each region has it's own mysql database, and robust uses a single one.
Since around 5 days, I get continuous timeout, access to inventory or
assets errors and sometimes region crashes.
Though they were no recent change on the set up when the problems
began. Hence my suspicions on the database.
(CPU, memory and disk usage don't show any overload)
Regions database are fairly light (~20MB)
Robust database is huge: 2.6 GB
I am not sure such a big database is common for setup like ours.
So it looks obvious that I should clean up the database, which may
contain a lot of outdated items.
Fair enough. How can I do?
I would like to know
- which tables I can empty without losses, at all
- which tables I can empty after having made a successful oar save of
my regions
- how can I clean up the inventory and assets server, without having
my users loosing their inventory?
- and any advise on how to optimize mysql server for this usage is welcome
Another clue is to split robust inventory and/or assets servers, so,
in this case
- which tables do I have to split?
- are there risks of losses if the address/port of inventory and
assets server change?
And, last but not least, monitoring sql show warning of lot of queries
are made on non-indexes fields of mysql tables. Which is not harmful
for marginal queries, but is a big problem for recurring ones. I can't
imagine parsing the whole code to guess where there are such recurring
queries. Any idea on how to track guilty queries? A clue: it seems to
be happening mostly when accessing foreign content (content taken on
HG regions, or content used by HG visitor).
--
http://www.speculoos.net/
secondlife://speculoos.net:8002/
Speculoos, the belgian cookie-flavored metaverse
_______________________________________________
Opensim-users mailing list
[email protected]
https://lists.berlios.de/mailman/listinfo/opensim-users
_______________________________________________
Opensim-users mailing list
[email protected]
https://lists.berlios.de/mailman/listinfo/opensim-users
