Bug 793537 (JBEPP-616)

I meant this task so that we can profile all the queries coming to database and see if proper indexing is setup. So you would not necessarily need to do it with all DB. I guess MySql5 should be enough.

logger configuration used for storing hibernate SQL queries only.

Attachment: Added: jboss-log4j.xml

Can you attach or list out all queries generated during CRUD of user and role?

I have been logging sql queries for simple CRUD use cases (attached in a zip file). Then i've been counting the select queries with 
grep 'select this' sql.log | wc -l
for different jboss-cache eviction algorithm set. there was no difference between algorithms, the eviction seems to be fully satysfying since it keeps all the data cached as long as possible. Anyway, there seems to be a place for improvement, since i.e. the query (in create.log)
2011-02-08 15:17:15,489 DEBUG [org.hibernate.SQL] org.jboss.logging.Logger select this_.ID as ID4_2_, this_.IDENTITY_TYPE as IDENTITY2_4_2_, this_.NAME as NAME4_2_, this_.REALM as REALM4_2_, type1_.ID as ID11_0_, type1_.NAME as NAME11_0_, rm2_.ID as ID0_1_, rm2_.NAME as NAME0_1_ from jbid_io this_ inner join jbid_io_type type1_ on this_.IDENTITY_TYPE=type1_.ID inner join jbid_realm rm2_ on this_.REALM=rm2_.ID where this_.NAME=? and type1_.NAME=? and rm2_.NAME=?
is called several times with the same parameters. I will debug to understand the process better.

my use case:
start of startup.log
1. start clean EPP, after start
2. open "/portal/public/classic/"
end of startup.log
3. click on login as root icon (bottom left)
4. click on Group -> Organization -> New staff
start of create.log
5. fill in all fields, click save
6. click OK on modal confirmation window
end of create.log
7. click on Group -> Organization -> Users and groups management
start of retrieve.log
8. fill in the previously created username into the search field
9. click on search link
end of retrieve.log
10. click on the edit button for that user
start of update.log
11. change his first name
12. click save
13. click OK on modal confirmation window
end of update.log
14. fill in the previously created username into the search field
15. click on search link
start of remove.log
16. click on the delete button for that user
17. click ok on both modal confirmation windows
end of remove.log

Attachment: Added: sql_logs.zip

python script used to calculate frequencies of sql queries above.

usage:
python scriptanalyzer.py /path/to/logfile.log | less

Attachment: Added: scriptanalyzer.py

in http://anonsvn.jboss.org/repos/picketlink/idm/trunk/picketlink-idm-hibernate/src/main/java/org/picketlink/idm/impl/store/hibernate/HibernateIdentityStoreImpl.java some criteria objects are missing "setCacheable(true)". in rev 801 the criteria objects without cache set are on lines: 323, 749, 1077, 1187, 2704, 2722, 2741.