[arch-general] Implement sql/sqlite database for pacman local database

[Allan McRae]

On 22/10/16 14:06, Alive 4ever wrote:
> On Sat, Oct 22, 2016 at 02:15:01AM +0800, Chi-Hsuan Yen via arch-general wrote:
>> On Sat, Oct 22, 2016 at 1:54 AM, Robin via arch-general <
>> arch-general at archlinux.org> wrote:
>>
>>> Hi,
>>>
>>>> I was curious why does 'pacman -Q' operations took longer than 'apt'
>>>> counterparts.
>>> Sounds interesting but I have a few question about how did you measure
>>> this and how big the difference is. (Shouldn't be that big). Would be great
>>> if you provide more information on the comparability of you systems and the
>>> tools you used for tracing.
>>> Maybe there are other reasons why it is slow on your installation ?
>>>
>>>> For long term pacman development road map, it would be better to use
>>>> single sql based database for tracking locally installed packages
>>>> instead of keeping directories of every installed packages.
>>> I am not sure if a sql based database would be a good solution if you
>>> where right. It adds much more complexity and also a dependencies on $SQL
>>> backend. For me as a semi-professional arch user this would be worse than a
>>> maybe "not that fast" package dB querying.
>>>
>>> Regards,
>>>  Robin
>>
>>
>> Sometimes I have a similar problem, too. When the system just boots up, or
>> I just exploits my disk (for example building Firefox), pacman-related
>> files are moved out of the disk cache, so it needs some time to read them
>> all from the disk. Here's a simple performance test:
>>
>> $ sudo -v && time pacman -Q linux && sudo sync && sudo sync && echo 3 |
>> sudo tee /proc/sys/vm/drop_caches && time pacman -Q linux
>> [sudo] password for yen:
>> linux 4.8.3-1
>> pacman --color=auto -Q linux  0.00s user 0.00s system 2% cpu 0.121 total
>> 3
>> linux 4.8.3-1
>> pacman --color=auto -Q linux  0.00s user 0.01s system 0% cpu 1.229 total
>>
>> The difference is more than 10 times. I use a 5-year-old HDD. I guess on
>> even older machines things are worse.
>>
>> Regards,
>>
>> Yen Chi Hsuan
> 
> My own test - before optimization, ``pacman -Qs linux`` took almost half
> a minute.
> $ time pacman -Qs linux
> real    0m26.716s
> user    0m0.063s
> sys     0m0.230s
> 
> After running ``pacman-optimize``, it runs instantly.
> $ time pacman -Qs linux
> real    0m0.048s
> user    0m0.030s
> sys     0m0.017s
> 
> The filesystem fragmentation can be felt more deeply on slower and older
> HDD.
> .

Isn't caching brilliant...