performance – NOTgreSQL NOT IN table slow query

I have a big table with millions of lines. Each line has a table field tags. I also have the right GIN index on tags.

Count lines that have a tag is fast (~ 7s):

SELECT COUNT(*) FROM "subscriptions" WHERE (tags @> ARRAY('t1')::varchar());

However, counting lines that do not have a tag is extremely slow (~ 70s):

SELECT COUNT(*) FROM "subscriptions" WHERE NOT (tags @> ARRAY('t1')::varchar());

I've also tried other variants, but with the same results (~ 70 years old):

SELECT COUNT(*) FROM "subscriptions" WHERE NOT ('t1' = ANY (tags));

How can I quickly perform the operation "not in the table"?

VPS SSD Windows or Linux High Performance at $ 6

VPS SSD Windows or Linux high performance

AFFORDABLE. POWERFUL. RELIABLE. Discover our high-performance SSD virtual private servers.

features:

  • POWERFUL CPU: Our servers operate at over 2.6 GHz and can even reach 4.2 GHz!
  • SEMICONDUCTOR READERS: Difficulties with Disk Speed? No problem! You can now enjoy more speed and reliability with our servers equipped only with SSDs.
  • SOLID NETWORK: Want a fast network? Our network is systematically running at 1/10 Gbps and offers seamless connectivity to major ISPs.

Available locations: we currently have servers available in Germany and Finland
customs:

  • SEO tools: Scrape Box, GSA SER, XRumer, Majestic, Semrush, Ahrefs, MOZ, etc.
  • Bots: account creators, MassPlaner, Jarvee, FollowLiker, Ubot, Traffic Bots, Imacros, etc.
  • Website traffic: IPTS and Jingling are also allowed
  • Web Hosting: Websites, Landing Pages, etc.

Price:

Our basic VPS package starts at $ 6 a month and includes:

1 central unit
2 GB of RAM
20GB SSD
Windows Server 2012 R2, 2016 or Linux

For more power and resources, please check out the concert extras below!

.

performance – Speed ​​up the python program to calculate the shortest paths more than 100 times

This code introduces a weighted edgelist, positive and negative sentiment keywords, as well as a target word. The program calculates the sum of the weights on the shortest paths from the initial words to the target and from the target to the initial words, because it generates 9 output values.

The program is very slow. Running large edgelist files takes days, rather than minutes or seconds.

How can this program be accelerated?

from tkinter import Tk, X, Y, TOP, BOTTOM, LEFT, RIGHT, BOTH, END
from tkinter import filedialog, messagebox
from tkinter.ttk import Frame, Button, Entry, Label, Progressbar
import os, glob, time
import pandas as pd

root = Tk()
root.geometry("600x400+300+300")

def read_edge_list(filename):
    edges = {}
    words = set()

    with open(filename) as fp:
        lines = fp.readlines()

        for line in lines:
            token = line.split()

            if len(token) != 3:
                continue

            word1 = token(0)
            word2 = token(1)
            freq = token(2)

            words = words | {word1, word2}

            if not word1 in edges.keys():
                edges(word1) = {}

            if not word2 in edges(word1):
                edges(word1)(word2) = {}

            edges(word1)(word2) = freq

    return edges, words

def read_sentiment(filename):
    with open(filename, encoding='utf-8-sig') as fp:
        lines = fp.readlines()

    words = {line.strip() for line in lines}

    return words

def read_target_word():
    word = input("Please input target word: ")
    return word

def run_shortest_path_algorithm(edges, positive, negative, target):
    positivedict = {}
    negativedict = {}

    for source in positive:
        dist1 = dijkstra(edges, source, target)
        dist2 = dijkstra(edges, target, source)
        if dist1 and dist2:
            positivedict(source) = dist1 + dist2

    for source in negative:
        dist1 = dijkstra(edges, source, target)
        dist2 = dijkstra(edges, target, source)
        if dist1 and dist2:
            negativedict(source) = dist1 + dist2

    return positivedict, negativedict

def calculate_statistics_summary(positivedict, negativedict, 
positivewords, negativewords):
    numpositive = len(positivedict)
    numnegative = len(negativedict)

    actualnumpositive = len(positivewords)
    actualnumnegative = len(negativewords)

    sumpositive = sum(positivedict.values())
    sumnegative = sum(negativedict.values())

    if actualnumpositive == 0:
        s1 = 0
    else:
        s1 = sumpositive / actualnumpositive

    if actualnumnegative == 0:
        s2 = 0
    else:
        s2 = sumnegative / actualnumnegative

    if numnegative == 0:
        s3 = 0
    else:
        s3 = s1 * numpositive / numnegative

    if s2 == 0:
        s4 = 0
    else:
        s4 = s3 / s2

    if numpositive == 0:
        s5 = 0
    else:
        s5 = sumpositive / numpositive

    if numnegative == 0:
        s6 = 0
    else:
        s6 = sumnegative / numnegative

    if numnegative == 0:
        s7 = 0
    else:
        s7 = s5 * numpositive / numnegative

    if s6 == 0:
        s8 = 0
    else:
        s8 = s7 / s6

    s9 = s3 - s2

    return (s1, s2, s3, s4, s5, s6, s7, s8, s9)

def write_output_file():
    pass

def dijkstra(graph, start, end):
    shortest_paths = {start: (None, 0)}
    current_node = start
    visited = set()

    while current_node != end:
        visited.add(current_node)

        if current_node not in graph:
            destinations = ()
        else:
            destinations = graph(current_node).keys()

        weight_to_current_node = shortest_paths(current_node)(1)

        for next_node in destinations:
            weight = int(graph(current_node)(next_node)) + 
weight_to_current_node
            if next_node not in shortest_paths:
                shortest_paths(next_node) = (current_node, weight)
            else:
                current_shortest_weight = shortest_paths(next_node)(1)
                if current_shortest_weight > weight:
                    shortest_paths(next_node) = (current_node, weight)

        next_destinations = {node: shortest_paths(node) for node in 
shortest_paths if node not in visited}
        if not next_destinations:
            return None

        current_node = min(next_destinations, key=lambda k: 
next_destinations(k)(1))

    #path = ()
    #while current_node is not None:
        #path.append(current_node)
        #next_node = shortest_paths(current_node)(0)
        #current_node = next_node

    #path = path(::-1)
    #return path

    return shortest_paths(end)(1)

class SentimentWindow(Frame):
    def __init__(self):
        super().__init__()

        self.initUI()

        self.initPositiveDir = None
        self.initNegativeDir = None
        self.initSaveDir = None

        self.summary = pd.DataFrame(columns=('S1', 'S2', 'S3', 'S4', 
'S5', 'S6', 'S7', 'S8', 'S9'))

    def initUI(self):
        self.master.title("Sentiment")
        self.pack(fill=BOTH, expand=True, padx=15, pady=15)

        frmEdges = Frame(self)
        frmEdges.pack(fill=X, expand=True)

        lblEdges = Label(frmEdges, text="Select the directory of edge 
list.")
        lblEdges.pack(expand=True, fill=X, side=TOP, pady=2)

        frmEdgesPath = Frame(frmEdges)
        frmEdgesPath.pack(expand=True, fill=X, side=BOTTOM, pady=2)

        self.entEdgesPath = Entry(frmEdgesPath, width=60)
        self.entEdgesPath.pack(expand=True, fill=X, side=LEFT)

        btnEdgesPath = Button(frmEdgesPath, width=20, text="Load 
Edges", command=self.loadEdges)
        btnEdgesPath.pack(expand=True, side=RIGHT)

        frmPositive = Frame(self)
        frmPositive.pack(fill=X, expand=True)

        lblPositive = Label(frmPositive, text="Select the positive 
file.")
        lblPositive.pack(expand=True, fill=X, side=TOP, pady=2)

        frmPositivePath = Frame(frmPositive)
        frmPositivePath.pack(expand=True, fill=X, side=BOTTOM, pady=2)

        self.entPositivePath = Entry(frmPositivePath, width=60)
        self.entPositivePath.pack(expand=True, fill=X, side=LEFT)

        btnPositivePath = Button(frmPositivePath, width=20, text="Load 
Positive", command=self.loadPositive)
        btnPositivePath.pack(expand=True, side=RIGHT)

        frmNegative = Frame(self)
        frmNegative.pack(fill=X, expand=True)

        lblNegative = Label(frmNegative, text="Select the negative 
file.")
        lblNegative.pack(expand=True, fill=X, side=TOP, pady=2)

        frmNegativePath = Frame(frmNegative)
        frmNegativePath.pack(expand=True, fill=X, side=BOTTOM, pady=2)

        self.entNegativePath = Entry(frmNegativePath, width=60)
        self.entNegativePath.pack(expand=True, fill=X, side=LEFT)

        btnNegativePath = Button(frmNegativePath, width=20, text="Load 
Negative", command=self.loadNegative)
        btnNegativePath.pack(expand=True, side=RIGHT)

        frmTarget = Frame(self)
        frmTarget.pack(fill=X, expand=True)

        lblTarget = Label(frmTarget, text="Input the target word.")
        lblTarget.pack(expand=True, fill=X, side=TOP, pady=2)

        self.entTarget = Entry(frmTarget)
        self.entTarget.pack(fill=X, expand=True, pady=2)

        frmRun = Frame(self)
        frmRun.pack(fill=X, expand=True, pady=20)

        self.proRun = Progressbar(frmRun, value=0)
        self.proRun.pack(fill=X, expand=True, side=LEFT)

        btnRun = Button(frmRun, text = "Run", width=20, 
command=self.run)
        btnRun.pack(side=RIGHT, padx=20)


    def loadEdges(self):
        edgesFolderName = filedialog.askdirectory()

        if edgesFolderName:
            self.entEdgesPath.delete(0, END)
            self.entEdgesPath.insert(0, edgesFolderName)

    def loadPositive(self):
        if self.initPositiveDir is None:
            self.initPositiveDir = "https://codereview.stackexchange.com/"

        positiveFileName = 
filedialog.askopenfilename(initialdir=self.initPositiveDir, 
title="Open Positive File", filetypes=(("Text file", "*.txt"),))

        if positiveFileName:
            self.initPositiveDir = positiveFileName
            self.entPositivePath.delete(0, END)
            self.entPositivePath.insert(0, positiveFileName)

    def loadNegative(self):
        if self.initNegativeDir is None:
            self.initNegativeDir = "https://codereview.stackexchange.com/"

        negativeFileName = 
filedialog.askopenfilename(initialdir=self.initNegativeDir, 
title="Open Positive File", filetypes=(("Text file", "*.txt"),))

        if negativeFileName:
            self.initNegativeDir = negativeFileName
            self.entNegativePath.delete(0, END)
            self.entNegativePath.insert(0, negativeFileName)

    def run(self):
        edgesFolderName = self.entEdgesPath.get()
        if not os.path.isdir(edgesFolderName):
            messagebox.showerror("Invalid Path", "The directory of 
edge list is invalid.")
            return

        positiveFileName = self.entPositivePath.get()
        if not os.path.isfile(positiveFileName):
            messagebox.showerror("Invalid Path", "The positive 
filename is invalid.")
            return

        negativeFileName = self.entNegativePath.get()
        if not os.path.isfile(negativeFileName):
            messagebox.showerror("Invalid Path", "The negative 
filename is invalid.")
            return

        targetWord = self.entTarget.get()
        if targetWord is None or len(targetWord) <= 0:
            messagebox.showerror("No Target", "Please input the target 
word.")

        os.chdir(edgesFolderName)
        edgefiles = glob.glob("*.pr")

        if len(edgefiles) <= 0:
            messagebox.showerror("No Edge File", "Cannot find the edge 
files.")

        positivewords = read_sentiment(positiveFileName)

        negativewords = read_sentiment(negativeFileName)

        self.summary.drop(self.summary.index, inplace=True)
        self.proRun("value") = 0.0
        self.proRun.update()
        root.config(cursor="wait")
        root.update()
        time.sleep(0.300)

        for index, edgefile in enumerate(edgefiles):
            edges, words = read_edge_list(edgefile)

            if targetWord not in words:
                messagebox.showerror("Invalid Target", "Target does 
not exist in " + edgefile)
            else:
                possiblepositive = positivewords & words
                possiblenegative = negativewords & words

                positivedict, negativedict = 
                    run_shortest_path_algorithm(edges, 
possiblepositive, possiblenegative, targetWord)

                statistics_summary = 
calculate_statistics_summary(positivedict, negativedict, 
positivewords, negativewords)
                self.summary.loc(edgefile) = statistics_summary
            self.proRun("value") = 100 * (index + 1) / len(edgefiles)
            self.proRun.update()

        root.config(cursor="")    

        if self.summary.shape(0) > 0:
            self.summary.loc('mean') = self.summary.mean()
            self.summary.loc('std') = self.summary.std()

            if self.initSaveDir is None:
                self.initSaveDir = "https://codereview.stackexchange.com/"

            outputFile = 
filedialog.asksaveasfilename(initialdir=self.initSaveDir, title="Save 
Summary File", filetypes=(("Text file", "*.txt"),))
            self.initSaveDir = outputFile

            if outputFile:
                with open(outputFile, 'w') as outfp:
                    self.summary.to_string(outfp)

app = SentimentWindow()
root.mainloop()

Here are the little edgelists: https://drive.google.com/file/d/1zDOSMFz0AooXrs9WJ0noC3oD9cWg_562/view?usp=sharing

Here is a big file that will last for days! https://drive.google.com/file/d/18NR_bPjb9OU03n7MO08GwELrK7gqXEKE/view?usp=sharing

Here is the negative starter file: https://docs.google.com/document/d/1Y0eFolLWjqoHiFnHD7TOS-9z5h1xxmvUiENS1TEv9yU/edit?usp=sharing.

The positive starter file: https://docs.google.com/document/d/1FAct8O-rRN6qsdTU3praW6hy2ckMf1s1mA9K2gy7WYI/edit?usp=sharing

Set the target word to: bp.

Here is the code in a file: https://docs.google.com/document/d/1erSpyXxy3eMehBCiYJudf7tnQgIneT9H7Ot2_wGYBBI/edit?usp=sharing

performance query – NULL queries get blocked and use connections

Operation show processlist in phpmyadmin for me often shows queries with NULL information and a seemingly few seconds time, but that are actually stuck there for a long time (every time I run show processlist the same identifier may have a different time). I have no idea about the nature of these queries or why they are blocked, but they can often result in all of my available connections being used and waiting in Apache state, preventing them from loading. sites. Is there a parameter I can modify to end blocked queries for a long time? Is the php.ini parameter that defines max_execution_time responsible for this situation (I've set it to 3000 from the default value 90).

I've attached a screenshot of the race show processlist.

enter the description of the image here

network – Is it normal for the WMI Performance Adapter service to always start when launching the Chrome browser? what is the reason

Is it normal for the WMI Performance Adapter service (WmiApSrv.exe) to always start when launching the Chrome browser? What is the reason?

I heard that this service could be used maliciously to "communicate" via the Internet via open ports?

When I finally finish this process in the task manager, Chrome continues to work perfectly, without problems.

Could this WMI process be necessary because one of the Chrome features called "Conceal local IP addresses with mDNS host names"?

Thank you!

Do many failed connections from an unauthorized IP address affect SQL Server performance?

If access to a SQL server is allowed from any outside IP address and if the Windows event logs many (incorrect) connection attempts from IP addresses / unauthorized uses, influence there the performance of the SQL server?

For example, I've detected about 100 connection attempts over a period of one second and I really want to know if that affects performance (and how) before I put the list white of the IP addresses allowed to access the SQL server.

Profiling manager tasks for better performance

I am working on a project to intercept the purpose android.bluetooth.device.action.BATTERY_LEVEL_CHANGED with the help of tasker and shunter the battery level so that it will display in the form of a widget on my smartwatch. I plan to share this with the r / pebble sub-reseller community, but I'm facing a performance issue.

When a headset connects or its battery level changes, the watch will update in less than 3 seconds, which I consider acceptable. However, when the headset disconnects, the intention is immediately received, but the treatment by the manager takes 20 to 30 seconds, which is less responsive. that I would like.

I would like to determine what stage of the task takes so long; I've been considering adding instructions for writing timestamps into files to determine the responsible step or steps, but adding ~ 20 instructions, and then deleting them is tedious. Before I started, I thought I would check if Tasker had a native performance profile – unfortunately, googling for it is almost impossible (a set of terms that I can find means something other than the word optimization jargon). .

TL; DR – Does the tasker have native tools to diagnose slow-running tasks?

linux – Improved high performance and high average loads on a "Tube" site

I manage a small tube site with paid videos for engineers. I've noticed that my IOWait CPUs and my load averages are pretty high most of the time. I understand that it is normal for IOWait to be a bit too high for a tube site since the processor is waiting for those big, big videos on hold.

I am currently on VPS (for an upgrade):

1 vCPU core
4GB RAM
80GB SSD
512GB InfiniBand RDMA BlockStorage
CentMinMod modified LEMP stack

Here are some averages for the last month:

CPU usage: 15%
CPU WA: 63.3%
Memory used (minus buffers): 773M
Load average: 1.30
Number of active users per minute: 80

My query is what can be done to improve performance? To be honest, I do not see any noticeable lag, but I get more traffic from day to day, so I'd like to improve these indicators.

PS At first, I imagined that BlockStorage was the bottleneck, but I can see a 10ms lag in the logs and switching to a directly connected SSD slightly improved it, but the average load was still higher to 1.

unit – Occasional increase in highly optimized game performance (including profiler screen captures)

I have been working for a few years on a mobile platform program. I have spent more than a year optimizing as much as possible mobile gaming. Almost every one of my stitches uses the Mobile / Diffuse shader. When I test the performance of my game (on my phone) with the profiler, the frame rate remains clean at 60 fps for most. The CPU time varies between 16 ms and 17 ms. Rendering statistics are also excellent, with drawing calls ranging from 18 to 25, triangles between 1.0k and 2.0k, and vertices between 2.0k and 3.1k. Below is a screenshot of the Rendering Profiler when the game is working properly. I think for a mobile device, these numbers should be very good.

Statistics of Render profiler

However, from time to time, I see a peak in the profiler window (see below):

Occurrence of a peak

According to the profiler, the peak is caused by Render.OpaqueGeometry.

Render.OpaqueGeometry

In normal scenarios (when performance is fluid), Render.OpaqueGeometry takes between 1ms and 2ms of CPU time. However, as you can see in the screenshot above, Render.OpaqueGeometry takes 5.36 ms of CPU time and the total processor time is 24.16 ms.

According to my observations, this peak appears once or twice per minute. Sometimes, several minutes go by without any point. And on very rare occasions, the spike can appear 3 times in a minute (though I do not think it has happened the last two times I've tested it).

So, why do you think this happens even though the game is highly optimized? I've read in an article from Unity Answers that Render.OpaqueGeometry picks are common in all games and that there is nothing to worry about. But do you think 2 picks per minute are too common for a simple platform game?

optimization – dynamic programming algorithm with O (n) performance that will give the optimal solution

I am currently learning the dynamic substructure and the optimal solution for parts transformation. One of my teacher's questions is to describe a global O (n) dynamic programming algorithm that will give the optimal solution and why is it O (n)? Can someone help me understand the question? Thank you!