Joined: Tue Sep 02, 2008 12:19 pm
Posts: 4
|
Hi,
I have a working web project that runs with Hibernate and this project is running on a host provider that uses the user resources limitation " max_questions =18000" from MYSQL.
So in order to avoid reaching this limit too often/early, I try to optimize my use of Hibernate (apart from this max_question limitation, the project itself works fine).
I've printed out sql traces thanks to Code: <property name="show_sql">true</property> and after running some tests scenarios, I noticed that even if the number of sql statements in the traces is far less than 18000, I reach the 18000 limit (MySQL exception thrown) :( and I don't manage to understand why.... Here are the 2 requests that I keep on repeating indefinitely to test this scenario :
Hibernate: select this_.id as id4_0_, this_.label as label4_0_, this_.secret as secret4_0_, this_.quotaMax as quotaMax4_0_, this_.quotaCurrent as quotaCur5_4_0_, this_.lastRequestDate as lastRequ6_4_0_ from users this_ where this_.label=? limit ?
Hibernate: select this_.id as id0_4_, this_.name as name0_4_, this_.brand as brand0_4_, this_.streetAddress as streetAd4_0_4_, this_.cityID as cityID0_4_, this_.pricesId as pricesId0_4_, this_.departementID as departem7_0_4_, this_.geoindexID as geoindexID0_4_, this_.latitude as latitude0_4_, this_.longitude as longitude0_4_, this_.accuracy as accuracy0_4_, city3_.id as id5_0_, city3_.name as name5_0_, city3_.latitude as latitude5_0_, city3_.longitude as longitude5_0_, city3_.departementID as departem5_5_0_, prices4_.id as id1_1_, prices4_.Gazole as Gazole1_1_, prices4_.Gpl as Gpl1_1_, prices4_.Sp95 as Sp4_1_1_, prices4_.GazoleDate as GazoleDate1_1_, prices4_.Sp95Date as Sp6_1_1_, prices4_.GplDate as GplDate1_1_, departemen5_.id as id2_2_, departemen5_.label as label2_2_, departemen5_.name as name2_2_, departemen5_.maxDistance as maxDista4_2_2_, departemen5_.pricesLastUpdate as pricesLa5_2_2_, departemen5_.latitude as latitude2_2_, departemen5_.longitude as longitude2_2_, geo1_.id as id3_3_, geo1_.minLat as minLat3_3_, geo1_.maxLat as maxLat3_3_, geo1_.minLong as minLong3_3_, geo1_.maxLong as maxLong3_3_, geo1_.remainingDepth as remainin6_3_3_, geo1_.maxSize as maxSize3_3_, geo1_.topLeftChild as topLeftC8_3_3_, geo1_.topRightChild as topRight9_3_3_, geo1_.bottomRightChild as bottomR10_3_3_, geo1_.bottomLeftChild as bottomL11_3_3_ from stations this_ left outer join cities city3_ on this_.cityID=city3_.id left outer join prices prices4_ on this_.pricesId=prices4_.id left outer join departements departemen5_ on this_.departementID=departemen5_.id inner join geoindex geo1_ on this_.geoindexID=geo1_.id where not (((geo1_.minLong>? or geo1_.maxLong<?) or (geo1_.minLat>? or geo1_.maxLat<?)))
I first thought that I would get this sequence around 9000 (=18000/2 requests) times but It didn't : it stops around 1000 and something when the exception saying that the max_questions limit is reached. In the MySQL logs I can see the corresponding traces : 720 Query SET autocommit=1
720 Query SET autocommit=0
720 Prepare [65] select this_.id as id4_0_, this_.label as label4_0_, this_.secret as secret4_0_, this_.quotaMax as quotaMax4_0_, this_.quotaCurrent as quotaCur5_4_0_, this_.lastRequestDate as lastRequ6_4_0_ from users this_ where this_.label=? limit ?
720 Execute [65] select this_.id as id4_0_, this_.label as label4_0_, this_.secret as secret4_0_, this_.quotaMax as quotaMax4_0_, this_.quotaCurrent as quotaCur5_4_0_, this_.lastRequestDate as lastRequ6_4_0_ from users this_ where this_.label='demo' limit 1
720 Query commit
720 Query rollback
720 Query SET autocommit=1
719 Query SET autocommit=0
719 Prepare [82] select this_.id as id0_4_, this_.name as name0_4_, this_.brand as brand0_4_, this_.streetAddress as streetAd4_0_4_, this_.cityID as cityID0_4_, this_.pricesId as pricesId0_4_, this_.departementID as departem7_0_4_, this_.geoindexID as geoindexID0_4_, this_.latitude as latitude0_4_, this_.longitude as longitude0_4_, this_.accuracy as accuracy0_4_, city3_.id as id5_0_, city3_.name as name5_0_, city3_.latitude as latitude5_0_, city3_.longitude as longitude5_0_, city3_.departementID as departem5_5_0_, prices4_.id as id1_1_, prices4_.Gazole as Gazole1_1_, prices4_.Gpl as Gpl1_1_, prices4_.Sp95 as Sp4_1_1_, prices4_.GazoleDate as GazoleDate1_1_, prices4_.Sp95Date as Sp6_1_1_, prices4_.GplDate as GplDate1_1_, departemen5_.id as id2_2_, departemen5_.label as label2_2_, departemen5_.name as name2_2_, departemen5_.maxDistance as maxDista4_2_2_, departemen5_.pricesLastUpdate as pricesLa5_2_2_, departemen5_.latitude as latitude2_2_, departemen5_.longitude as longitude2_2_, geo1_.id as id3_3_, geo1_.minLat as minLat3_3_, geo1_.maxLat as maxLat3_3_, geo1_.minLong as minLong3_3_, geo1_.maxLong as maxLong3_3_, geo1_.remainingDepth as remainin6_3_3_, geo1_.maxSize as maxSize3_3_, geo1_.topLeftChild as topLeftC8_3_3_, geo1_.topRightChild as topRight9_3_3_, geo1_.bottomRightChild as bottomR10_3_3_, geo1_.bottomLeftChild as bottomL11_3_3_ from stations this_ left outer join cities city3_ on this_.cityID=city3_.id left outer join prices prices4_ on this_.pricesId=prices4_.id left outer join departements departemen5_ on this_.departementID=departemen5_.id inner join geoindex geo1_ on this_.geoindexID=geo1_.id where not (((geo1_.minLong>? or geo1_.maxLong<?) or (geo1_.minLat>? or geo1_.maxLat<?)))
719 Execute [82] select this_.id as id0_4_, this_.name as name0_4_, this_.brand as brand0_4_, this_.streetAddress as streetAd4_0_4_, this_.cityID as cityID0_4_, this_.pricesId as pricesId0_4_, this_.departementID as departem7_0_4_, this_.geoindexID as geoindexID0_4_, this_.latitude as latitude0_4_, this_.longitude as longitude0_4_, this_.accuracy as accuracy0_4_, city3_.id as id5_0_, city3_.name as name5_0_, city3_.latitude as latitude5_0_, city3_.longitude as longitude5_0_, city3_.departementID as departem5_5_0_, prices4_.id as id1_1_, prices4_.Gazole as Gazole1_1_, prices4_.Gpl as Gpl1_1_, prices4_.Sp95 as Sp4_1_1_, prices4_.GazoleDate as GazoleDate1_1_, prices4_.Sp95Date as Sp6_1_1_, prices4_.GplDate as GplDate1_1_, departemen5_.id as id2_2_, departemen5_.label as label2_2_, departemen5_.name as name2_2_, departemen5_.maxDistance as maxDista4_2_2_, departemen5_.pricesLastUpdate as pricesLa5_2_2_, departemen5_.latitude as latitude2_2_, departemen5_.longitude as longitude2_2_, geo1_.id as id3_3_, geo1_.minLat as minLat3_3_, geo1_.maxLat as maxLat3_3_, geo1_.minLong as minLong3_3_, geo1_.maxLong as maxLong3_3_, geo1_.remainingDepth as remainin6_3_3_, geo1_.maxSize as maxSize3_3_, geo1_.topLeftChild as topLeftC8_3_3_, geo1_.topRightChild as topRight9_3_3_, geo1_.bottomRightChild as bottomR10_3_3_, geo1_.bottomLeftChild as bottomL11_3_3_ from stations this_ left outer join cities city3_ on this_.cityID=city3_.id left outer join prices prices4_ on this_.pricesId=prices4_.id left outer join departements departemen5_ on this_.departementID=departemen5_.id inner join geoindex geo1_ on this_.geoindexID=geo1_.id where not (((geo1_.minLong>9.9945956370941 or geo1_.maxLong<9.8149325802702) or (geo1_.minLat>51.927408376436 or geo1_.maxLat<51.747745319612)))
719 Query commitMy Hibernate configuration looks like this : Code: <hibernate-configuration> <session-factory > <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mobicarbu</property> <property name="hibernate.connection.username">xxxx</property> <property name="hibernate.connection.password">xxxx</property> <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property> <property name="connection.autoReconnect">true</property> <property name="connection.autoReconnectForPools">true</property> <property name="connection.is-connection-validation-required">true</property> <property name="hibernate.connection.characterEncoding">UTF-8</property> <property name="hibernate.connection.useUnicode">true</property> <!-- configuration pool via c3p0--> <property name="c3p0.min_size">5</property> <property name="c3p0.max_size">20</property> <property name="c3p0.timeout">1800</property> <property name="c3p0.idleConnectionTestPeriod">60</property> <!-- <property name="c3p0.initialPoolSize">20</property> <property name="c3p0.maxPoolSize">100</property> <property name="c3p0.maxIdleTime">1200</property> <property name="c3p0.maxStatements">50</property> <property name="c3p0.minPoolSize">10</property> --> <property name="dialect">org.hibernate.dialect.MySQLDialect</property> <!-- dialect for MySQL --> <property name="current_session_context_class">thread</property> <!-- Enable Hibernate's automatic session context management --> <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property> <!-- Disable the second-level cache --> <property name="show_sql">true</property> <!-- Echo all executed SQL to stdout --> <mapping resource="xxxxx/repository/GazStation.hbm.xml"/> <mapping resource="xxxxx/repository/Prices.hbm.xml"/> <mapping resource="xxxxx/repository/Departements.hbm.xml"/> <mapping resource="xxxxx/repository/Geoindex.hbm.xml"/> <mapping resource="xxxxx/repository/User.hbm.xml"/> <mapping resource="xxxxx/repository/City.hbm.xml"/> <mapping resource="xxxxxx/repository/Configuration.hbm.xml"/> </session-factory> </hibernate-configuration> Can someone explain me how to avoid reaching this max_questions limit too early (without changing the DB user used by Hibernate on the fly ;) or tell me how hibernate can influence the counting of questions/requests (max_questions) used by MySQL ?
Any explanation, link is welcome.
Thanks for your help,
Lionel.
|
|
Login
FAQ
Search