Forums / Developer / Editing object with image make very long sql query

"Please Note:
  • At the specific request of Ibexa we are changing this projects name to "Exponential" or "Exponential (CMS)" effective as of August, 11th 2025.
  • This project is not associated with the original eZ Publish software or its original developer, eZ Systems or Ibexa".

Editing object with image make very long sql query

« 
Previous topic
|
Developer
|
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