Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

Windows Perl with sqlite3

by miner7777 (Novice)
on Jan 17, 2023 at 06:09 UTC ( #11149626=perlquestion: print w/replies, xml ) Need Help??

miner7777 has asked for the wisdom of the Perl Monks concerning the following question:

I have several Windows machines running the same Perl (This is perl 5, version 24, subversion 2 (v5.24.2) built for MSWin32-x64-multi-thread, Activestate) for many years. Sqlite3 (3.36.0 2021-06-18 18:36:39) with a 4GB indexed database containing one table with about a million rows. On three PCs (Ryzen 3600 with 16GB RAM Win 10 Pro, Ryzen 2700X with 16GB RAM Win 10 Pro, Xeon 5660 with 12 GB RAM Win 7 Pro) I get 100,000 selects in about 10-15 seconds. On the 4th PC (AMD A8-7500 with 16GB RAM Win 7 Pro) it barely crawls. Maybe 100 selects in 10 minutes.

The code is identical all places. The DB is a separate copy on each machine. No errors or obvious clues about why it is sooo slow. Even on an older laptop it gets comparable performance.

I have examined the Windows logs and find nothing amiss. No device manager flags. Crystal Disk reads the system drive (SSD) at about 450MB/sec and the data drive (HD) at about 180MB/sec. procexp and procmon don't show anything obvious. taskmgr shows 25% CPU usage, and 13GB free memory. I have examined all the BIOS params and see none that need adjustment.

I have run out of ideas on how to troubleshoot this issue. Seems clear that the OS is throttling Perl, but I can't pinpoint it.

Thoughts? Ideas? Suggestions? Not critical, not commercial, but surprising given everything else Perl on Windows has been fast. I could move it to Debian, but would like to figure this one out.


Replies are listed 'Best First'.
Re: Windows Perl with sqlite3
by Corion (Patriarch) on Jan 17, 2023 at 09:33 UTC

    Some configuration of an SQLite database is stored in the database file. See the list of pragmata.

    An easy check whether the file itself is problematic would be to copy a file from a "fast" machine to the "slow" machine and see if that makes a difference.

    If it does, either that is enough, or you can look at whether the pragmata cache_size, fullfsync or journal_mode make a difference.

      That did it. Apparently the DB file was corrupted in some way but threw no errors. re-copied the original DB file to the local machine and now it flies! 100,000 selects in 11.6 seconds. Pretty good for an old machine.

      Thanks everyone!

Re: Windows Perl with sqlite3
by Discipulus (Canon) on Jan 17, 2023 at 09:24 UTC
    Hello miner7777 and welcome to the monastery and to the wonderful world of perl!

    another test you can try is to time sql queries on machine 1 and 4 directly from the sqlite command line utility.

    The DB is relatively small, but did you checked disks speed and or disk problem?


    There are no rules, there are no thumbs..
    Reinvent the wheel, then learn The Wheel; may be one day you reinvent one of THE WHEELS.
Re: Windows Perl with sqlite3
by marto (Cardinal) on Jan 17, 2023 at 13:04 UTC

    Comparing two of the CPUs you mention there's significant single thread performance uplift, it's worth noting that the older part has significantly less CPU cache available, and the caching mechanisms are different, understandably. That said, once you've address some of the other points listed in the thread the sysinternals suite from Microsoft provides process monitor (procmon) and process explorer (procexp) which should help you in debugging.

Re: Windows Perl with sqlite3
by kcott (Archbishop) on Jan 17, 2023 at 06:32 UTC
Re: Windows Perl with sqlite3
by cavac (Vicar) on Jan 17, 2023 at 14:26 UTC

    A few things come to mind.

    Basically, every time you do anything (select, insert, update), SQLite (just as any other database) rummages around in the datafile(s). Depending on how your filesystem is configured it can cause one or more writes to disk, because it may be updating the "last access" time of those files.

    SQLite can also cause writes during read operation, because it supports concurrency as far as i remember. So it would need to write some lock information to disk. Here, it also depends on your file system setup as well as your hardware if these things get buffered in RAM with a delayed write or if the SQLite process is essentially blocked until the system can complete the write operation.

    If you use DBD::SQlite, you can try to run everything using the filename ":memory:" to run an in-memory database. This way you can check if the problem is File IO or if this is a processor-optimization-gone-wrong kind of situation.

    Write operations are especially "special" if you use some kind of RAID controller or a software RAID. There are all kinds of checksum-generation plus the whole business of having to send everything to multiple disks and waiting for them to finish.

    Lastly, there may or may not be a patched security vulnerability with your AMD CPU on the slow system. There were a lot of these since your processor was designed (in 2016 if Google is correct). Meltdown, Spectre, Hertzbleed, etc. Most of the fixes by the manufacturers seem to be to disable all the features that made those processors fast. And if your copy of Perl or SQLite uses one of them (which now needs to be emulated in some other way by the processor or operating system), this can slow things down by a lot. That is another thing where testing with an in-memory database can really help, because it allows you to distinguish between IO-wait and data processing.

    PerlMonks XP is useless? Not anymore: XPD - Do more with your PerlMonks XP
Re: Windows Perl with sqlite3
by stonux (Initiate) on Jan 17, 2023 at 12:38 UTC
    As already mentioned, observe your RAM. If the slow machine is swapping, that kills performance. Since you are talking of SELECTs per second, its not a COMMIT issue. The only additional hint that comes to my mind is encoding. Make sure everything is utf-8. I suppose you have the same INDEX on all systems.
Re: Windows Perl with sqlite3
by tangent (Parson) on Jan 17, 2023 at 14:24 UTC
    a 4GB indexed database containing one table with about a million rows
    Is it possible the hard drive on the problem machine is formatted using a FAT/FAT32 partition? If so you may be coming up against the 4GB file size limit.
Re: Windows Perl with sqlite3
by bliako (Monsignor) on Jan 17, 2023 at 18:34 UTC

    You realise you are talking about a ((100000/10)-(100/(60x10)))/(100/(60*10)) = 60,000 times performance difference.

      yes, but it feels worse than that. :-)

      I ran it overnight and it only did 7500 selects in something like 10 hours. So something is horribly wrong, although "sfc /scannow" says everything is hunky dory.

      This is a read-only DB and is a simple copy from the original. All nonessential services have been disabled on Win (eg. search and more). I did look at procexp and procmon before posting.

      I have an identical machine with Win 10 Pro installed and will test there. Also may test read/write speeds of csv files to see if there are obvious disk issues that Crystal disk can't see.

      Big thanks to everyone for your help!

        Did you verify that the disk is indeed formatted with NTFS and not FAT32? Win 7 Disk Management can tell you that.

        Don't know about Crystal disk benchmarks. I would try something simple, how fast can the DB be read?
        >copy yourDBname.sqlite NULL on Windows, NULL is a reserved file name that means "the bit bucket".
        See if there is any difference between the computers...

        Perhaps some experimentation to see what kind of cliff you are hitting might be interesting...Make a DB 1/10 the current size and benchmark that - see if the 400 MB file compares ok against the other machines or not.

Re: Windows Perl with sqlite3
by stevieb (Canon) on Jan 17, 2023 at 06:35 UTC
    Thoughts? Ideas? Suggestions?

    Yes. Don't make any major changes until our qualified folk have given feedback.

    You've an excellent question here, well defined and laid out, and I'm confident it'll be sorted.


Re: Windows Perl with sqlite3
by Anonymous Monk on Jan 17, 2023 at 10:41 UTC
    Did you check the ram?? Memtest or whatnot
Re: Windows Perl with sqlite3
by harangzsolt33 (Hermit) on Jan 17, 2023 at 12:18 UTC
    That could be caused by a lot of things. Perhaps services running which should not be running or background processes or updates or an antivirus that is too slow...just to name a few things which aren't hardware related. Open Task Manager... Then open SERVICES.MSC and MSCONFIG to see a list of startup programs. Several things I always disable because they are a huge performance draws are: Windows Search service, Workstation, Server, TCP/IP NetBIOS, Windows Error Reporting. Then in MSCONFIG >> Startup, the list should be empty. If you have a bunch of startup applications, then no wonder it's slow...

    There are two programs called XSETUP and Starter, which are tweaking tools that I use to change certain startup settings. I think, these can be downloaded for free. Starter is a tiny program that is worth installing. It shows a comprehensive list of user apps that start everytime Windows starts: XSETUP is a bigger program and allows a lot more than just performance tweaking:

      "If you have a bunch of startup applications, then no wonder it's slow..."

      OP is talking about program execution performance, there's nothing to suggest that windows start up time is a problem (on a 'modern PC', it shouldn't be). The tools linked to seem to be for operating systems that have past End of life.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11149626]
Approved by Athanasius
Front-paged by stevieb
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (1)
As of 2023-06-04 21:10 GMT
Find Nodes?
    Voting Booth?
    How often do you go to conferences?

    Results (22 votes). Check out past polls.