Wiki source code of Database Administration

Version 31.2 by Vincent Massol on 2013/12/09

Hide last authors
Manuel Smeria 27.2 1 {{box cssClass="floatinginfobox" title="**Contents**"}}
2 {{toc/}}
3 {{/box}}
Ludovic Dubost 1.1 4
Manuel Smeria 27.2 5 XWiki uses by default a database to store its data. You will find on this page tips and tricks to administer and tune your database setup. For Installation please check the [[Installation guide>>InstallationWAR#HInstallandconfigureaRelationalDatabase]] which contains information on how to setup XWiki for multiple databases.
Ludovic Dubost 1.1 6
Silvia Macovei 19.2 7 = Indexes =
Jean-Vincent Drean 7.1 8
Manuel Smeria 27.2 9 To improve the performance of your XWiki instance when running with many documents you should run the following script to create indexes for your database (this has been tested for MySQL 5.0):
Ludovic Dubost 1.1 10
Vincent Massol 29.1 11 == Indexes for XWiki Enterprise versions after 4.3 ==
12
13 {{code}}
14 create index xwl_value on xwikilargestrings (xwl_value(50));
15 create index xwd_parent on xwikidoc (xwd_parent(50));
16 create index xwd_class_xml on xwikidoc (xwd_class_xml(20));
17 create index xws_number on xwikistatsdoc (XWS_NUMBER);
18 create index xws_classname on xwikistatsdoc (XWS_CLASSNAME);
19 create index xwr_number on xwikistatsreferer (XWR_NUMBER);
20 create index xwr_classname on xwikistatsreferer (XWR_CLASSNAME);
21 create index xwr_referer on xwikistatsreferer (XWR_REFERER(50));
22 create index xwv_user_agent on xwikistatsvisit (XWV_USER_AGENT(255));
23 create index xwv_cookie on xwikistatsvisit (XWV_COOKIE(255));
24 create index xwv_classname on xwikistatsvisit (XWV_CLASSNAME);
25 create index xwv_number on xwikistatsvisit (XWV_NUMBER);
26 create index ase_page_date on activitystream_events (ase_page, ase_date);
27 create index xda_docid1 on xwikiattrecyclebin (xda_docid);
28 create index ase_param1 on activitystream_events (ase_param1(200));
29 create index ase_param2 on activitystream_events (ase_param2(200));
30 create index ase_param3 on activitystream_events (ase_param3(200));
31 create index ase_param4 on activitystream_events (ase_param4(200));
32 create index ase_param5 on activitystream_events (ase_param5(200));
33 {{/code}}
34
Manuel Smeria 27.2 35 == Indexes for XWiki Enterprise versions after 3.2 ==
Sergiu Dumitriu 23.2 36
Manuel Smeria 27.2 37 {{code}}
Sergiu Dumitriu 23.2 38 create index xwl_value on xwikilargestrings (xwl_value(50));
39 create index xwd_parent on xwikidoc (xwd_parent(50));
40 create index xwd_class_xml on xwikidoc (xwd_class_xml(20));
41 create index xws_number on xwikistatsdoc (XWS_NUMBER);
42 create index xws_classname on xwikistatsdoc (XWS_CLASSNAME);
43 create index xwr_number on xwikistatsreferer (XWR_NUMBER);
44 create index xwr_classname on xwikistatsreferer (XWR_CLASSNAME);
45 create index xwr_referer on xwikistatsreferer (XWR_REFERER(50));
46 create index xwv_user_agent on xwikistatsvisit (XWV_USER_AGENT(255));
47 create index xwv_cookie on xwikistatsvisit (XWV_COOKIE(255));
48 create index xwv_classname on xwikistatsvisit (XWV_CLASSNAME);
49 create index xwv_number on xwikistatsvisit (XWV_NUMBER);
50 create index ase_requestid on activitystream_events (ase_requestid(200));
51 create index ase_page_date on activitystream_events (ase_page, ase_date);
52 create index xda_docid1 on xwikiattrecyclebin (xda_docid);
Vincent Massol 29.1 53 create index ase_param1 on activitystream_events (ase_param1(200));
54 create index ase_param2 on activitystream_events (ase_param2(200));
55 create index ase_param3 on activitystream_events (ase_param3(200));
56 create index ase_param4 on activitystream_events (ase_param4(200));
57 create index ase_param5 on activitystream_events (ase_param5(200));
Manuel Smeria 27.2 58 {{/code}}
Sergiu Dumitriu 23.2 59
Silvia Macovei 19.1 60 {{info}}
Manuel Smeria 27.2 61 * Most indexes are created automatically starting with XWiki Enterprise 3.2 and only the indexes listed above need to be created.
62 * For older versions every index must be created manually. You can see the full list of indexes that should be created for older versions below. There is now [[an application>>extensions:Extension.Check Config And Indexes Application]] that runs this script automatically on XE and XEM directly from your wiki.
Silvia Macovei 19.1 63 {{/info}}
Vincent Massol 17.2 64
Vincent Massol 24.1 65 == Indexes for XWiki Enterprise versions before 3.2 ==
Sergiu Dumitriu 23.3 66
Manuel Smeria 27.2 67 {{code}}
Sergiu Dumitriu 23.3 68 create index DOC_SPACE on xwikidoc (XWD_WEB);
69 create index DOC_NAME on xwikidoc (XWD_NAME);
70 create index DOC_FULLNAME on xwikidoc (XWD_FULLNAME);
71 create index DOC_TITLE on xwikidoc (XWD_TITLE);
72 create index DOC_PARENT on xwikidoc (XWD_PARENT(50));
73 create index DOC_CREATION_DATE on xwikidoc (XWD_CREATION_DATE);
74 create index DOC_DATE on xwikidoc (XWD_DATE);
75 create index DOC_CONTENT_UPDATE_DATE on xwikidoc (XWD_CONTENT_UPDATE_DATE);
76 create index DOC_CREATOR on xwikidoc (XWD_CREATOR);
77 create index DOC_AUTHOR on xwikidoc (XWD_AUTHOR);
78 create index DOC_CONTENT_AUTHOR on xwikidoc (XWD_CONTENT_AUTHOR);
79 create index DOC_LANGUAGE on xwikidoc (XWD_LANGUAGE);
80 create index DOC_DEFAULT_LANGUAGE on xwikidoc (XWD_DEFAULT_LANGUAGE);
81 create index DOC_CLASS_XML on xwikidoc (XWD_CLASS_XML(20));
82 create index DOC_MINOREDIT on xwikidoc (XWD_MINOREDIT);
83 create index DOC_HIDDEN on xwikidoc (XWD_HIDDEN);
84 create index OBJ_NAME on xwikiobjects (XWO_NAME);
85 create index OBJ_CLASSNAME on xwikiobjects (XWO_CLASSNAME);
86 create index OBJ_NUMBER on xwikiobjects (XWO_NUMBER);
87
88 create index XWINT_NAME on xwikiintegers (XWI_NAME);
89 create index XWINT_VALUE on xwikiintegers (XWI_VALUE);
90 create index XWLONG_NAME on xwikilongs (XWL_NAME);
91 create index XWLONG_VALUE on xwikilongs (XWL_VALUE);
92 create index XWFLOAT_NAME on xwikifloats (XWF_NAME);
93 create index XWFLOAT_VALUE on xwikifloats (XWF_VALUE);
94 create index XWDOUBLE_NAME on xwikidoubles (XWD_NAME);
95 create index XWDOUBLE_VALUE on xwikidoubles (XWD_VALUE);
96 create index XWDATE_NAME on xwikidates (XWS_NAME);
97 create index XWDATE_VALUE on xwikidates (XWS_VALUE);
98 create index XWSTR_NAME on xwikistrings (XWS_NAME);
99 create index XWSTR_VALUE on xwikistrings (XWS_VALUE);
100 create index XWLS_NAME on xwikilargestrings (XWL_NAME);
101 create index XWLS_VALUE on xwikilargestrings (XWL_VALUE(50));
102
Raffaello Pelagalli 12.1 103 create index xwr_isdiff on xwikircs(xwr_isdiff);
Raffaello Pelagalli 13.1 104 create index xws_name on xwikistatsdoc (XWS_NAME);
105 create index xws_number on xwikistatsdoc (XWS_NUMBER);
106 create index xws_classname on xwikistatsdoc (XWS_CLASSNAME);
107 create index xws_action on xwikistatsdoc (XWS_ACTION);
108 create index xws_page_views on xwikistatsdoc (XWS_PAGE_VIEWS);
109 create index xws_unique_visitors on xwikistatsdoc (XWS_UNIQUE_VISITORS);
110 create index xws_period on xwikistatsdoc (XWS_PERIOD);
111 create index xws_visits on xwikistatsdoc (XWS_VISITS);
112 create index xwr_number on xwikistatsreferer (XWR_NUMBER);
113 create index xwr_name on xwikistatsreferer (XWR_NAME);
114 create index xwr_classname on xwikistatsreferer (XWR_CLASSNAME);
Ludovic Dubost 14.1 115 create index xwr_referer on xwikistatsreferer (XWR_REFERER(50));
Raffaello Pelagalli 13.1 116 create index xwr_page_views on xwikistatsreferer (XWR_PAGE_VIEWS);
117 create index xwr_period on xwikistatsreferer (XWR_PERIOD);
118 create index xwv_start_date on xwikistatsvisit (XWV_START_DATE);
119 create index xwv_name on xwikistatsvisit (XWV_NAME);
120 create index xwv_page_views on xwikistatsvisit (XWV_PAGE_VIEWS);
121 create index xwv_page_saves on xwikistatsvisit (XWV_PAGE_SAVES);
122 create index xwv_downloads on xwikistatsvisit (XWV_DOWNLOADS);
Vincent Massol 18.1 123 create index xwv_end_date on xwikistatsvisit (XWV_END_DATE);
124 create index xwv_ip on xwikistatsvisit (XWV_IP);
125 create index xwv_user_agent on xwikistatsvisit (XWV_USER_AGENT(255));
126 create index xwv_cookie on xwikistatsvisit (XWV_COOKIE(255));
127 create index xwv_unique_id on xwikistatsvisit (XWV_UNIQUE_ID);
128 create index xwv_classname on xwikistatsvisit (XWV_CLASSNAME);
129 create index xwv_number on xwikistatsvisit (XWV_NUMBER);
Sergiu Dumitriu 23.3 130
131 create index xdd_fullname1 on xwikirecyclebin (xdd_fullname);
132 create index xdd_language on xwikirecyclebin (xdd_language);
133 create index xdd_date on xwikirecyclebin (xdd_date);
134 create index xdd_deleter on xwikirecyclebin (xdd_deleter);
135 create index xda_docid1 on xwikiattrecyclebin (xda_docid);
136 create index xda_doc_name on xwikiattrecyclebin (xda_doc_name);
137 create index xda_filename on xwikiattrecyclebin (xda_filename);
138 create index xda_date on xwikiattrecyclebin (xda_date);
139 create index xda_deleter on xwikiattrecyclebin (xda_deleter);
140
Ludovic Dubost 20.1 141 create index ase_requestid on activitystream_events (ase_requestid(200));
142 create index ase_stream on activitystream_events (ase_stream);
143 create index ase_date on activitystream_events (ase_date);
144 create index ase_type on activitystream_events (ase_type);
145 create index ase_application on activitystream_events (ase_application);
146 create index ase_user on activitystream_events (ase_user);
147 create index ase_wiki on activitystream_events (ase_wiki);
148 create index ase_space on activitystream_events (ase_space);
149 create index ase_page on activitystream_events (ase_page);
Sergiu Dumitriu 22.1 150 create index ase_page_date on activitystream_events (ase_page, ase_date);
Manuel Smeria 27.2 151 {{/code}}
Ludovic Dubost 1.1 152
Silvia Macovei 19.2 153 = Sanity Checks =
Ludovic Dubost 1.1 154
jeanvivienmaurice 1.6 155 To verify the consistency of your XWiki database you can run the sanity check script. The script will select rows that are inconsistent with the logics of XWiki. If no rows are selected, it means the script itself can not detect any error.
jeanvivienmaurice 1.7 156
Manuel Smeria 27.2 157 * If you use **MySQL** as your database:
158 ** The following [[sanity check script>>attach:sanitycheck.sql]] has been tested for **MySQL 5.0** (also been reported to work with MS-SQL)
159 ** Another [[script>>attach:sanitycheckMySQL4.sql]] is a variant of the previous script for **MySQL 4.x** replacing some syntax with more ancient one (still reports errors in one command)
160 * If you use **postgresql** as your database:
Silvia Macovei 19.1 161 ** Here is a [[modified version of the sanity check script>>attach:sanitycheck_postgresql.sql]], to be run in the "query" tool of pgAdmin. It is the same as the script for MySQL, except that the syntax for SQL comments is different.
Vincent Massol 22.2 162 ** pgAdmin is the GUI tool provided with postgresql to access the database. Run pgAdmin, select the ##xwiki## database, and choose the "Query" option in the "Tools" menu. Then just open the script and click on the play icon ("Execute query").
Jean-Vincent Drean 7.1 163
Silvia Macovei 19.2 164 = Database browsing =
Jean-Vincent Drean 7.1 165
Silvia Macovei 19.2 166 == DbVisualizer ==
Jean-Vincent Drean 7.1 167
Manuel Smeria 27.2 168 [[DbVisualizer>>http://www.dbvis.com/download/]] natively supports these DBs:
Jean-Vincent Drean 7.1 169
170 * DB2 for Windows/Linux
171 * JavaDB/Derby
172 * MySQL
173 * PostgreSQL
Manuel Smeria 27.2 174 * [[and more>>http://www.dbvis.com/features/tour/supported-databases/]]
Jean-Vincent Drean 7.1 175
Manuel Smeria 27.2 176 Any DB using the corresponding JDBC driver:
Jean-Vincent Drean 7.2 177
Manuel Smeria 27.2 178 * Example: HSQLDB by using the driver bundled with XWiki

Get Connected