Show last authors
1 {{box cssClass="floatinginfobox" title="**Contents**"}}
2 {{toc/}}
3 {{/box}}
4
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>>Documentation.AdminGuide.InstallationWAR||anchor="HInstallandconfigureaRelationalDatabase"]] which contains information on how to setup XWiki for multiple databases.
6
7 = Indexes =
8
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).
10
11 == Indexes for XWiki versions after 4.3 ==
12
13 {{info}}
14 Even though we've configured XWiki to let Hibernate create indexes by default, there are some limitations and some indexes cannot be created automatically for string columns longer than 255 chars ([[MySQL has a limit of 255 characters>>http://dev.mysql.com/doc/refman/5.7/en/column-indexes.html]] - See also [[InnoDB limits>>http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html]]). Thus, at the moment the indexes listed below need to be created manually (for all databases since our default Hibernate configuration file doesn't specify creating indexes for those).
15 {{/info}}
16
17 {{code}}
18 // Required
19 create index xwl_value on xwikilargestrings (xwl_value(50));
20 create index xwd_parent on xwikidoc (xwd_parent(50));
21 create index xwd_class_xml on xwikidoc (xwd_class_xml(20));
22 create index ase_page_date on activitystream_events (ase_page, ase_date);
23 create index xda_docid1 on xwikiattrecyclebin (xda_docid);
24 create index ase_param1 on activitystream_events (ase_param1(200));
25 create index ase_param2 on activitystream_events (ase_param2(200));
26 create index ase_param3 on activitystream_events (ase_param3(200));
27 create index ase_param4 on activitystream_events (ase_param4(200));
28 create index ase_param5 on activitystream_events (ase_param5(200));
29 // Only required if you use stats (feature is off by default)
30 create index xws_number on xwikistatsdoc (XWS_NUMBER);
31 create index xws_classname on xwikistatsdoc (XWS_CLASSNAME);
32 create index xwr_number on xwikistatsreferer (XWR_NUMBER);
33 create index xwr_classname on xwikistatsreferer (XWR_CLASSNAME);
34 create index xwr_referer on xwikistatsreferer (XWR_REFERER(50));
35 create index xwv_user_agent on xwikistatsvisit (XWV_USER_AGENT(255));
36 create index xwv_cookie on xwikistatsvisit (XWV_COOKIE(255));
37 create index xwv_classname on xwikistatsvisit (XWV_CLASSNAME);
38 create index xwv_number on xwikistatsvisit (XWV_NUMBER);
39 {{/code}}
40
41 {{info}}
42 Note to XWiki developers: The following indexes could be created automatically though since they're less than 255 characters and thus should be added in a future version of XWiki so that they don't need to be created manually:
43
44 {{code}}
45 create index xws_number on xwikistatsdoc (XWS_NUMBER);
46 create index xws_classname on xwikistatsdoc (XWS_CLASSNAME);
47 create index xwr_number on xwikistatsreferer (XWR_NUMBER);
48 create index xwr_classname on xwikistatsreferer (XWR_CLASSNAME);
49 create index xwv_classname on xwikistatsvisit (XWV_CLASSNAME);
50 create index xwv_number on xwikistatsvisit (XWV_NUMBER);
51 create index xda_docid1 on xwikiattrecyclebin (xda_docid);
52 {{/code}}
53 {{/info}}
54
55 == Indexes for XWiki versions after 3.2 ==
56
57 {{code}}
58 create index xwl_value on xwikilargestrings (xwl_value(50));
59 create index xwd_parent on xwikidoc (xwd_parent(50));
60 create index xwd_class_xml on xwikidoc (xwd_class_xml(20));
61 create index xws_number on xwikistatsdoc (XWS_NUMBER);
62 create index xws_classname on xwikistatsdoc (XWS_CLASSNAME);
63 create index xwr_number on xwikistatsreferer (XWR_NUMBER);
64 create index xwr_classname on xwikistatsreferer (XWR_CLASSNAME);
65 create index xwr_referer on xwikistatsreferer (XWR_REFERER(50));
66 create index xwv_user_agent on xwikistatsvisit (XWV_USER_AGENT(255));
67 create index xwv_cookie on xwikistatsvisit (XWV_COOKIE(255));
68 create index xwv_classname on xwikistatsvisit (XWV_CLASSNAME);
69 create index xwv_number on xwikistatsvisit (XWV_NUMBER);
70 create index ase_requestid on activitystream_events (ase_requestid(200));
71 create index ase_page_date on activitystream_events (ase_page, ase_date);
72 create index xda_docid1 on xwikiattrecyclebin (xda_docid);
73 create index ase_param1 on activitystream_events (ase_param1(200));
74 create index ase_param2 on activitystream_events (ase_param2(200));
75 create index ase_param3 on activitystream_events (ase_param3(200));
76 create index ase_param4 on activitystream_events (ase_param4(200));
77 create index ase_param5 on activitystream_events (ase_param5(200));
78 {{/code}}
79
80 {{info}}
81 * Most indexes are created automatically starting with XWiki 3.2 and only the indexes listed above need to be created.
82 * 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.
83 {{/info}}
84
85 == Indexes for XWiki versions before 3.2 ==
86
87 {{code}}
88 create index DOC_SPACE on xwikidoc (XWD_WEB);
89 create index DOC_NAME on xwikidoc (XWD_NAME);
90 create index DOC_FULLNAME on xwikidoc (XWD_FULLNAME);
91 create index DOC_TITLE on xwikidoc (XWD_TITLE);
92 create index DOC_PARENT on xwikidoc (XWD_PARENT(50));
93 create index DOC_CREATION_DATE on xwikidoc (XWD_CREATION_DATE);
94 create index DOC_DATE on xwikidoc (XWD_DATE);
95 create index DOC_CONTENT_UPDATE_DATE on xwikidoc (XWD_CONTENT_UPDATE_DATE);
96 create index DOC_CREATOR on xwikidoc (XWD_CREATOR);
97 create index DOC_AUTHOR on xwikidoc (XWD_AUTHOR);
98 create index DOC_CONTENT_AUTHOR on xwikidoc (XWD_CONTENT_AUTHOR);
99 create index DOC_LANGUAGE on xwikidoc (XWD_LANGUAGE);
100 create index DOC_DEFAULT_LANGUAGE on xwikidoc (XWD_DEFAULT_LANGUAGE);
101 create index DOC_CLASS_XML on xwikidoc (XWD_CLASS_XML(20));
102 create index DOC_MINOREDIT on xwikidoc (XWD_MINOREDIT);
103 create index DOC_HIDDEN on xwikidoc (XWD_HIDDEN);
104 create index OBJ_NAME on xwikiobjects (XWO_NAME);
105 create index OBJ_CLASSNAME on xwikiobjects (XWO_CLASSNAME);
106 create index OBJ_NUMBER on xwikiobjects (XWO_NUMBER);
107
108 create index XWINT_NAME on xwikiintegers (XWI_NAME);
109 create index XWINT_VALUE on xwikiintegers (XWI_VALUE);
110 create index XWLONG_NAME on xwikilongs (XWL_NAME);
111 create index XWLONG_VALUE on xwikilongs (XWL_VALUE);
112 create index XWFLOAT_NAME on xwikifloats (XWF_NAME);
113 create index XWFLOAT_VALUE on xwikifloats (XWF_VALUE);
114 create index XWDOUBLE_NAME on xwikidoubles (XWD_NAME);
115 create index XWDOUBLE_VALUE on xwikidoubles (XWD_VALUE);
116 create index XWDATE_NAME on xwikidates (XWS_NAME);
117 create index XWDATE_VALUE on xwikidates (XWS_VALUE);
118 create index XWSTR_NAME on xwikistrings (XWS_NAME);
119 create index XWSTR_VALUE on xwikistrings (XWS_VALUE);
120 create index XWLS_NAME on xwikilargestrings (XWL_NAME);
121 create index XWLS_VALUE on xwikilargestrings (XWL_VALUE(50));
122
123 create index xwr_isdiff on xwikircs(xwr_isdiff);
124 create index xws_name on xwikistatsdoc (XWS_NAME);
125 create index xws_number on xwikistatsdoc (XWS_NUMBER);
126 create index xws_classname on xwikistatsdoc (XWS_CLASSNAME);
127 create index xws_action on xwikistatsdoc (XWS_ACTION);
128 create index xws_page_views on xwikistatsdoc (XWS_PAGE_VIEWS);
129 create index xws_unique_visitors on xwikistatsdoc (XWS_UNIQUE_VISITORS);
130 create index xws_period on xwikistatsdoc (XWS_PERIOD);
131 create index xws_visits on xwikistatsdoc (XWS_VISITS);
132 create index xwr_number on xwikistatsreferer (XWR_NUMBER);
133 create index xwr_name on xwikistatsreferer (XWR_NAME);
134 create index xwr_classname on xwikistatsreferer (XWR_CLASSNAME);
135 create index xwr_referer on xwikistatsreferer (XWR_REFERER(50));
136 create index xwr_page_views on xwikistatsreferer (XWR_PAGE_VIEWS);
137 create index xwr_period on xwikistatsreferer (XWR_PERIOD);
138 create index xwv_start_date on xwikistatsvisit (XWV_START_DATE);
139 create index xwv_name on xwikistatsvisit (XWV_NAME);
140 create index xwv_page_views on xwikistatsvisit (XWV_PAGE_VIEWS);
141 create index xwv_page_saves on xwikistatsvisit (XWV_PAGE_SAVES);
142 create index xwv_downloads on xwikistatsvisit (XWV_DOWNLOADS);
143 create index xwv_end_date on xwikistatsvisit (XWV_END_DATE);
144 create index xwv_ip on xwikistatsvisit (XWV_IP);
145 create index xwv_user_agent on xwikistatsvisit (XWV_USER_AGENT(255));
146 create index xwv_cookie on xwikistatsvisit (XWV_COOKIE(255));
147 create index xwv_unique_id on xwikistatsvisit (XWV_UNIQUE_ID);
148 create index xwv_classname on xwikistatsvisit (XWV_CLASSNAME);
149 create index xwv_number on xwikistatsvisit (XWV_NUMBER);
150
151 create index xdd_fullname1 on xwikirecyclebin (xdd_fullname);
152 create index xdd_language on xwikirecyclebin (xdd_language);
153 create index xdd_date on xwikirecyclebin (xdd_date);
154 create index xdd_deleter on xwikirecyclebin (xdd_deleter);
155 create index xda_docid1 on xwikiattrecyclebin (xda_docid);
156 create index xda_doc_name on xwikiattrecyclebin (xda_doc_name);
157 create index xda_filename on xwikiattrecyclebin (xda_filename);
158 create index xda_date on xwikiattrecyclebin (xda_date);
159 create index xda_deleter on xwikiattrecyclebin (xda_deleter);
160
161 create index ase_requestid on activitystream_events (ase_requestid(200));
162 create index ase_stream on activitystream_events (ase_stream);
163 create index ase_date on activitystream_events (ase_date);
164 create index ase_type on activitystream_events (ase_type);
165 create index ase_application on activitystream_events (ase_application);
166 create index ase_user on activitystream_events (ase_user);
167 create index ase_wiki on activitystream_events (ase_wiki);
168 create index ase_space on activitystream_events (ase_space);
169 create index ase_page on activitystream_events (ase_page);
170 create index ase_page_date on activitystream_events (ase_page, ase_date);
171 {{/code}}
172
173 = Sanity Checks =
174
175 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.
176
177 * If you use **MySQL** as your database:
178 ** The following [[sanity check script>>attach:sanitycheck.sql]] has been tested for **MySQL 5.0** (also been reported to work with MS-SQL)
179 ** 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)
180 * If you use **postgresql** as your database:
181 ** 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.
182 ** 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").
183
184 = Database browsing =
185
186 == DbVisualizer ==
187
188 [[DbVisualizer>>http://www.dbvis.com/download/]] natively supports these DBs:
189
190 * DB2 for Windows/Linux
191 * JavaDB/Derby
192 * MySQL
193 * PostgreSQL
194 * [[and more>>http://www.dbvis.com/features/tour/supported-databases/]]
195
196 Any DB using the corresponding JDBC driver:
197
198 * Example: HSQLDB by using the driver bundled with XWiki

Get Connected