Friday 18 June 2010 1:44:24 am

Your questions are pretty much self-answering... It shows you have investigated thoroughly the problem ;-)

- doing custom sql queries: very possible, will give you great speedups. Use ezdb api, write your own sql. Hire (or be) an sql guru to get best results. There is no "standard" "more efficient" api in ez because the most efficient query will be the one that only works in your db (you can hardcode class ids, knowledge of content tree structure and such)

- adding indexes: very much possible. It might even be a good idea in conjunction with the point above

- using solr: quite possible. By default the index IS updated on every object publication, unless you use delayedindexing; net effect: publishing of objects gets slower, as it takes into account now the indexing phase

- usage of load_data_map: try it out: enable echoing into debug of the generated sql with that param on and off, and then run the generated queries with tracing enabled to see the difference. The idea is: if it is disabled, more smaller queries are generated (to fetch data_map later on), if it is enabled, a single query with more joins is generated (all attributes are fetched in one pass)

Principal Consultant International Business
Member of the Community Project Board

Sunday 20 June 2010 8:53:08 am

After further investigation I came to an interesting point...

The SQL queries produced when fetching nodes always have joins on this tables:

• ezcontentobject_tree
• ezcontentobject
• ezcontentclass
• ezcontentobject_name

The problem is that this joins ALWAYS produce a temporary table on the disk, and that's because the tables in the join contain longtext fields (since mysql can't use memory storage for tables containing text fields). Specifically this ones:

ezcontentclass - filed "serialized_name_list"

ezcontentobject_tree - field "path_identification_string"

What happens as a result of this is that a temporary table gets created on disk on EVERY fetch made in eZ (list or tree fetch), no matter how high you set the mysql memory limits. Since doing fetches is the most used way of retrieving data in eZ, this can lead to some serious performance issues.

The first questions that comes in mind are:

• Are this two fields really so important so that have to be fetched in this join? How often are this fields actually used and can they be fetched later?
• Can some of this fields be converted to varchar?

This is a problem that eZ Team should deal with, and correct it in some future eZP versions. Here are some suggestions and ideas:

1) Convert the "serialized_name_list" to varchar

All the entries in my ezcontentclass table have this field shorter than 100 chars. Why it has to be a longtext field?

Also is this field really needed here? Can it's data be divided into some other table fields?

2) Move "path_identification_string" out of ezcontentobject_tree table

The first idea is to move this field out from ezcontentobject_tree table, for example in a new table ezcontentobject_tree_path. So that it doesn't get involved in this fetch but is fetched later when needed.

Another idea would be to convert path_identification_string to varchar. For example in my DB the longest occurrence of this field is 199. So could this conversion be done? What are the implications?

I think this issue deserves some serious consideration from the eZ development team, since it can bring some really big performance gains. So what are your opinions?

--
Nothing is impossible. Not if you can imagine it!

Hubert Farnsworth

Sunday 20 June 2010 12:39:51 pm

ps: since you tested the size of your data in those two cols and found it small, could you try to ALTER those cols to varchar and measure the speed/db load difference?

Principal Consultant International Business
Member of the Community Project Board

Saturday 09 April 2011 6:35:03 pm

Sorry for the late answer but I've been busy lately... Here are some results of my research

Removing longtext fields

I tried to alter the columns ezcontentobject_tree_path and serialized_name_list, and I have changed them to varchar(200). After that the temporary tables for this problematic joins is no longer created on disk, but is kept in memory, I have checked that by inspecting the mysql status variables before and after the query. So this is good news.

But unfortunately, this didn't give any improvement, the problematic query was not faster. Which is very strange, it seems like creating temporary table on the disk is faster than in memory.

Optimizing indexes

I found a way for a big performance speedup by adding some indexes. It involves sections and an "archiving" mechanism. I'm currently testing it and will report more when I'm done.

Marko,

Hi after reading the whole thread I have found myself in a similar issue. Unfortunately I am not as experienced as you are. I found the above issues also apply in my case, I've been tweaking my.cnf growing cache tables over a over. I now have around 4000...

As I am not an SQL expert I am trying to find out how to index join queries but Im lost here... Have you been able to Optimize indexes? I'm sure that this thread will be very helpful for a lot of people that are trying to make a stable / memory efficeint / low server load configuration

Cheers

GOOD 4 ALL