Suggestion - Reduce inserts

[michelinok]

Hi! This is not an issue but a suggestion. Will you make some mods to the code to have less inserts? I don't know where the code is slow but it seems to me that it takes ages because you do an insert for each qso. It would be a great improvement if you can modify to to have less "insert into" but with a lot of qsos, something like 100 qso would improve a lot.

It's JUST an IDEA.

Two years ago uploading my log required 30 seconds...now (doubled my qsos!!) it require 15 minutes. Don't know how much code changed in these 2 years. PS: I changed my pc...it's much more powerfull that 2 years ago....it's not a pc problem

[jxmx]

That seems very strange and unexpected. I don't believe there's been any material change to the SQL parts and I don't experience that. Can you send me an ADIF file for testing?

[michelinok]

Here's my log

mylog.adi.txt

[jxmx]

@michelinok Loading mylog_adi.txt on my test system took about 2 seconds. There were 7810 contacts in that log and SmoothQSL churned through them like nothing. This is running on a 2CPU development VM with 2Gb of RAM. No MariaDB tuning has been done - it's configuration is "out of the box" Debian 12.

Can you tell me more about your setup?

[michelinok]

Wow.... My config is Intel i5 6gen, wampserver (no tuning at all),24gb ram (almost free...). No idea where the problem can be. I've noticed that my CPU 'sleep' around 25% (almost used by apache). Do you have any suggestion?

Il Dom 24 Dic 2023, 14:33 Jason McCormick @.***> ha scritto:

@michelinok https://github.com/michelinok Loading mylog_adi.txt on my test system took about 2 seconds. There were 7810 contacts in that log and SmoothQSL churned through them like nothing. This is running on a 2CPU development VM with 2Gb of RAM. No MariaDB tuning has been done - it's configuration is "out of the box" Debian 12.

Can you tell me more about your setup?

— Reply to this email directly, view it on GitHub https://github.com/jxmx/smooth-qsl/issues/15#issuecomment-1868518444, or unsubscribe https://github.com/notifications/unsubscribe-auth/AANMKIM2ZKE2F4NWWTTG57LYLAVKPAVCNFSM6AAAAABAEF2GEOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNRYGUYTQNBUGQ . You are receiving this because you were mentioned.Message ID: @.***>

[jxmx]

To confirm, you are running Apache 2.4 on Windows? What version of PHP? How is Apache connecting to PHP - FCGI proxy or mod_php or something else?

Is there a long delay between the first and second load screen, second to third, or both?

[jxmx]

Also, something is wrong if Apache is “idle” at 25% CPU.

[michelinok]

1. Apache 2.4.6
2. No idea…how can I check?
3. Yes…I get some seconds delay between screen updates during the qso load (i get something LIKE 50 qso each scree screen updates)

I’ve tryed uploading the entire project on an external hosting and it’s much more faster (about 10 times faster).

I’ve no idea what’s wrong on my pc

Da: Jason McCormick @.> Inviato: domenica 24 dicembre 2023 14:56 A: jxmx/smooth-qsl @.> Cc: Mike IU5HES @.>; Mention @.> Oggetto: Re: [jxmx/smooth-qsl] Suggestion - Reduce inserts (Issue #15)

To confirm, you are running Apache 2.4 on Windows? What version of PHP? How is Apache connecting to PHP - FCGI proxy or mod_php or something else?

Is there a long delay between the first and second load screen, second to third, or both?

— Reply to this email directly, view it on GitHub https://github.com/jxmx/smooth-qsl/issues/15#issuecomment-1868522721 , or unsubscribe https://github.com/notifications/unsubscribe-auth/AANMKIPKW2BV7XCLIGK7VR3YLAYA3AVCNFSM6AAAAABAEF2GEOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNRYGUZDENZSGE . You are receiving this because you were mentioned. https://github.com/notifications/beacon/AANMKIPBBXEDUL4ML4YXZLLYLAYA3A5CNFSM6AAAAABAEF2GEOWGG33NNVSW45C7OR4XAZNMJFZXG5LFINXW23LFNZ2KUY3PNVWWK3TUL5UWJTTPL5SOC.gif Message ID: @. @.> >

[michelinok]

Also, bypassing strcleaner it runs much more faster (I was just trying to understand where is the bottleneck)

Da: Jason McCormick @.> Inviato: domenica 24 dicembre 2023 14:56 A: jxmx/smooth-qsl @.> Cc: Mike IU5HES @.>; Mention @.> Oggetto: Re: [jxmx/smooth-qsl] Suggestion - Reduce inserts (Issue #15)

To confirm, you are running Apache 2.4 on Windows? What version of PHP? How is Apache connecting to PHP - FCGI proxy or mod_php or something else?

Is there a long delay between the first and second load screen, second to third, or both?

— Reply to this email directly, view it on GitHub https://github.com/jxmx/smooth-qsl/issues/15#issuecomment-1868522721 , or unsubscribe https://github.com/notifications/unsubscribe-auth/AANMKIPKW2BV7XCLIGK7VR3YLAYA3AVCNFSM6AAAAABAEF2GEOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNRYGUZDENZSGE . You are receiving this because you were mentioned. https://github.com/notifications/beacon/AANMKIPBBXEDUL4ML4YXZLLYLAYA3A5CNFSM6AAAAABAEF2GEOWGG33NNVSW45C7OR4XAZNMJFZXG5LFINXW23LFNZ2KUY3PNVWWK3TUL5UWJTTPL5SOC.gif Message ID: @. @.> >

[michelinok]

I've tryed disabling entire antivirus, disabling xdebug,disabling cgi_module and using 127.0.0.1 instead of localhost. I've no more idea. It seems that qsos are displayed in "batch" of 15/20 on the screen. The final "commit" is done in a fraction of second. I'll investigate

Apache/2.4.46 (Win64) PHP/7.3.21 mod_fcgid/2.3.10-dev - Port defined for Apache: 80 PHP Version: [Apache module] 7.3.21 [FCGI] 5.6.40 - 7.3.21 - 7.4.9 - 8.1.26 - 8.3.0 5.7.31 - Port defined for MySQL: 3306 - default DBMS

[jxmx]

I am not sure exactly the setup of "External hosting" but if it's on a shared hosting provider, I would expect loading those 7800s QSO in 10s of seconds - maybe 30 or so. Shared hosting providers are always vastly oversubscribed and inserts are the most expensive database transaction. That's why it's all wrapped in one transaction at the end.

Looking at the above are you by chance running the pages through PHP twice? From that server signature is sounds like you have mod_php running AND mod_fci running simultaneously. You definitely should be using one or the other, but not both although preferably proxy_fcgi (not mod_fcgi) is the most performant way to run PHP.

Also, PHP 7.3 is very very old. While I don't think it's a root cause, PHP 8.0+ does have significant performance improvements. Although it's likely been over 10 years since I've see Apache or PHP on a Windows host.

[jxmx]

@michelinok - If you pull down the latest code out of git, there is now a program load/qsladifloader-cli.php. This won't fully load QSOs, but it will tell you where the bottleneck on your system is. Run it from cmd or Powershell like so:

php qsladifloader-cli.php -c IU5HES -l Italy -f c:\path\to\mylog.adi.txt

You will get a timestamp output for how long processing each record took and then one timestamp for how long the transaction stage to the database took. For example:

2023-11-26 15:53        NR4M    28.0078 10m     CW      599     IU5HES
Record processed in 0.000035 s
2023-11-26 15:54        WG3J    28.0097 10m     CW      599     IU5HES
Record processed in 0.000035 s
2023-11-26 15:56        V26K    28.0615 10m     CW      599     IU5HES
Record processed in 0.000036 s
2023-11-26 15:57        N6SS    28.0939 10m     CW      599     IU5HES
Record processed in 0.000035 s
Database insert prepped in 0.041264 s

[jxmx]

Let me know what you get.

[michelinok]

Just a part of the log...

2017-08-09 10:08 DG5NET 14.0749 20m FT8 -07 IU5HES Record processed in 0.000212 s 2017-08-09 10:24 PD7RF 14.0749 20m FT8 -02 IU5HES Record processed in 0.000209 s 2017-08-09 10:35 S56ECR 14.0749 20m FT8 -01 IU5HES Record processed in 0.000212 s 2017-08-09 10:49 5B4AIF 14.0752 20m FT8 -15 IU5HES Record processed in 0.000241 s 2017-08-09 10:53 DH1BBH 14.0752 20m FT8 -10 IU5HES Record processed in 0.000208 s

I'll run entire log asap

[michelinok]

it seems it takes 10 times your speed processing each qso....

[jxmx]

Even 10x is meaningless at that scale. Thats taking 0.2ms per line in your ADIF. If I wasn’t using microseconds, my system and your system would both take 0ms to complete at that line. That means your bottleneck is in your webserver setup somehow.

[michelinok]

I've tryed from cli with different php version (thread safe and not thread safe). Same results...I'll try a live linux version asap (if you have any suggestion...). it's the latest hope...I hope it's not an hardware bottleneck :) Maybe my problem will help someone else, so please...don't close the ticked now. Many thanks, I'll let you know

[michelinok]

Can you share your php.ini ? Maybe on pastebin Many thanks

[michelinok]

Oh my god.....i've profiled your source (with xdebug) code and found what was cousing the problem to me.... For me it's the stripos inside adif_parser (inside get_record function) !!!! I've replaced stripos with strpos and... it's a rocket!!!! Since we already replace EOR with eor, it's not a problem at all to search for "eor". We can also replace Gotta modify my code now (i've deeply adapted your source for my project...i now include dxcc and flags). If you want, you can push the mod...I'm still not able to do "pull request"

Many thanks for your help!!!!!!!!!!!!!!!!!!

[jxmx]

So you are saying that replacing stripos with stripos reduces your web processing time from 15 minutes to seconds? That just doesn't make any sense. Stripos is an order of magnitude slower because of what it has to do, but it shouldn't be that bad. Most likely, the right thing to do is replace stripos/strpos with `preg_match.

Also when you say you "can't push" what do you mean? I don't see an open fork of SmoothQSL for you in Github. If you fork it and upload your code, you should be able to propose an upstream pull with no issue.

[michelinok]

My bad english.... I've tryied your "banchmark" against my adif. Before the mod I got 15 minutes, with strpos i get some seconds. I've read that strpos is much faster because it doesn't handle lowercase/upccase. Preg_match is the slowest.

When I say "can't push" I mean that I'm working on your code and haven't forked....need to study how "git" works. I'm still learning a lot of things, working on a little project (started from scratch an older project).

I swear I can handle my log in a couple of seconds instead of minutes. It would be a good idea if you can try the mod of your benchmark on your working machine. I have no words to thank you for your patience.

AGAIN...I'M A BEGINNER STILL LEARNING, so maybe I'm doing something wrong.

[jxmx]

If you want to attach a zipfile or tarball here of your code, I can take a look at it. Getting familiar with basic Git and Github would help in the long run though.

[jxmx]

Also, what is your local language on your system? I'm assuming Italian? I wonder if the performance problems are due to localization issues with Italian on Windows. I'll look at the ADIF code to see if there's some efficiencies.

[michelinok]

Yes, it's italian.

Il Lun 25 Dic 2023, 17:14 Jason McCormick @.***> ha scritto:

Also, what is your local language on your system? I'm assuming Italian? I wonder if the performance problems are due to localization issues with Italian on Windows. I'll look at the ADIF code to see if there's some efficiencies.

— Reply to this email directly, view it on GitHub https://github.com/jxmx/smooth-qsl/issues/15#issuecomment-1869042201, or unsubscribe https://github.com/notifications/unsubscribe-auth/AANMKIPFR4SW6LMNA5TOKZDYLGRABAVCNFSM6AAAAABAEF2GEOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNRZGA2DEMRQGE . You are receiving this because you were mentioned.Message ID: @.***>

[jxmx]

@michelinok - Try the updated adif_parser I just committed from source. I removed all of the stripos() reliance and unnecessary string manipulations. It increased by the per-record processing by 200% and cut the database prep time in half. Reading about stripos() in various PHP places, it sounds like it's a known poor performer in certain cases.

2023-11-26 15:53        NR4M    28.0078 10m     CW      599     IU5HES
Record processed in 0.000007 s
2023-11-26 15:54        WG3J    28.0097 10m     CW      599     IU5HES
Record processed in 0.000006 s
2023-11-26 15:56        V26K    28.0615 10m     CW      599     IU5HES
Record processed in 0.000007 s
2023-11-26 15:57        N6SS    28.0939 10m     CW      599     IU5HES
Record processed in 0.000007 s
Database insert prepped in 0.021563 s

[michelinok]

Hi, so great improvments, faster than my previous stripos/strpos mod. The database prep time is also very very faster now! Many thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[jxmx]

Fantastic! Upload your code to a different ticket and I'll check out your changes for inclusion in SmoothQSL.

[jxmx]

Fixed in release 2.1 https://github.com/jxmx/smooth-qsl/releases/tag/rel_2_1_0

[michelinok]

Fantastic! Upload your code to a different ticket and I'll check out your changes for inclusion in SmoothQSL.

YOU did the final job, i've only investigated where was my problem. Maybe your computer is so fast that you didn't noticed the "problem". The major improvment was changing stripos/strpos. I've learned also how to profile php code, that's fantastic!

You're a real good programmer with a big patience and I'll put credits to you as soon as the project is finished (I'm working on an permanent hf-award ...need to learn bootstrap too...)

[jxmx]

You're welcome. I'm just happy people use my code.