Editing object with image make very long sql query

Next topic

Author	Message
stephane couzinier	Tuesday 30 January 2007 2:35:19 pm We have 400000 object and more than 4M lines in the table ezcontentobject_attribute When we try to edit an article or any object with an image attribute, the system didn't reponse anything. In the log file notice.log ez try to fetch data on the ezcontentobject_attribute : [ Jan 30 2007 23:06:37 ] [82.226.216.49] eZMySQLDB::query(2 rows, 10,976.943 ms) query number per page:14: EXPLAIN SELECT id, version FROM ezcontentobject_attribute WHERE data_type_string = 'ezimage' and data_text like '%url="var/premiere/storage/images/jeux/quizz-la-mome/4171905-4-fre-FR/quizz_la_mome.png"%' This query take 12s. the query didn't use any index If somebody have an idea Bug http://issues.ez.no/IssueView.php?Id=10130 don't flush the cache
Xavier Dutoit	Wednesday 31 January 2007 1:24:25 am Salut, What's your setting ? Also, why do you have a explain in the log ? Do you have a debugsql+ something ? X+ http://www.sydesy.com
stephane couzinier	Wednesday 31 January 2007 2:19:46 am salut xavier Next time I will speak french... Just for information - the bug appear only when the database is overload. I think we have to change some fetch... - we use 1 server for the backoffice 4 servers for the live site 1 mysql server(6Go of ram, 2*CPU intel xeon 3G) For the setting: Production site acces: [DebugSettings] DebugOutput=disabled DebugRedirection=disabled [TemplateSettings] Debug=disabled ShowXHTMLCode=disabled ShowUsedTemplates=disabled DelayedCacheBlockCleanup=disabled [ContentSettings] ViewCaching=enabled [TemplateSettings] TemplateCache=enabled TemplateCompile=enabled For the debug siteacces: [DatabaseSettings] ConnectRetries=5 QueryAnalysisOutput=enabled SlowQueriesOutput=2 DebugTransactions=enabled Transactions=disabled Socket=enabled SQLOutput=enabled [DebugSettings] DebugOutput=enabled DebugRedirection=enabled [TemplateSettings] Debug=enabled ShowXHTMLCode=disabled ShowUsedTemplates=enabled When the bug appear, there is no trace in the error.log or in the php log. I find a query like this when we try to login to the admin. But I don't know why ez fetch this table when we try to login We use the same version of ez for another web site and it work find But there is less data... I don't know why there is EXPLAIN in the query Tks for your help. don't flush the cache
Kristof Coomans	Wednesday 31 January 2007 3:08:19 am The EXPLAIN sql statements are executed because you have QueryAnalysisOutput=enabled You can disable them with QueryAnalysisOutput=disabled independent eZ Publish developer and service provider \| http://blog.coomanskristof.be \| http://ezpedia.org
stephane couzinier	Wednesday 31 January 2007 3:55:18 am thanks for the information. The QueryAnalysisOutput is disabled for the production site. I just enable all debug output for a "debug" site acces. I just don't know why ez make a query like this there is not index on the data_type_string and of course an the data_text Why ez need to do this and why don't you use the contentobject_id if I change the query to SELECT * FROM ezcontentobject_attribute WHERE data_type_string = 'ezimage' AND data_text LIKE '%url="var/premiere/storage/images/jeux/quizz-la-mome/4171905-4-fre-FR/quizz_la_mome.png"%' AND contentobject_id =335727 I have the same result but in 0.0021s a little faster... Do you know, if there is a quick way to change this query in the kernel don't flush the cache
stephane couzinier	Wednesday 31 January 2007 4:48:21 am Maybe I find something The function fetchImageAttributesByFilepath in the file ezimagefile.php generate the query In ezimagealiashandler.php the function "removeAliases" parse all image alias and make a query foreach alias, I have a lot of image alias. So I have a lot of sql query for "nothing" I will change my setting But for the query I'll like to add a parameter to the function fetchImageAttributesByFilepath (add the contentobject_id) In the function removeAliases I have the value of the contentobject_id so it easy to change the code and add the parameter. BUT I don't know everything (nothing ...) in the kernel, If ez crew didn't use the contentobject_id maybe there is a good reason ? Tks for your help don't flush the cache
Xavier Dutoit	Wednesday 31 January 2007 7:07:49 am I suppose not having more than 2 images in the dev config isn't considered as a valid reason ? You have 2 ms as the lower limit to output the query. Out of curiosity, do you have lots of other sqls that are slow with your huge db (probably worthwhile rising the 2 ms to something a wee bit higher) ? It would be a nice profiling tool to log all the url+slow queries to see if things can be improved at the db level. Does it exist already with the right settings here and there ? X+ http://www.sydesy.com
stephane couzinier	Wednesday 31 January 2007 9:47:25 am So I find a solution, I hack the kernel , I hope it won't have any effect;-) We can edit object with no pb I just change 2 functions 1) removeAliases Change line 707 , replace $dbResult = eZImageFile::fetchImageAttributesByFilepath( $filepath)); with $dbResult = eZImageFile::fetchImageAttributesByFilepath( $filepath,$contentObjectAttributeID); 2)fetchImageAttributesByFilepath add the new parameter and change the select $query = "SELECT id, version FROM ezcontentobject_attribute WHERE data_type_string = 'ezimage' and contentobject_id =$contentobject_id and data_text like '%url=\"$filepath\"%'"; Xavier for your questions: We use 40 image alias. We use a lot of xml attribute => a lot of large data_text attribute =>maybe one reason for poor perf on this query For the slow query Yes we have a "lot" of slow query (1s or more), 3000 slow query for 11 805 376 query I know where to change, i just have to change it... It will be nice if somebody of ez can validate my hack. don't flush the cache
stephane couzinier	Thursday 01 February 2007 8:08:22 am I know I'm the only one with this pb. If anotehr need this code change my first sql query with this one or you will lose data $query = "SELECT id, version FROM ezcontentobject_attribute WHERE data_type_string = 'ezimage' and id =$contentobject_id and data_text like '%url=\"$filepath\"%'"; don't flush the cache
Xavier Dutoit	Thursday 01 February 2007 2:36:30 pm salut, can you post a bug report with your patch ? http://www.sydesy.com
stephane couzinier	Thursday 01 February 2007 3:12:55 pm Salut I post the change files http://issues.ez.no/IssueView.php?Id=10130&activeItem=1 Must be validate by other users. The first version have a smal bug... Images were lost when you change the name of an object ($node.name) Maybe my solution is not the good one (I don't know the kernel of EZ)but Store image with an id will resolve a lot of probleme like this, see my post in the sugestion part for this part of code, somebody could explain why you check each image alias on the SGBD, why don't you check it on the disk? Maybe I don't understand all of the code.. don't flush the cache