- Posts: 10
- Thank you received: 0
×
Here you can get community support related to ReDJ.
performance regexp in mysql
7 years 6 months ago #5652
by marcin
Replied by marcin on topic performance regexp in mysql
hi Luigi,
do you mean to extend the redirect feature with one-to-one ? (the error feature is already one-to-one)
after last rebuild of website all my article URLs has been reorganised. And I am still from time due to SEO tunning changing my URLs. Maybe I could define some rules but for most of URLs I have to defined them separately.
I have tested again the ReDJ and actually it is quite performant even when duing RegExp but for my needs I would never use this nice feature - I need just simple one-to-one mapping. I need to save the CPU as there are other components on my side which consume resources
Would you like to add this feature in near future? This would really make ReDJ perfect tool covering all customer needs
I think the extension would be quite simple. I would suggest to keep the redirect mapping with 2 modes : one-to-one and with regexp as two separated tables. On the "add/modify redirect entry" add additional checkbox "use regexp". If an mapping is defined as 1-1 then will go to corresponding table, if as regexp then will go to second table.
The ReDJ system plugin would first check if the incoming URL is defined in the one-to-one table and if not found then will check in the second table (less performant operation as second).
In addition as global setting you could allow the administrator to globally disable/enable any of both mode if a website would use only one kind of mapping. I could imagine many administrator would like to use such "mix" mode.
best regards
Martin
do you mean to extend the redirect feature with one-to-one ? (the error feature is already one-to-one)
after last rebuild of website all my article URLs has been reorganised. And I am still from time due to SEO tunning changing my URLs. Maybe I could define some rules but for most of URLs I have to defined them separately.
I have tested again the ReDJ and actually it is quite performant even when duing RegExp but for my needs I would never use this nice feature - I need just simple one-to-one mapping. I need to save the CPU as there are other components on my side which consume resources
Would you like to add this feature in near future? This would really make ReDJ perfect tool covering all customer needs
I think the extension would be quite simple. I would suggest to keep the redirect mapping with 2 modes : one-to-one and with regexp as two separated tables. On the "add/modify redirect entry" add additional checkbox "use regexp". If an mapping is defined as 1-1 then will go to corresponding table, if as regexp then will go to second table.
The ReDJ system plugin would first check if the incoming URL is defined in the one-to-one table and if not found then will check in the second table (less performant operation as second).
In addition as global setting you could allow the administrator to globally disable/enable any of both mode if a website would use only one kind of mapping. I could imagine many administrator would like to use such "mix" mode.
best regards
Martin
Please Log in or Create an account to join the conversation.
7 years 6 months ago #5661
by marcin
Replied by marcin on topic performance regexp in mysql
Hi Luigi,
my server 4Core+32GB RAM was not responding today when I had 300 user online concurrently - database consumes 80% of CPU and all PHP threads were waiting. After disabling ReDJ system was responsive back
This kind of queries ReDJ executes are very hard to cache or optimizable by mysql engine once many concurrent threads executing.
So I can not use ReDJ any more. For bigger sites this component is a bottleneck.
Kindly ask you to implement the mentioned "simple mode" using database indexes for URLs redirection urgently
thanks in advance and best regards
Martin
my server 4Core+32GB RAM was not responding today when I had 300 user online concurrently - database consumes 80% of CPU and all PHP threads were waiting. After disabling ReDJ system was responsive back
This kind of queries ReDJ executes are very hard to cache or optimizable by mysql engine once many concurrent threads executing.
So I can not use ReDJ any more. For bigger sites this component is a bottleneck.
Kindly ask you to implement the mentioned "simple mode" using database indexes for URLs redirection urgently
thanks in advance and best regards
Martin
Please Log in or Create an account to join the conversation.
7 years 6 months ago - 7 years 6 months ago #5663
by admin
Replied by admin on topic performance regexp in mysql
Hi Martin,
I understand but i am overwhelmed by requests, my todo queue still grows, and this is probably the "worst" moment in the whole 2016 for me to find some a free time slot for a new task.
Sorry, but I promise you I will do this in the next ReDJ release, I already put this in my queue... and I can see it... at the end...
But if you do not need regexp at all you could probably change the redj query to work with "literals"...
Drop this:
And add this:
And put full URLs in the from field. You can also add an index on the table.
NOT TESTED but it should work, maybe with a little patience from you...
kind regards
Luigi
I understand but i am overwhelmed by requests, my todo queue still grows, and this is probably the "worst" moment in the whole 2016 for me to find some a free time slot for a new task.
Sorry, but I promise you I will do this in the next ReDJ release, I already put this in my queue... and I can see it... at the end...
But if you do not need regexp at all you could probably change the redj query to work with "literals"...
Drop this:
$db->setQuery('SELECT * FROM ( '
. 'SELECT * FROM #__redj_redirects '
. 'WHERE ( '
. '( (' . $db->quote($currenturi) . ' REGEXP BINARY fromurl)>0 AND (case_sensitive<>0) AND (decode_url<>0) AND (request_only<>0) ) '
. 'OR ( (' . $db->quote($currenturi_encoded) . ' REGEXP BINARY fromurl)>0 AND (case_sensitive<>0) AND (decode_url=0) AND (request_only<>0) ) '
. 'OR ( (' . $db->quote($currentfullurl) . ' REGEXP BINARY fromurl)>0 AND (case_sensitive<>0) AND (decode_url<>0) AND (request_only=0) ) '
. 'OR ( (' . $db->quote($currentfullurl_encoded) . ' REGEXP BINARY fromurl)>0 AND (case_sensitive<>0) AND (decode_url=0) AND (request_only=0) ) '
. 'OR ( (' . $db->quote($currenturi) . ' REGEXP fromurl)>0 AND (case_sensitive=0) AND (decode_url<>0) AND (request_only<>0) ) '
. 'OR ( (' . $db->quote($currenturi_encoded) . ' REGEXP fromurl)>0 AND (case_sensitive=0) AND (decode_url=0) AND (request_only<>0) ) '
. 'OR ( (' . $db->quote($currentfullurl) . ' REGEXP fromurl)>0 AND (case_sensitive=0) AND (decode_url<>0) AND (request_only=0) ) '
. 'OR ( (' . $db->quote($currentfullurl_encoded) . ' REGEXP fromurl)>0 AND (case_sensitive=0) AND (decode_url=0) AND (request_only=0) ) '
. ') '
. 'AND published=1 '
. 'ORDER BY ordering ) AS A '
. 'WHERE A.skip=\'\' '
. 'OR ( (' . $db->quote($currentfullurl) . ' REGEXP BINARY A.skip)=0 AND (case_sensitive<>0) AND (decode_url<>0) ) '
. 'OR ( (' . $db->quote($currentfullurl_encoded) . ' REGEXP BINARY A.skip)=0 AND (case_sensitive<>0) AND (decode_url=0) ) '
. 'OR ( (' . $db->quote($currentfullurl) . ' REGEXP A.skip)=0 AND (case_sensitive=0) AND (decode_url<>0) ) '
. 'OR ( (' . $db->quote($currentfullurl_encoded) . ' REGEXP A.skip)=0 AND (case_sensitive=0) AND (decode_url=0) ) ');And add this:
$db->setQuery('SELECT * FROM #__redj_redirects WHERE fromurl = ' . $db->quote($currentfullurl));And put full URLs in the from field. You can also add an index on the table.
NOT TESTED but it should work, maybe with a little patience from you...
kind regards
Luigi
Please Log in or Create an account to join the conversation.
7 years 6 months ago #5664
by marcin
Replied by marcin on topic performance regexp in mysql
hi Luigi,
thanks for fast response and the workaround. I will try this temporary change of the code as suggested tomorrow and give you my feedback.
thanks
Martin
thanks for fast response and the workaround. I will try this temporary change of the code as suggested tomorrow and give you my feedback.
thanks
Martin
Please Log in or Create an account to join the conversation.
Time to create page: 0.097 seconds