Tool session starts rely on checks of tool export controls and on the tool user's country and location. These checks are based on lookup of the user's IP address. There are inefficiencies in how these lookups are performed that may help expedite tool starts, if improved.
I propose improvements to these lookups based on common entries in the slow query log.
Nanohub has recently had queries in the slow query log (from 40 to several hundred per day) that look like this:
SELECT LOWER(countrySHORT) FROM ipcountry WHERE ipFROM <= INET_ATON('193.x.y.z') AND ipTO >= INET_ATON('193.x.y.z');
(where 193.x.y.z represents a real IP address in dotted quad format) These logged queries were taking 5-6 sec to execute.
It is inefficient to use INET_ATON() calls in the database to convert a dotted-quad IP to a long (especially in the WHERE clause, as this invalidates use of the index). This conversion could be done in the PHP instead. Quick query benchmarking indicated we can save about a factor of 2 in time by making the conversion in the PHP and making this change to the query:
SELECT LOWER(countrySHORT) FROM ipcountry WHERE ipFROM <= <IP-as-LONG> AND ipTO >= <IP-as-LONG>;
I tracked the query down to libraries/Hubzero/Geocode/Geocode.php and found a couple of other places in the Geocode library code where we are relying on the database to do string conversions and/or IP conversions.
Places where the INET_ATON heavy library code was called include the com_tools controller that handles session management (core/components/com_tools/site/controllers/sessions.php) and the utility code that validates user permissions to run export controlled tools. Since nanohub has been experiencing slow tool starts, improving efficiencies there seems like a good move.
Changes made
Relocated formatting (strings, IPs) from database to PHP in Geocode library code
Removed redundant calls to library function \Hubzero\Geocode\Geocode::ipcountry() from com_tools helper class
Removed redundant calls to library function \Hubzero\Geocode\Geocode::ipcountry() from com_tools session controller
PR Checklist
[X] Make sure there are links in the PR to the source JIRA card(s) and hubzero ticket(s)
[X] Include a brief summary of the issue in your own words
[X] Include a brief summary of the fix/changed code
[] Include a brief summary of your testing. What did you specifically do to investigate that this change has the correct results?
[] Indicate if the change needs to be hotfixed to any production hubs before a normal core rollout
[X] Double check someone is assigned to review the ticket
Summary
Tool session starts rely on checks of tool export controls and on the tool user's country and location. These checks are based on lookup of the user's IP address. There are inefficiencies in how these lookups are performed that may help expedite tool starts, if improved.
I propose improvements to these lookups based on common entries in the slow query log.
Motivation
Jira card: https://sdx-sdsc.atlassian.net/browse/NCN-789
Nanohub has recently had queries in the slow query log (from 40 to several hundred per day) that look like this:
SELECT LOWER(countrySHORT) FROM ipcountry WHERE ipFROM <= INET_ATON('193.x.y.z') AND ipTO >= INET_ATON('193.x.y.z');(where 193.x.y.z represents a real IP address in dotted quad format) These logged queries were taking 5-6 sec to execute.
It is inefficient to use INET_ATON() calls in the database to convert a dotted-quad IP to a long (especially in the WHERE clause, as this invalidates use of the index). This conversion could be done in the PHP instead. Quick query benchmarking indicated we can save about a factor of 2 in time by making the conversion in the PHP and making this change to the query:
SELECT LOWER(countrySHORT) FROM ipcountry WHERE ipFROM <= <IP-as-LONG> AND ipTO >= <IP-as-LONG>;I tracked the query down to
libraries/Hubzero/Geocode/Geocode.phpand found a couple of other places in the Geocode library code where we are relying on the database to do string conversions and/or IP conversions.Places where the INET_ATON heavy library code was called include the com_tools controller that handles session management (
core/components/com_tools/site/controllers/sessions.php) and the utility code that validates user permissions to run export controlled tools. Since nanohub has been experiencing slow tool starts, improving efficiencies there seems like a good move.Changes made
com_toolshelper classcom_toolssession controllerPR Checklist