This topic contains 7 replies, has 2 voices, and was last updated by 
- Posts
Hi,
I’ve installed your plugin a few days and I must say congratulations. This is truly a great plugin and because of it, I saved a few days of work.
However, I often have 400-500 users connected at the same time on this website and there has been a dramatic increase in server load since I installed your plugin.
I have absolutely everything cached so no traffic is hitting the CPU unless there’a a special action being done.
Is there a ajax call on every page regardless of a search being done?
Because, my SQL query logger shows an obscene amount of query to « SHOW TABLES LIKE ‘wp_ajaxsearchpro’ ». 10-15 times a minutes. I find it weird because on Multisite, it should be wp_818181_ajaxsearchpro.
No hurry buddy, my server hangs on tight anyway, but I would like to reduce the overhead. At least, the « idle overhead ».
Regards,
Hi,
Thank you very much for your kind words, I really appreciate them!
I believe this might be something you are interested in: Documentation – Fine tuning the search performance
It covers most possibilities to improve the overall performance, by reducing the number of queries made to the database one way or another.There is no ajax call made individually, unless the auto populate feature is enabled. The queries are either actualy live searches, or the users arrive on non-cached pages or pages with expried cache (?), or perhaps the search results page, which is usually not cached entirely. With 400-500 active users at times, I can imagine the combination of these.
The ‘SHOW TABLES LIKE wp_ajaxsearchpro’ query is executed during the initialization process to check if the table exists before getting the instance data. The table name is correct, there is only one table for search instances shared across the multisite. The instance data is not stored in the options table for better performance, as it is faster to query the information from smaller tables – and the wp_options table can grow very fast to a big size.
I will take a look at the instance initialization class, as I believe there might be a possible optimization I could make for the upcoming update. Currently it fetches the instances even if the search shortcode is not present on the page during the initialization. I believe I can refactor it, simply to get the instances on-demand only (if possible). It won’t affect the performance much, especially in cases where the search shortcode is present on most pages, but it’s something I can do.
Best,
Ernest Marcinko
If you like my products, don't forget to rate them on codecanyon :)
Hi Ernest,
I really appreciate the effort you put in your response. God knows, it’s rare to have good support.
I think I have found what may cause a part of the overhead. I enabled statistics and in a few hours I gathered a lot of “No keyword” searches. I wonder what may cause that? Is it because it’s the first thing on my page and it might autofocus on some browser?
Also, concerning the “SHOW TABLE” thing. The problem is that, as a query it is not cachable and mysql does write a temp table to disk to produce the result. It would be much more efficient to set a flag with update_option once you found it and then use get_option to check if it has been created since those query will get cached either by a WordPress Caching Plugin, Redis/Varnish or MySQL.
Anyway, I’m still happy.
Have a nice day.
Hi,
Thank you for your kind words, I really appreciate them.
You wrore at the perfect moment, I was struggling to find a good tool to record/live feed all database queries on my test environment, as my current solution feels a bit of an issue to maintain correctly. Currently I’m using the phpMyAdmin statistics with full query log, which is not bad, as it allows selecting specific timeframes to analyze. As well as WordPress records all queries if enabled, so I tried a few plugins as well. If you have any good suggestions, please feel free to share it with me.
I did not know that about the “SHOW TABLES” query. I thought it delegates to the information schema table, and just queries the corresponding row there, but apparently not, my google searches confirm that. I spent so much time optimizing the search queries, I completely forgot about these. I think I can simply resolve this by your method, or just query the table contents without this check, the WordPress database wrapper should return an error in case of issues. This will be sorted within the next release for sure.
The [no-keyword] (empty phrase) record indicates searches where the keyword was not present:
– ..in cases where the user types in something, the deletes it. If the character count to trigger the search is set to 0 (by default it is 0), then the search triggers, and notes this as an empty phrase search. If you change the minimum character count to at least 1, this will go away. Depending on the language, I can suggest even 2-3 or more characters. It will reduce the server overhead even more.
– ..or when the phrase is not escapable and poses an SQL injection threat. Bots can do this, but there is a very slim chance.
– ..and sometimes it is due to a bug, that was recently discovered. A few versions back the statistics were changed, so it is separated from the search process. The search phrase is parsed again from the input then sent for statistics, after the search is done. Sometimes the users already delete the phrase or navigate to a result, so from time to time it gets an empty value.
Given your graph, it is most likely case 1 and sometimes case 3 combined.For even more optimization, you can off the search statistics alltogether. It faces a massive rework soon, as it is dated and needs a lot of improvement. As an alternative, I suggest using the google analytics integration option which also allows site search tracking. It has one downside, that the search queries are recorded as page views – but at least it does not use any server resources on your end.
Thank you for all your information, and I hope this helps.
Best,
Ernest Marcinko
If you like my products, don't forget to rate them on codecanyon :)
Hi Ernest,
Unfortunately, I don’t have THAT perfect tool to track queries we all wish to have. However, if that can help, I use two that you don’t seem to.
1) I enable slow query log that is built-in in MySQL. I set it to 1 seconds, so every query that is not lightning fast is recorded with details about disk usage.
2) I use a console app « mytop » (the little brother of the top command for MySQL) which shows what query is being run with the server load. Pretty simple, but if you check it for a while you get an idea of what is being queried too much.
I’ve set the trigger at 3 character. IMO, it gonna really help.
Also, I’ve figured why the SHOW TABLE sticks out so much, it’s because on my server it’s slow (over 2 seconds). I run a WordPress MultiSite where Quebec citizens can create free/paid blogs and over the years there’s just way way too many tables in that database.
Has it been fixed in the new version or do you plan to do so in the future? I might have to hack it otherwise and I don’t really want to 🙂
Thanks a lot,
Cheers!
Hi,
Thank you very much 🙂 I do actually test with slow query log enabled as well, it helps a lot. I recall using mytop as well with the other ‘top’ type server load console apps, it is indeed really great.
Yes, the most recent release (4.11.9) includes a fix for the SHOW tables issue as well. I have made a get_option bypass and wrapped it into the database wrapper to an exists() function call. It should be executed only once now initally.
Best,
Ernest Marcinko
If you like my products, don't forget to rate them on codecanyon :)
Thats perfect. Thanks again 🙂
You cannot access this content. Best,
Ernest Marcinko
If you like my products, don't forget to rate them on codecanyon :)
You must be logged in to reply to this topic.