mysql maintenance scripts - answered

* Technical questions about the grid, servers and viewers
* Technische Fragen über das Grid, Server und die Viewer

mysql maintenance scripts - answered

Beitragvon Emil_Jannings » So 13. Mär 2016, 03:37

Translate to English translate to German Translate to French Translate to Italian Translate to Spanish Translate to Portuguese Translate to Czech Перевести на русский язык 翻译成中国

I understand that a mysql database can have some fragmentation after a period of use and that there are scripts one can run to detect and clean this up. I've never heard of anyone who uses mysql in an opensim application doing any of this maintenance work. So my question is -- what if anything do you do to keep your mysql database in good shape?

thanks,
Emil
Zuletzt geändert von Emil_Jannings am So 13. Mär 2016, 21:40, insgesamt 1-mal geändert.
Benutzeravatar
Emil_Jannings
 
Beiträge: 92
Registriert: Do 14. Feb 2013, 20:58
Wohnort: Chicago USA

Re: mysql maintainance scripts

Beitragvon Sheera Khan » So 13. Mär 2016, 09:03

Translate to English translate to German Translate to French Translate to Italian Translate to Spanish Translate to Portuguese Translate to Czech Перевести на русский язык 翻译成中国

Hi Emil,

I'm no real expert in DB-design and operation but as far as I know the fragmentation in your database tables will affect the space consumed by your database but not the performance. Under normal circumstances the optimization run will take quite some time during which the tables have to be locked and can't be used. So if disk space is is of concern for you then you might get some space back by optimizing your DB. I found an article about that in http://serverfault.com/questions/202000/how-find-and-fix-fragmented-mysql-tables. Maybe that can help you more

Ciaoo

Sheera
Benutzeravatar
Sheera Khan
 
Beiträge: 969
Registriert: Fr 22. Mär 2013, 14:18

Re: mysql maintainance scripts

Beitragvon Mareta Dagostino » So 13. Mär 2016, 11:07

Translate to English translate to German Translate to French Translate to Italian Translate to Spanish Translate to Portuguese Translate to Czech Перевести на русский язык 翻译成中国

Hi Emil,

the sql databases of region servers do not contain much data, so normally there is no need to think much about what they contain. If I have any consistency doubts, instead of administering the sql stuff I prefer: OAR backup, delete the database, create a new one, OAR restore.

Ciao,
Mareta
https://hyperweb.eu => Server-Tutorial für Linux mit OpenSim, konfigurierter OpenSim Arriba Binärdownload!
Benutzeravatar
Mareta Dagostino
 
Beiträge: 757
Registriert: Sa 29. Jan 2011, 20:26

Re: mysql maintainance scripts

Beitragvon Eryn Galen » So 13. Mär 2016, 12:40

Translate to English translate to German Translate to French Translate to Italian Translate to Spanish Translate to Portuguese Translate to Czech Перевести на русский язык 翻译成中国

Agrees with Mareta
Benutzeravatar
Eryn Galen
 
Beiträge: 972
Registriert: So 13. Jan 2013, 22:48

Re: mysql maintainance scripts

Beitragvon Shy Robbiani » So 13. Mär 2016, 18:00

Translate to English translate to German Translate to French Translate to Italian Translate to Spanish Translate to Portuguese Translate to Czech Перевести на русский язык 翻译成中国

I never thought about that nor did I experience any problems. On the other hand I recreate the databases from MySQL backups with each update of the Metropolis simulator software.
Am Ende wird alles gut. Wenn es nicht gut ist, ist es nicht das Ende.
Benutzeravatar
Shy Robbiani
 
Beiträge: 276
Registriert: Di 17. Jul 2012, 19:23

Re: mysql maintainance scripts

Beitragvon Emil_Jannings » So 13. Mär 2016, 21:39

Translate to English translate to German Translate to French Translate to Italian Translate to Spanish Translate to Portuguese Translate to Czech Перевести на русский язык 翻译成中国

I like the idea of starting with a new DB any time an OAR file has to be used to restore a region. Thanks for that and the other comments too. I didn't think it was a too big of a problem. :D

I recently moved some regions from one server to another and wanted to keep the owner/creator information intact so I moved the DB entirely and it seemed to work out. I'll be glad to post details if anyone else is facing a similar move.

Emil
Benutzeravatar
Emil_Jannings
 
Beiträge: 92
Registriert: Do 14. Feb 2013, 20:58
Wohnort: Chicago USA

Re: mysql maintenance scripts - answered

Beitragvon droid crazyboy » So 13. Mär 2016, 22:27

Translate to English translate to German Translate to French Translate to Italian Translate to Spanish Translate to Portuguese Translate to Czech Перевести на русский язык 翻译成中国

About table /indes reorg
it happen in every BD even Oracle I use dayly

if table or index is fragmented, the table will take more and more place in the DD for nothing. here is an impact on perfe, because each read load more and more unsued space in memory and so there more read on DD to get data to use. With a good system with lot of memory and good ssd disk, the lsot of perf is not immediatly visible or only when you use come of the index
there is THREE solution
1) pass a script that try to get back the unsued palce and shrink the files in wich the data tables and index are stored
2) move the table (depending of DB but generaly moving a table assign a new disk space and everything is reorganized when write rhe new table...
3) restore the database, as restoring automatickly reorganized the table and index

anyway...during the 1 solution it is better to lock acces to the table and in solution 2 and 3 tables are not accessible...or to be more clear data not comitted are not accessible

droid
Benutzeravatar
droid crazyboy
 
Beiträge: 24
Registriert: Sa 29. Mär 2014, 18:30

Re: mysql maintainance scripts

Beitragvon Emil_Jannings » Do 17. Mär 2016, 19:43

Translate to English translate to German Translate to French Translate to Italian Translate to Spanish Translate to Portuguese Translate to Czech Перевести на русский язык 翻译成中国

Sheera Khan hat geschrieben:Hi Emil,
I'm no real expert in DB-design and operation but as far as I know the fragmentation in your database tables will affect the space consumed by your database but not the performance. Under normal circumstances the optimization run will take quite some time during which the tables have to be locked and can't be used. So if disk space is is of concern for you then you might get some space back by optimizing your DB. I found an article about that in http://serverfault.com/questions/202000/how-find-and-fix-fragmented-mysql-tables. Maybe that can help you more


The posts in that forum support what you and others have been saying -- it's not performance that is the immediate problem but disk space. With the small db used to run a region I don't see this as a problem for me so I'm going to leave it alone -- for now at least.

Thanks for the information.
Benutzeravatar
Emil_Jannings
 
Beiträge: 92
Registriert: Do 14. Feb 2013, 20:58
Wohnort: Chicago USA

Re: mysql maintenance scripts - answered

Beitragvon JoshuaDepuy » Fr 13. Nov 2020, 11:30

Translate to English translate to German Translate to French Translate to Italian Translate to Spanish Translate to Portuguese Translate to Czech Перевести на русский язык 翻译成中国

The projection of light and all dynamics re reform for the estimated show for the citizens. the skills are marginal for the movement of the essay have review for all changes. The skills are implied for the way out for the turns and turns for humans.
Benutzeravatar
JoshuaDepuy
 
Beiträge: 1
Registriert: Di 3. Nov 2020, 07:45

Re: mysql maintenance scripts - answered

Beitragvon ashigarugabe » Do 21. Jan 2021, 08:38

Translate to English translate to German Translate to French Translate to Italian Translate to Spanish Translate to Portuguese Translate to Czech Перевести на русский язык 翻译成中国

With a good system with lot of memory and good ssd disk, the lsot of perf is not immediatly visible or only when you use come of the index
there is THREE solution
Benutzeravatar
ashigarugabe
 
Beiträge: 1
Registriert: Mi 20. Jan 2021, 13:08


Zurück zu Technical Support / Technische Unterstützung

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 1 Gast



Powered by phpBB

Deutsche Übersetzung durch phpBB.de
.

Style designed by Artodia.