Editing object with image make very long sql query

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

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

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

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

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

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