Expansion of system (and not only) tables in MODX Revolution
At the moment I am engaged in alteration of one news portal on MODX Revolution. Since the attendance on the site is up to 100,000 people per day, the question of productivity here is one of the most important. Taking into account the fact that currently there are more than 75,000 articles in the database, the site’s brakes are almost guaranteed with the wrong (and even with the traditional MODX approach to development), and if the frequency of visits exceeds the query execution time, the server will generally fall. Here are some of the techniques involved here for solving these problems, and I will describe in this article.
Surely many people know that when updating the MODX cache, it goes through all the documents and stuffs the resource map into the context cache. If someone does not know, I wrote about it in detail here . And although in MODX, starting from version 2.2.7 (or around that), it is possible to disable resource map caching in the settings (system setting cache_alias_map ) this problem is only partially solved - MODX does not cache document URLs, but the structure with ID-schnick digits anyway going through all the documents from the database. This leads to the fact that, firstly, the context cache file grows, and secondly, the script may simply not run in 30 seconds and the cache file will be beaten, which can generally lead to fatal errors and make the site inoperable.
But even if the server is still able to pull all the documents and stuff everything into the cache, let's look at the comparative figures for one request with different settings. These numbers will be very relative for much depends on the server settings and on different servers the memory consumption of the same site will be different, but in comparison these figures will give an idea of the difference of states. To estimate the memory consumption, I will call getdata processor to receive 10 articles.
')
So, option one: Full resource map caching is enabled.
Option two: Full resource map caching is disabled (system setting cache_alias_map == false).
Option Three: Caching of the resource map with the cacheOptimizer patch is completely disabled .
And that's just 75,000 resources. On hundreds of thousands, the difference will be much more palpable.
There are of course here and cons. For example, Wayfinder, which builds a menu based on alias map data, will not work. Here you have to collect the menu itself. I most often use the menu processor I wrote about here (see section 2. Replacing Wayfinder ).
But this is the main and most interesting reason for writing this topic. There are probably no MODX-developers who did not use TV-fieldTVs . They solve two problems at once: 1. add custom fields to documents; 2. provide different interfaces for editing them depending on the type of field.
But they also have a serious disadvantage - they are all stored in the same table. This adds several problems at once:
1. You cannot control the uniqueness of values at the database level.
2. You can not use different data types for different TV-fields. All data of TV-fields are contained in a single column value with the data type mediumtext. That is, we cannot use more data, and we will store numeric values as lower-case (which imposes additional requirements on the formation of a query with sorting), and we do not have to compare the data from different columns and many, many more unpleasant because of this.
3. Poor performance when sampling from several tables. For example, we have several TV-fields for one document, of which at least 2-3 fields are almost always filled. We want to get in the request immediately the data and documents and fields to them. We have two main options for forming a request for this:
1. Simply add a table of TV-NIS.
But there is a serious disadvantage: in the resulting table we get C * TV number of records, where C is the number of records in site_content, and TV is the number of records in the table site_tmplvar_contentvalues for each document separately. That is, if we have, for example, 100 document recordings and 3 TV recordings for each document (on average), we will end up with 100 * 3 = 300 recordings.
Since, for this reason, as a result, there was more than one resultant record per document, it is necessary to further process the data at the PHP level in order to form unique data. This is what we get in the getdata processor . And it also increases the load and increases the execution time.
Here I have in this news portal just had an average of 3 main entries per document. As a result, ~ 225,000 TV records. Even with query optimization, performing with conditions took 1-4 seconds, which is very long.
2. Join each TV field separately.
Sample request:
Such a query will work out faster, because the result table will have as many records as document records, but still the load will not be small when the count of records goes to tens and hundreds of thousands, and the number of TV-shecks will be over a dozen (after all, each TV the scale is a plus of another table joining).
Of course, the best option in this case is storing the TV values in the site_content system table itself, that is, each value is stored in a separate column of this table.
If anyone thinks that this is another lesson on the subject CRC, then this is not quite so. Traditionally, we were taught to expand the existing classes with our own and add the columns we need there (or even prescribe our own table). But this path is not optimal. The main problem here is that we are somehow expanding the class, but not changing it. Extensions concern only the expanding (and not the expanding) class, as well as those extending classes that will extend our class. Confusing, but difficult to say easier. Will explain. We have a base class of modResource. It is extended by the modDocument, modWebLink, modSimLink, etc. classes. All of them inherit from modResource map table. If we expand the modResource class with our class, then there will be new columns in our class that we will add, but they will not be in the modDocument class, since it does not extend our class. In order for information about new columns to appear in all classes extending modResource, this information must be in the modResource class itself. But how to do it without touching the system files themselves? .. In fact, I wrote about this partly more than two years ago (I moved the article here ), but only now I implemented it in combat mode. We do this:
1. Create a new component that will be loaded as extensionPackage (wrote about it in detail here ).
2. Create new columns in the site_content table via phpMyAdmin or something like that.
3. Using the CMPGenerator, we generate a separate package with the site_content table map. In this map there will be a description of your new columns and tables.
4. We register the data of your columns and indexes in your package in the metadata.mysql.php file (an example of such a file can be seen in our assembly ShopModxBox).
Read it carefully. It adds information about two columns and one index to the site_content table.
Let's make sure the columns were actually added. Run this code in the console:
We will see the following result:
Now we can work with the system table with our custom fields. For example, you can write this:
In the table for this document will be recorded our value.
It is clear that with this approach we have the problem of migration from such a custom site to a pure MODX, because there are no our custom fields and indices in the tables. But in fact, this is not a problem at all. The fact is that as we generate a map from tables, we can also create tables, columns and indexes from map descriptions of classes. Creating a column or index is very simple:
In this case, it is not necessary to specify any data of columns and indexes except their names. XPDO will receive this data from our map and use it when creating the described column or index.
If you assemble your component into a normal installation package, then you can directly write a script there so that when you install the package, your custom columns and indices are immediately created in the tables.
As I said above, the convenience of TV-NIS is that various control elements are created for them (text fields, drop-down lists, checkboxes, radio boxes, etc.). Plus, in the native form editor, you can demarcate the rights to certain TV fields, so that someone can’t see / edit private fields. In fact, it is possible, if you really want to, but still private fields will not be a callous to the eyes of someone who does not like. And it’s just that I don’t want to lose these mechanisms, because otherwise I’ll have to set up my own interfaces to manage this data, which is very labor-intensive. I would like to use the native resource editor to edit such data. There is no perfect mechanism here, but I have worked a less suitable option. Its meaning is to substitute a TV-field with its custom value at the time of the plug-in editing document form, while saving the document to intercept the TV-shki data and save this data into our custom fields. Unfortunately, it does not work here as it should be (it’s just because the API doesn’t allow it), so we cannot affect the data transmitted to the document processor, which means that TVshi data will still be written to the TVs table, but this is not a problem - just after saving the document automatically, let's clean this label and that's it. Here is an example of a plugin that triggers three events (1. rendering the document editing form with TV field substitution and custom data, 2. receiving data and changing the document object before saving it, 3. cleaning unnecessary data).
Thanks to this plugin, custom data is rendered in the document editing form and processed when it is saved.
Of the 225+ thousand entries in the table of additional fields, only 78 remain. Of course, not all of the TVs will appear in the system table (but only those used for searching and sorting), and some data will of course be in the table of TV fields, but the load nevertheless seriously decreased, and requests became simpler.
UPD: A more universal plugin for rendering and processing tvsh to.
1. Long generation of cache.
Surely many people know that when updating the MODX cache, it goes through all the documents and stuffs the resource map into the context cache. If someone does not know, I wrote about it in detail here . And although in MODX, starting from version 2.2.7 (or around that), it is possible to disable resource map caching in the settings (system setting cache_alias_map ) this problem is only partially solved - MODX does not cache document URLs, but the structure with ID-schnick digits anyway going through all the documents from the database. This leads to the fact that, firstly, the context cache file grows, and secondly, the script may simply not run in 30 seconds and the cache file will be beaten, which can generally lead to fatal errors and make the site inoperable.
But even if the server is still able to pull all the documents and stuff everything into the cache, let's look at the comparative figures for one request with different settings. These numbers will be very relative for much depends on the server settings and on different servers the memory consumption of the same site will be different, but in comparison these figures will give an idea of the difference of states. To estimate the memory consumption, I will call getdata processor to receive 10 articles.
')
So, option one: Full resource map caching is enabled.
Context cache file size: 5,792,604 bytes.
Memory consumption upon request: 28.25 Mb
Time: 0.06-0.1 sec.
Option two: Full resource map caching is disabled (system setting cache_alias_map == false).
Context cache file size: 1,684,342 bytes.
Memory consumption upon request: 15,5 Mb
Time: 0.03-0.06 seconds.
Option Three: Caching of the resource map with the cacheOptimizer patch is completely disabled .
Context cache file size: 54,945 bytes.
Memory consumption upon request: 4.5 Mb
Time: 0.02-0.03 seconds
And that's just 75,000 resources. On hundreds of thousands, the difference will be much more palpable.
There are of course here and cons. For example, Wayfinder, which builds a menu based on alias map data, will not work. Here you have to collect the menu itself. I most often use the menu processor I wrote about here (see section 2. Replacing Wayfinder ).
2. Poor performance due to TV-parameters of documents.
But this is the main and most interesting reason for writing this topic. There are probably no MODX-developers who did not use TV-field
But they also have a serious disadvantage - they are all stored in the same table. This adds several problems at once:
1. You cannot control the uniqueness of values at the database level.
2. You can not use different data types for different TV-fields. All data of TV-fields are contained in a single column value with the data type mediumtext. That is, we cannot use more data, and we will store numeric values as lower-case (which imposes additional requirements on the formation of a query with sorting), and we do not have to compare the data from different columns and many, many more unpleasant because of this.
3. Poor performance when sampling from several tables. For example, we have several TV-fields for one document, of which at least 2-3 fields are almost always filled. We want to get in the request immediately the data and documents and fields to them. We have two main options for forming a request for this:
1. Simply add a table of TV-NIS.
$q = $modx->newQuery("modResource"); $alias = $q->getAlias(); $q->leftJoin("modTemplateVarResource", "tv", "tv.contentid = {$alias}.id"); $c->select(array( "tv.*", "{$alias}.*", )); But there is a serious disadvantage: in the resulting table we get C * TV number of records, where C is the number of records in site_content, and TV is the number of records in the table site_tmplvar_contentvalues for each document separately. That is, if we have, for example, 100 document recordings and 3 TV recordings for each document (on average), we will end up with 100 * 3 = 300 recordings.
Since, for this reason, as a result, there was more than one resultant record per document, it is necessary to further process the data at the PHP level in order to form unique data. This is what we get in the getdata processor . And it also increases the load and increases the execution time.
Here I have in this news portal just had an average of 3 main entries per document. As a result, ~ 225,000 TV records. Even with query optimization, performing with conditions took 1-4 seconds, which is very long.
2. Join each TV field separately.
Sample request:
$q = $modx->newQuery("modResource"); $alias = $q->getAlias(); $q->leftJoin("modTemplateVarResource", "tv1", "tv1.tmplvarid = 1 AND tv1.contentid = {$alias}.id"); $q->leftJoin("modTemplateVarResource", "tv2", "tv2.tmplvarid = 2 AND tv2.contentid = {$alias}.id"); // ......... $c->select(array( "tv1.value as tv1_value", "tv2.value as tv2_value", "{$alias}.*", )); Such a query will work out faster, because the result table will have as many records as document records, but still the load will not be small when the count of records goes to tens and hundreds of thousands, and the number of TV-shecks will be over a dozen (after all, each TV the scale is a plus of another table joining).
Of course, the best option in this case is storing the TV values in the site_content system table itself, that is, each value is stored in a separate column of this table.
If anyone thinks that this is another lesson on the subject CRC, then this is not quite so. Traditionally, we were taught to expand the existing classes with our own and add the columns we need there (or even prescribe our own table). But this path is not optimal. The main problem here is that we are somehow expanding the class, but not changing it. Extensions concern only the expanding (and not the expanding) class, as well as those extending classes that will extend our class. Confusing, but difficult to say easier. Will explain. We have a base class of modResource. It is extended by the modDocument, modWebLink, modSimLink, etc. classes. All of them inherit from modResource map table. If we expand the modResource class with our class, then there will be new columns in our class that we will add, but they will not be in the modDocument class, since it does not extend our class. In order for information about new columns to appear in all classes extending modResource, this information must be in the modResource class itself. But how to do it without touching the system files themselves? .. In fact, I wrote about this partly more than two years ago (I moved the article here ), but only now I implemented it in combat mode. We do this:
1. Create a new component that will be loaded as extensionPackage (wrote about it in detail here ).
2. Create new columns in the site_content table via phpMyAdmin or something like that.
3. Using the CMPGenerator, we generate a separate package with the site_content table map. In this map there will be a description of your new columns and tables.
4. We register the data of your columns and indexes in your package in the metadata.mysql.php file (an example of such a file can be seen in our assembly ShopModxBox).
For example, I have this file looks like this
<?php $custom_fields = array( "modResource" => array( "fields" => array( "article_type" => array( "defaultValue" => NULL, "metaData" => array ( 'dbtype' => 'tinyint', 'precision' => '3', 'attributes' => 'unsigned', 'phptype' => 'integer', 'null' => true, 'index' => 'index', ), ), "image" => array( "defaultValue" => NULL, "metaData" => array ( 'dbtype' => 'varchar', 'precision' => '512', 'phptype' => 'string', 'null' => false, ), ), ), "indexes" => array( 'article_type' => array ( 'alias' => 'article_type', 'primary' => false, 'unique' => false, 'type' => 'BTREE', 'columns' => array ( 'article_type' => array ( 'length' => '', 'collation' => 'A', 'null' => true, ), ), ), ), ), ); foreach($custom_fields as $class => $class_data){ foreach($class_data['fields'] as $field => $data){ $this->map[$class]['fields'][$field] = $data['defaultValue']; $this->map[$class]['fieldMeta'][$field] = $data['metaData']; } if(!empty($class_data['indexes'])){ foreach($class_data['indexes'] as $index => $data){ $this->map[$class]['indexes'][$index] = $data; } } } Read it carefully. It adds information about two columns and one index to the site_content table.
Let's make sure the columns were actually added. Run this code in the console:
$o = $modx->newObject('modDocument'); print_r($o->toArray()); We will see the following result:
Array ( [id] => [type] => document [contentType] => text/html [pagetitle] => [longtitle] => // // [article_type] => [image] => ) Now we can work with the system table with our custom fields. For example, you can write this:
$resource = $modx->getObject('modResource', $id); $resource->article_type = $article_type; $resource->save(); In the table for this document will be recorded our value.
Creating your own columns and indexes on pure MODX.
It is clear that with this approach we have the problem of migration from such a custom site to a pure MODX, because there are no our custom fields and indices in the tables. But in fact, this is not a problem at all. The fact is that as we generate a map from tables, we can also create tables, columns and indexes from map descriptions of classes. Creating a column or index is very simple:
// $manager = $modx->getManager(); // $manager->addField($className, $fieldName); // $manager->addIndex($className, $fieldName); In this case, it is not necessary to specify any data of columns and indexes except their names. XPDO will receive this data from our map and use it when creating the described column or index.
If you assemble your component into a normal installation package, then you can directly write a script there so that when you install the package, your custom columns and indices are immediately created in the tables.
Rendering your custom data in TV-fields when editing documents.
As I said above, the convenience of TV-NIS is that various control elements are created for them (text fields, drop-down lists, checkboxes, radio boxes, etc.). Plus, in the native form editor, you can demarcate the rights to certain TV fields, so that someone can’t see / edit private fields. In fact, it is possible, if you really want to, but still private fields will not be a callous to the eyes of someone who does not like. And it’s just that I don’t want to lose these mechanisms, because otherwise I’ll have to set up my own interfaces to manage this data, which is very labor-intensive. I would like to use the native resource editor to edit such data. There is no perfect mechanism here, but I have worked a less suitable option. Its meaning is to substitute a TV-field with its custom value at the time of the plug-in editing document form, while saving the document to intercept the TV-shki data and save this data into our custom fields. Unfortunately, it does not work here as it should be (it’s just because the API doesn’t allow it), so we cannot affect the data transmitted to the document processor, which means that TVshi data will still be written to the TVs table, but this is not a problem - just after saving the document automatically, let's clean this label and that's it. Here is an example of a plugin that triggers three events (1. rendering the document editing form with TV field substitution and custom data, 2. receiving data and changing the document object before saving it, 3. cleaning unnecessary data).
View code
<?php /* OnBeforeDocFormSave OnDocFormSave OnResourceTVFormRender */ switch($modx->event->name){ /* */ case 'OnResourceTVFormRender': $categories = & $scriptProperties['categories']; foreach($categories as $c_id => & $category){ foreach($category['tvs'] as & $tv){ /* */ if($tv->id == '1'){ if($document = $modx->getObject('modResource', $resource)){ $q = $modx->newQuery('modResourceTag'); $q->select(array( "GROUP_CONCAT(distinct tag_id) as tags", )); $q->where(array( "resource_id" => $document->id, )); $tags = $modx->getValue($q->prepare()); $value = str_replace(",", "||", $tags); $tv->value = $value; $tv->relativeValue = $value; $inputForm = $tv->renderInput($document, array('value'=> $tv->value)); $tv->set('formElement',$inputForm); } } /* */ else if($tv->id == 2){ if($document = $modx->getObject('modResource', $resource)){ $tv->value = $document->image; $tv->relativeValue = $document->image; $inputForm = $tv->renderInput($document, array('value'=> $tv->value)); $tv->set('formElement',$inputForm); } } /* */ else if($tv->id == 12){ if($document = $modx->getObject('modResource', $resource)){ $tv->value = $document->article_status; $tv->relativeValue = $document->article_status; $inputForm = $tv->renderInput($document, array('value'=> $tv->value)); $tv->set('formElement',$inputForm); } } } } break; // case 'OnBeforeDocFormSave': $resource = & $scriptProperties['resource']; /* . active = 0. active = 1. OnDocFormSave */ if(isset($resource->tv1)){ $tags = array(); foreach((array)$resource->Tags as $tag){ $tag->active = 0; $tags[$tag->tag_id] = $tag; } // $tags = array(); if(!empty($resource->tv1)){ foreach((array)$resource->tv1 as $tv_value){ if($tv_value){ if(!empty($tags[$tv_value])){ $tags[$tv_value]->active = 1; } else{ $tags[$tv_value] = $modx->newObject('modResourceTag', array( "tag_id" => $tv_value, )); } } } } $resource->Tags = $tags; $tags_ids = array(); foreach($resource->Tags as $tag){ if($tag->active){ $tags_ids[] = $tag->tag_id; } } $resource->tags = ($tags_ids ? implode(",", $tags_ids) : NULL); } /* */ if(isset($resource->tv2)){ $resource->image = $resource->tv2; } /* */ if(isset($resource->tv12)){ $resource->article_status = $resource->tv12; } break; /* */ case 'OnDocFormSave': $resource =& $scriptProperties['resource']; /* */ $modx->removeCollection('modResourceTag',array( 'active' => 0, 'resource_id' => $resource->id, )); /* TV-, TV-, */ $modx->removeCollection('modTemplateVarResource',array( 'tmplvarid:in' => array( 1, // 2, // 12, // ), 'contentid' => $resource->id, )); break; } Thanks to this plugin, custom data is rendered in the document editing form and processed when it is saved.
Total
Of the 225+ thousand entries in the table of additional fields, only 78 remain. Of course, not all of the TVs will appear in the system table (but only those used for searching and sorting), and some data will of course be in the table of TV fields, but the load nevertheless seriously decreased, and requests became simpler.
UPD: A more universal plugin for rendering and processing tvsh to.
Source: https://habr.com/ru/post/253737/
All Articles