Today at about 1:00 pm Philadelphia time, the website went offline. It was still there, it just could not answer any queries. In this post: Website and database server running in AWS FreeBSD 12.2 on a t3.medium EC2 instance PostgreSQL 12.5 running on a db.t2.large RDS instance Initial look Looking at load, it was acceptable: […]
lack of index brings website to a crawl Read More »
This post is a how-to and reminder for myself. I’m working on finding a better way for FreshPorts to know a new physical ports category when it finds it. I think the only way is to look at the directories in the FreeBSD ports repo. A discussion on IRC led to this shell script: [dan@devgit-ingress01:/var/db/ingress/repos/ports]
Generating XML for a single commit Read More »
This post is related to a recent tweet. It was months ago that I wondered why one port installed another as a dependency. I could see no link. Since it FreshPorts ports, earlier this week I did some testing: I removed the package, and it’s own dependencies from dev, then test, and finally stage. Monitoring
Removing unneeded packages Read More »
This post is mostly for personal use as I investigate this topic. It may take a few days and I need to keep notes. Last week, FreshPorts went live with git commits. There are some outstanding issues, such as commits to branches, which I will catch up on in the weeks to come. During that
Removing ‘unused’ tables Read More »
Hello, The changeover from subversion to git for the FreeBSD project enters it’s final stage about three hours from now (as I type this). At 3:01 UTC on Mar 31 2021, the repository will become read-only. The full schedule for that process is in the FreeBSD wiki. The first git commit is not expected until
FreshPorts will disable user login/updates on March 31 as part of transition to git Read More »
I had noticed a problem with my full text search solution when I was testing. If the input query contained a quote, an error resulted on the query. At the time, I attributed that to a poor input/text handling. Later, I realized I was wrong. On the website, this PHP code is used: Where $this->Query
to_tsquery gives ‘ERROR: syntax error in tsquery’ when it contains a space Read More »
In yesterdays blog post, Replacing a column search with a full text search solution, I wanted to mention how adding a LIMIT 10 to a fast running query made it run slower. I didn’t remember that issue until this morning. Fortunately, the fix was low cost. The query This is the query I ran: The
A PostgreSQL query runs in 1.7s – add a LIMIT 10, runs in 28.2 seconds Read More »
One of the many useful features on FreshPorts is: what port[s] install[s] this file? That’s the pkg-plist search option. pkg-plist is a file which “lists all the files installed by the port”. However not all ports have a pkg-plist file because the list is compiled automatically. That is why the configure_plist table was created to
Replacing a column search with a full text search solution Read More »
In this post I will show you how I discovered that the backend was putting data into a new table (generate_plist) but the website was still using the ports.pkg_plist column. The result was stale data being shown on the website (for older ports) or no data at all (for newer ports). How long? Since Oct
Converting a column-based expression to a row based solution Read More »
This is a cheat sheet for FreshPorts behind-the-scenes administration. If you want one for users, please start with suggested content. Ingress Actions git-single-commit.sh – Take a commit from the repo and insert it into the database. Will fail if commit is already there. REPO is one of src, ports, doc. Hash is the git commit
FreshPorts cheat sheet Read More »