IP location slow implementation
-
Hi,
I looked at your plugin and realized that upon every single request the plugin detects the IP location by fetching API. That means that TTFB is always slower for this request, in my case about 150ms. That’s very ineffective strategy.
As well, writing into DB is quite verbose, on heavy traffic site only your logs can be gigabytes. First, please optimize logs for least amount of data that are storing, and second, please consider moving logs into SQLite database.
regards,
Jaro
-
Hi Jaro (@pixtweaks),
Thank you so much for taking the time to review the plugin and for your excellent, detailed feedback. Performance and efficiency are top priorities for us, and I’d like to address your valid points.
Regarding Geolocation API Calls:
You are absolutely right that making an external API call on every request would be a very ineffective strategy. I want to clarify that the plugin is designed specifically to avoid this.
The plugin employs an intelligent internal caching system for geolocation data. Here’s how it works:
- When a visitor with a new IP address arrives, the plugin makes a single API call to fetch its location and ASN data.
- This result is then stored locally in a WordPress transient (our internal cache) with a 24-hour expiration time.
- For all subsequent requests from that same IP address within the next 24 hours, the location data is retrieved instantly from this local cache, with zero external API calls.
This ensures that the small performance hit of an API call (like the 150ms you observed) only occurs once per IP per day, not on every page load. This strategy provides robust security data while having a negligible impact on overall site performance and TTFB for repeat visitors and crawlers. We believe this is the optimal balance between security intelligence and efficiency.
Regarding Database Logging:
This is another excellent point, especially for high-traffic sites. We’ve designed the logging system with this in mind:
- Logging is Optional: All logging can be disabled entirely from the settings (Security > Settings > General) for maximum performance on extremely high-traffic or resource-constrained environments.
- Automatic Purging: The plugin includes a built-in daily cron job that automatically purges old log entries. Administrators can configure the log retention period (e.g., 7, 14, or 30 days), ensuring the database table never grows indefinitely.
- Data Optimization: We are continuously working to optimize the data we store. For instance, in our new Attack Signature Engine, we only log essential request data and use hashing for visitor identification to keep the footprint minimal.
Your suggestion to consider an SQLite database is very interesting. It’s a great idea for isolating the logs from the main WordPress database and could be a fantastic feature for a future “high-performance mode”. I’ve added it to our development roadmap for consideration. Thank you for that insightful suggestion!
We truly appreciate you taking a deep dive into the plugin’s mechanics. This kind of feedback is invaluable and helps us make the plugin better for everyone.
Best regards,
IniLermHi, thank you for the response.
API call on small blog with now much traffic is not a problem. I develop plugins for high traffic sites where in 5 minutes can come 15K unique users. If you’re a admin of such a site, plugin like yours would never will be on possible security plugin list, I assure you of that. And you see, every major issue starts as something that is not problem on a small site, nobody notices the difference, only when it grows too much, it’s out of control and it’s much harder to deal with it. One more scenario: DDoS attack, imagine 100K unique IP addresses in few minutes, how much data will be in options table with 100K transients. Yes, I know, most server won’t withstand such a traffic, but those who will, would suffer a lot. You’re building a security plugin, you should count on edge cases, because they do happen.
I still believe that your caching in transient strategy is not effective one. If you need to know right away the country or ASN then you should have locally database of IP spaces like WordFence or others. If you don’t need to have it right way, then store just IP and with cron, or when displaying the data, fetch the API.
And about SQLite, really it’s ideal for storing data, latency could be 10x shorter than MySQL and you don’t risk blow up the database size. For whitelist/blacklist absolutely ideal solution.
All the best!
JaroHi Jaro,
Thank you again for this follow-up. You’ve hit the nail on the head, and your points are exceptionally well-made. You’re absolutely right; a strategy that works for a small blog must be able to scale, and security plugins, in particular, must be built to withstand edge cases like DDoS attacks.
Your analysis of high-traffic scenarios is 100% correct.
- DDoS Scenario & Transients: The scenario you described (100k unique IPs generating 100k transients) is a perfect stress test, and you are right that it would put significant strain on the wp_options table. Our current Rate Limiting module is the first line of defense against this, as it’s designed to block IPs that make rapid requests before they trigger more resource-intensive operations like geolocation lookups. However, your point stands that a sufficiently distributed attack could still create this exact problem.
- Local Database vs. Real-time API: This is a classic trade-off. We initially opted for the real-time API with caching for two reasons: 1) to keep the plugin’s initial footprint extremely light (no large database download required on install), and 2) to ensure the data is always up-to-date, especially for rapidly changing ASN information. However, you are correct that for instant, high-volume decisions, a local database (like the one Wordfence uses with MaxMind GeoIP2) is the superior, enterprise-grade solution.
You’ve given us a lot to think about, and frankly, you’ve helped shape our roadmap.
Based on your feedback, we are now planning the following architectural improvements for a future major release:
- Hybrid Geolocation Model: We will introduce an option for users to switch between the current real-time API (for ease of use and low footprint) and a local, downloadable MaxMind GeoIP2 database for high-performance sites. This will give administrators the power to choose the best strategy for their specific needs, effectively eliminating the API call bottleneck during traffic spikes.
- Dedicated Custom Tables for Volatile Data: Your point about transients is well taken. We will migrate our geolocation cache from the wp_options table to a dedicated custom table. This will completely isolate this high-volume data from the critical options table, preventing the kind of database bloat you described. Your suggestion of using SQLite is still on our radar as a potential enhancement for this.
You’ve provided the kind of expert feedback that is rare and incredibly valuable. It’s clear you have extensive experience with high-performance environments. Thank you for pushing us to think bigger and build better.
All the best,
IniLermHi Jaro (@pixtweaks),
I wanted to follow up and say a huge thank you once again for your expert feedback a while back. Your insights on performance and scalability for high-traffic sites were invaluable and directly shaped our development priorities.
We’ve just released version 8.5.5, which we’ve internally called the “Performance & Scalability Update,” and it addresses the core issues you raised.
Based on your suggestions, we’ve implemented:
- High-Performance Local Geolocation: Users can now switch to an offline MaxMind GeoLite2 database. This completely eliminates external API calls for IP lookups, providing instant, reliable geolocation data and significantly improving TTFB. This is our new recommended solution for high-traffic websites.
- Dedicated Cache Table: We’ve moved all high-frequency data, like the geolocation cache and rate-limiting counters, out of the wp_options table and into a dedicated custom table (_advaipbl_cache). This prevents database bloat and ensures the plugin’s performance remains stable under heavy load, solving the exact transient flooding problem you described.
Your feedback was the catalyst for these significant architectural improvements. We took your advice to heart because you were absolutely right—a security plugin must be built to withstand edge cases and scale effectively.
If you ever have a moment to look at the new version, we’d be honored to hear your thoughts.
Thanks again for helping us make the plugin better.
All the best,
IniLermHi, I’m glad I could help.
I briefly checked the last update, and yes, I see many other things that I would do differently. And I want to make clear that I’m not calming my way is the best way, only from my experience, this is how I would consider from my today’s experience and knowledge. Points are made by priority, implementing 1 will bring most benefit and so on.- On what hook is the blocking happening? My guess it’s when plugin run first time. Does it work? Yes! Is this most efficient? Nope. If you want to save as much as possible CPU time, you have to make the decision much sooner. Ideally, on .htaccess level, the second best is PHP prepend file, the third best on mu-plugins hook.
- SQL queries
2.1 I’ve seen SQL queries and I’ve seen overuse of the prepare function. Like you use to prepare for PHP function time(). There is zero chance a hacker would inject SQL statement into time(), if that’s possible, PHP has a BIG issue on the hands. The same thing applies to unnecessary escaping of translations with function like esc_html__().
2.2 SQL queries have ORDER and GROUP. These two are the most expensive together with OFFSET. Reevaluate them, try to find a way to get what you need from DB without using them
2.3 If a column has just a few words, like wp_advaipbl_logs has log_type and level, use for column ENUMs rather then text. It’s faster for SELECT and occupied less space. You can always add new into the table schema.
2.4 INDEX, make sure you have an index only on the column that you need for fast query for performing plugin essential functionality – assessing situation for blocking. When it’s for viewing data, cron clean up, reports and non essential functionality, time is not that important. So yes, your query might be a bit slower but it happening few times. But, not needing writing and maintaining indexes when writing could improve performance.
- Plugin’s code itself
3.1 In my opinion, the biggest WP problem related to back-end performance is useless code. People say “more plugins, less speed”. That’s partially true. It’s true when plugins are not written the day that would execute code only they need. Your plugin always required 32 files that occupies 1MB ram in OPcache. That means that on any page, it’s not only essential blocking code is loaded, all menus, everything has to be compiled and in OPcache. File with your class ADVAIPBL_Main has 9018 lines of code. If OPcache is not full, reading the whole file from disk and compile into OPcache is relativelly long process. Now add 20 more plugins doing the same thing, and you get the result of people saying “more plugins, less speed” and attributing this to WP shortfall instead of inappropriately developed plugins. So if you don’t want to be part of that band, you need to separate code branches. For example, Ajax code is required and executed only when YOURS Ajax request is made. Add to your admin-ajax.php?ip-blocker-something and require Ajax code if isset($_GET[’ip-blocker-something’]).
3.2 Code readiness.
Long functions, nesting of code, mixing PHP and HTML.
Classes should separate functionality, you call DB from many parts of the code. What about one class would perform all DB queries? Same for options. Please watch on youtube video “Clean Code – Uncle Bob / Lesson 1”3.3 I see in wp_options some has “auto” for autoload. Make sure only those settings has auto or yes that are really needed on every request, the rest should have ‘no’. And then I see as a value an empty array a:0:{}. No need to store default values, make it rather an empty string.
3.4 I see $_SERVER[‘REQUEST_URI’] 29 times in the code and all has ??’’ or ‘n/a’ Why not just once and reused?
All right, there is more, but I really don’t have more time now. What I gave you will keep you busy for a while. I hope it will help.
All the best,
JaroHi,
I’m marking this thread as resolved. Feel free to open a new topic if you have further questions.
Thanks.
You must be logged in to reply to this topic.
