...
SQL queries that are taking a long time to execute can indicate a performance issue. To identify long-running queries, log in to the MySQL console and run a command:
Go to
<ROOT>/etc/and copy the following information from theEnterpriseWizardConfig.xmlfile:Code Block language xml <databaseServerHome>/usr/local/EnterpriseWizard/mysql</databaseServerHome> <databaseAdminLogin>root</databaseAdminLogin> <databaseAdminPassword>123456</databaseAdminPassword> <databaseAddress>127.0.0.1</databaseAddress> <databasePort>3333</databasePort>Run the following command, using the information from step 1:
Code Block language powershell /usr/local/Agiloft_Home/mysql/bin/mysql -u root --host 127.0.0.1 --port=3333 -p sw2_std -A- When prompted, enter the password that you found in
/etc/EnterpriseWizardConfig.xml. Run the following command to list all SQL queries running for more than 15 seconds:
Code Block select * from information_schema.processlist where command != 'Sleep' and time>15;
Use OptimizationLog to Investigate Large Numbers of tmp Tables
Large numbers of tmp tables may be created because a query in a table does not have a covering index. The
| Companyname |
|---|
server.log file about the absence of such an index. You can obtain detailed activities of the optimizer by turning on the OptimizationLog debug output. This writes the activity information to the ewoptimizer.log file and helps you find which queries do not have covering indexes.To turn on the OptimizationLog debug output, go to <ROOT>/wildfly/standalone/conf, open the log4j.xml file, and change the INFO value to DEBUG in two places:
| Code Block | ||
|---|---|---|
| ||
<appender name="EWOptimizer">
<param name="File" value="${jboss.server.home.dir}/log/ewoptimizer.log"/>
<param name="DatePattern" value="'.'yyyy-MM-dd"/>
<!-- Rollover at midnight each day -->
<param name="Append" value="true"/>
<param name="Threshold" value="INFO"/>
<layout>
<param name="ConversionPattern"
value="%d %p %x %.25c:\n%m%n\n"/>
</layout>
</appender> |
and
| Code Block | ||
|---|---|---|
| ||
<category name="OptimizationLog">
<priority value="INFO"/>
<appender-ref ref="EWOptimizer"/>
</category> |
| Note |
|---|
You do not need to restart Jboss after saving the changes. |
...
Successes
| Code Block | ||||
|---|---|---|---|---|
| ||||
===========USER:akbaheti=====================
(((linked_id=852)&&(linked_table=company))&&(TEXT~=_E_W_%))&&((type<<261)||(((((type<<262,263)&&((f_from!=abc)||
(f_from=com.supportwizard.filters.parameters.NullParameter@38018f91)))&&((linked_table!=contacts.employees)||
((MultiValueLFSelectionCriteria{condition=1, swSavedSearchID=1757, internalSelectionCriteria=null, fieldName='f_to'})||
(f_from=$global.my_login))))||((type<<259,260)&&(f_from=abc)))||((type<<259,260)&&((linked_table!=contacts.employees)||
((MultiValueLFSelectionCriteria{condition=1, swSavedSearchID=1757, internalSelectionCriteria=null, fieldName='f_to'})||
(f_from=$global.my_login))))))
--------------------------------
OptimizationInformation{optimizationPossible=true, joinsNumber=2, possibleJoins={f_from=4}, impossibilityWarning='null',
columnDBNames=[id, linked_table, f_from, type, linked_id], selectedIndexDBNames2JoinColumns={cvr_2=[f_from]}, asIS=true} |
The above code displays a final query, subject to optimization, and the result of the
| Companyname |
|---|
Take note of the following parameters when you analyze the output:
- optimizationPossible=true: Success.
- joinsNumber=2: Number of joins at the final query.
- possibleJoins={f_from=4}: Possible joins.
- impossibilityWarning='null': Null for success.
- columnDBNames=[id, linked_table, f_from, type, linked_id]: Columns used at the query.
- selectedIndexDBNames2JoinColumns={cvr_2=[f_from]}: Selected covering index with fat field to join optimizations.
- asIS=true: Internal flag.
Failures
| Code Block | ||||
|---|---|---|---|---|
| ||||
=====EWOptimizationFail======USER:rmansfor=====================
((((linked_table=1545)&&(linked_id=328059))&&(((type=260)&&((email_type=1)||(outgoing_email_type=1)))||(type=261)))&&
(-TEXT-~=_E_W_%))&&
((type<<260,261)||(((((type<<262,263)&&((f_from!=abc)||
(f_from=com.supportwizard.filters.parameters.NullParameter@38018f91)))&&
((linked_table!=contacts.employees)||
((MultiValueLFSelectionCriteria{condition=1, swSavedSearchID=1757, internalSelectionCriteria=null, fieldName='f_to'})||
(f_from=$global.my_login))))||
((type=259)&&(f_from=abc)))||((type=259)&&((linked_table!=contacts.employees)||
((MultiValueLFSelectionCriteria{condition=1, swSavedSearchID=1757, internalSelectionCriteria=null, fieldName='f_to'})||(f_from=$global.my_login))))))
--------------------------------
OptimizationInformation{optimizationPossible=false, joinsNumber=0, possibleJoins={},
impossibilityWarning='There is not a covering index for the query.
Sorting (first, in order) fields: ([]),
Required fields: ([id, email_type, linked_table, f_from, outgoing_email_type, type, linked_id]),
Optional fields: ({}).',columnDBNames=[], selectedIndexDBNames2JoinColumns={}, asIS=true} |
For failures, you only need to pay attention to the impossibilityWarning parameter and the values that follow:
...
check AL_HOME//mysql/logs/ewmysql-slow-queries.log for queries that take more than 10 seconds.
If certain workflows cause a slowdown of performance, use the admin console to turn on Debugging, run through the steps, and check the server.log
...
.
Load Testing and Performance Optimization
...