フィードアグリゲーター

WordPress and HTTPS-terminating proxies

planet PHP - 2019-03-09(土) 20:15:00

A blog I am writing for was looking for a new place to host their website. Since we have a nice cluster with Rancher up and running, I offered to host the site. It's WordPress, so PHP, so how hard could it be, right?

I spent quite a few hours migrating everything. The initial migration to Docker was not that hard. There is a great official WordPress image for Docker, which makes it extremely easy to set up a new WordPress site in Docker.

The next thing is handling file uploads. Using the do-spaces-sync plugin this was easily set up to use DigitalOcean Spaces. It took a while to upload all images from the old wp-content/uploads to Spaces, but once that was done, I had it working immediately after setting it up. So far, this whole migration was a breeze.

Until I flipped the switch on the DNS and pointed it to our new hosting. I immediately got caught in an infinite redirect loop, and I had no idea why. I've spent hours turning off plugins, turning them on again. Debugging everything, watching logs. I could not figure it out. In the headers I did find a header saying that the redirect came from WordPress:

X-Redirect-By: WordPress

Eventually, I tried explaining the problem in the #wordpress channel in the PHPNL slack and as I'm typing my explanation something dawns on me...

Our Rancher setup has a load balancer that terminates the HTTPS then forwards an internal request to the container using http. But in WordPress, I have configured the siteurl to be https://. So WordPress gets a request using http, figures out it should be using https, and redirects. This causes the infinite redirect loop!

Of course, I wasn't the first to encounter this problem. Once I know what the problem was, searching the Internet quickly gave me the solution. In Wordpress Codex of course. The only thing I needed to do was add a single line to my .htaccess file:

SetEnvIf X-Forwarded-Proto https HTTPS

Once I did that, rebuilt my containers and deployed them to Rancher, the problem was solved. All of a sudden, everything worked.

カテゴリー: php

Bruce Momjian: The High Value of Data

planet postgresql - 2019-03-09(土) 01:30:01

There was a time when every piece of software had to be purchased: operating systems, compilers, middleware, text editors. Those days are mostly gone, though there are a few holdouts (e.g., MS Windows, vertical applications). What happened is that open source software has come to dominate most uses, and software selection is rarely based on cost requirements.

One of the final holdouts for purchased software is databases. You might think that is because database software is complex, but so is the other software mentioned. The big difference is that while non-database software processes or stores user data in a simple or standard way, databases lock user data inside the database. This data locking is a requirement for fast, reliable, and concurrent data access, but it does place the database on a different monetary plane.

In any organization, it is really their data that is valuable, and because the database is so tightly coupled to that valuable data, database software becomes something that is worth significant investment. This explains why databases have resisted the open source commoditization that has happened to so much other purchased software. (Custom database applications tied to complex business logic has also slowed the migration.)

Continue Reading »

カテゴリー: postgresql

Michael Paquier: Postgres 12 highlight - Connection slots and WAL senders

planet postgresql - 2019-03-08(金) 16:28:51

The maximum number of connections a PostgreSQL can accept is configured using max_connections. When attempting to connect to a server already at full capacity, logically the server complains:

$ psql psql: FATAL: sorry, too many clients already

It is possible to define connection policies, for example at database level with CREATE DATABASE or ALTER DATABASE, and even have superuser-only connection slots using superuser_reserved_connections, so as a superuser has a reserved space to be able to perform some activities even with a server full.

When creating a connection for replication purposes, the connection is spawned under a special status with the context of a WAL sender which is in charge of the communication, and speaks the replication protocol, so as it is possible to do replication, to take base backups, etc. A lot of those tasks are important for availability. One problem however is that this connection uses a shared memory slot which is part of max_connections. Hence, it is possible to get into a case where an application bloats the connections, and it becomes impossible to connect with a replication connection. This can be rather bad for availability, because this could the creation of a new standby after a failover for example.

One way to counter that is to connect to the server for base backups and standbys with a superuser role. Still this is not completely right either as by design there can be replication roles, which allow a role to connect to a server in replication mode, without being a superuser. In this context, this is where the following commit of Postgres 12 becomes handy:

commit: ea92368cd1da1e290f9ab8efb7f60cb7598fc310 author: Michael Paquier <michael@paquier.xyz> date: Tue, 12 Feb 2019 10:07:56 +0900 Move max_wal_senders out of max_connections for connection slot handling Since its introduction, max_wal_senders is counted as part of max_connections when it comes to define how many connection slots can be used for replication connections with a WAL sender context. This can lead t[...]
カテゴリー: postgresql

Viorel Tabara: Benchmarking Managed PostgreSQL Cloud Solutions - Part One: Amazon Aurora

planet postgresql - 2019-03-08(金) 07:37:18

This blog starts a multi-series documenting my journey on benchmarking PostgreSQL in the cloud.

The first part includes an overview of benchmarking tools, and kickstarts the fun with Amazon Aurora PostgreSQL.

Selecting the PostgreSQL Cloud Services Providers

A while ago I came across the AWS benchmark procedure for Aurora, and thought it would be really cool if I could take that test and run it on other cloud hosting providers. To Amazon’s credit, out of the three most known utility computing providers — AWS, Google, and Microsoft — AWS is the only major contributor to PostgreSQL development, and the first to offer managed PostgreSQL service (dating back in November 2013).

While managed PostgreSQL services are also available from a plethora of PostgreSQL Hosting Providers, I wanted to focus on the said three cloud computing providers since their environments are where many organizations looking for the advantages of cloud computing choose to run their applications, provided that they have the required know-how on managing PostgreSQL. I am a firm believer that in today’s IT landscape, organizations working with critical workloads in the cloud would greatly benefit from the services of a specialized PostgreSQL service provider, that can help them navigate the complex world of GUCS and myriads of SlideShare presentations.

Selecting the Right Benchmark Tool

Benchmarking PostgreSQL comes up quite often on performance mailing list, and as stressed countless of times the tests are not intended to validate a configuration for a real life application. However, selecting the right benchmark tool and parameters are important in order to gather meaningful results. I would expect every cloud provider to provide procedures for benchmarking their services, especially when the first cloud experience may not start on the right foot. The good news is that two of the three players in this test, have included benchmarks in their documentation. The AWS Benchmark Procedure for Aurora guide is easy to find, available right on

[...]
カテゴリー: postgresql

Bruce Momjian: Tool Vendor/Support Options

planet postgresql - 2019-03-08(金) 00:15:02

Having explained that lock-in is not a binary option, what are the Postgres tool support options available, at a high level?

  • Develop in-house database tools and support them yourself
  • Use open source tools and support them yourself
  • Use open source tools with vendor support (hopefully the vendor supports your chosen tools)
  • Use closed-source tools with vendor support

Of course, you can mix and match these options, i.e., use a support vendor for the open source tools they support, use other open source tools they don't support, and use some tools you develop in-house, e.g.:

  • open source Postgres database (vendor support)
  • pgBackRest for backup (vendor support)
  • patroni for failover (community support channels)
  • In-house developed tools (self support)

I went over these options in more detail in this presentation. This diversity of options is rarely available for closed-source, single-vendor database solutions.

カテゴリー: postgresql

New domain

planet PHP - 2019-03-07(木) 23:10:00

I've had the domain leftontheweb.com for ages. It's been with me since 2004. However, since I recently got a brand new .dev domain, I decided it was time for a change. Since I can't even remember how I came up with the old name, it's time for a change. A new name that is easy to recognize, easy to remember and easy to link to me.

The new domain name for this blog is:

skoop.dev

It only makes sense to switch to this domain. Skoop has been my nickname for as long as I have access to the Internet. And since my main occupation is still development, this switch makes sense.

Now, to find interesting topics to blog about again...

カテゴリー: php

PHP 7.1.27 Released

php.net - 2019-03-07(木) 20:49:58
カテゴリー: php

PHP 7.2.16 Released

php.net - 2019-03-07(木) 19:08:52
カテゴリー: php

PHP 7.3.3 Released

php.net - 2019-03-07(木) 18:10:40
カテゴリー: php

Laurenz Albe: “LOCK TABLE” can harm your database’s health

planet postgresql - 2019-03-07(木) 18:00:17
© Laurenz Albe 2019

 

Many people know that explicit table locks with LOCK TABLE are bad style and usually a consequence of bad design. The main reason is that they hamper concurrency and hence performance.

Through a recent support case I learned that there are even worse effects of explicit table locks.

Table locks

Before an SQL statement uses a table, it takes the appropriate table lock. This prevents concurrent use that would conflict with its operation. For example, reading from a table will take a ACCESS SHARE lock which will conflict with the ACCESS EXCLUSIVE lock that TRUNCATE needs.

You can find a description of the individual lock levels in the documentation. There is also the matrix that shows which lock levels conflict with each other.

You don’t have to perform these table locks explicitly, PostgreSQL does it for you automatically.

Explicit table locks with the LOCK TABLE statement

You can also explicitly request locks on a table with the LOCK statement:

LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]

There are some cases where it is useful and indicated to use such an explicit table lock. One example is a bulk update of a table, where you want to avoid deadlocks with other transactions that modify the table at the same time. In that case you would use a SHARE lock on the table that prevents concurrent data modifications:

LOCK atable IN SHARE MODE; Typical mistakes with LOCK TABLE

Unfortunately most people don’t think hard enough and just use “LOCK atable” without thinking that the default lock mode is ACCESS EXCLUSIVE, which blocks all concurrent access to the table, even read access. This harms performance more than necessary.

But most of the time, tables are locked because developers don’t know that there are less restrictive ways to achieve what they want:

  • You don’t want concurrent transactions to modify a row between the time you read it and the time you update it? Use SELECT ... FOR UPDATE!
    If concurrent modifications are unlikely and you are not sure that you ar
[...]
カテゴリー: postgresql

PHP 7.2.16 Released

planet PHP - 2019-03-07(木) 09:00:00
The PHP development team announces the immediate availability of PHP 7.2.16. This is a security release which also contains several minor bug fixes.All PHP 7.2 users are encouraged to upgrade to this version.For source downloads of PHP 7.2.16 please visit our downloads page, Windows source and binaries can be found on windows.php.net/download/. The list of changes is recorded in the ChangeLog.
カテゴリー: php

PHP 7.1.27 Released

planet PHP - 2019-03-07(木) 09:00:00
The PHP development team announces the immediate availability of PHP 7.1.27. This is a security release which also contains several bug fixes.All PHP 7.1 users are encouraged to upgrade to this version.For source downloads of PHP 7.1.27 please visit our downloads page, Windows source and binaries can be found on windows.php.net/download/. The list of changes is recorded in the ChangeLog.
カテゴリー: php

PHP 7.3.3 Released

planet PHP - 2019-03-07(木) 09:00:00
The PHP development team announces the immediate availability of PHP 7.3.3. This is a security release which also contains several bug fixes.All PHP 7.3 users are encouraged to upgrade to this version.For source downloads of PHP 7.3.3 please visit our downloads page, Windows source and binaries can be found on windows.php.net/download/. The list of changes is recorded in the ChangeLog.
カテゴリー: php

Mark Wong: PDXPUG: March Meetup: Let’s talk databases in python!

planet postgresql - 2019-03-06(水) 23:09:45

2019 March 21 Meeting 6pm-8pm

Location:

PSU Business Accelerator
2828 SW Corbett Ave · Portland, OR
Parking is open after 5pm.

Speaker: Hannah Stepanek

Let’s talk databases in python! What’s an ORM? Is there a way to write database queries so that they are compatible with multiple types of databases? How do you make database changes (such as adding a new table or a new column) safely? What is a connection pool and why is it useful? What are some things that can go wrong when operating at scale? In this talk we’ll take a deep dive into how the python libraries sqlalchemy and alembic make managing production databases simple, efficient, and painless so you can get back to feature development. Hannah has been working in industry for over 6 years as a python software engineer. She currently works at Hypothesis, a web application for annotating web pages and pdfs. In her spare time she enjoys riding her horse Sophie and playing board games.
カテゴリー: postgresql

Bruce Momjian: SQL Replay for Replication?

planet postgresql - 2019-03-06(水) 22:45:01

Postgres has had streaming (binary) replication for a long time, and logical (row shipping) replication since Postgres 10. Implementing these was a lot of work, and they work well.

However, the simplest way to do replication is often considered to be replaying SQL queries on standbys. The primary was modified by SQL queries, so isn't the simplest way to replicate replaying SQL? A novice would think so, and many database server developers initially try replication by replaying SQL. It seems simple because SQL queries are more concise than per-row changes. Imagine a DELETE that affects one million rows being shipped to a standby as a single SQL query. The conciseness and simplicity of SQL replication looks promising.

However, if you try implementing replication via SQL, you will realize that SQL runs in a multi-user environment. SQL commands do not contain enough information to replay queries the exact same way on standbys as the primary. Concurrent DML, volatile functions, sequence assignment, locks, and cursor contents can all cause inconsistencies. Developers have tried patching over these issues, but eventually the fundamental limitations of this approach become clear. I doubt Postgres will ever implement SQL-level replication for this reason.

カテゴリー: postgresql

Ibrar Ahmed: Settling the Myth of Transparent HugePages for Databases

planet postgresql - 2019-03-06(水) 22:07:25

The concept of Linux HugePages has existed for quite a while: for more than 10 years, introduced to Debian in 2007 with kernel version 2.6.23. Whilst a smaller page size is useful for general use, some memory intensive applications may gain performance by using bigger memory pages. By having bigger memory chunks available to them, they can reduce lookup time as well as improve the performance of read/write operations. To be able to make use of HugePages, applications need to carry the specific code directive, and changing applications across the board is not necessarily a simple task. So enter Transparent HugePages (THP).

By reputation, THPs are said to have a negative impact on performance. For this post, I set out to either prove or debunk the case for the use of THPs for database applications.

The Linux context

On Linux – and for that matter all operating systems that I know of – memory is divided into small chunks called pages. A typical memory page size is set to 4k. You can obtain the value of page size on Linux using getconf.

# getconf PAGE_SIZE 4096

Generally, the latest processors support multiple page sizes. However, Linux defaults to a minimal 4k page size. For a system with 64GB physical memory, this memory will be divided into more than 16 million pages. Linking between these pages and physical memory (which is called page table walking) is undertaken by the CPU’s memory management unit (MMU). To optimize page lookup, CPU maintains a cache of recently used pages called the Table Lookaside Buffer (TLB). The higher the number of pages, the lower the percentage of pages that are maintained in TLB. This translates to a higher cache miss ratio. With every cache miss, a more expensive search must be done via page table walking. In effect, that leads to a degradation in performance.

So what if we could increase the page size? We could then reduce the number of pages accessed, and reduce the cost of page walking. Cache hit ratio might then improve because more relevant data now fits in one page rather than

[...]
カテゴリー: postgresql

Interview with Jeremy Lindblom

planet PHP - 2019-03-06(水) 21:00:00

@jeremeamia Show Notes

Audio

This episode is sponsored by


The post Interview with Jeremy Lindblom appeared first on Voices of the ElePHPant.

カテゴリー: php

Alexander Sosna: Antivirus and database systems (PostgreSQL in focus)

planet postgresql - 2019-03-06(水) 19:00:00
Antivirus and database systems (PostgreSQL in focus) Many users are required to run virus scanners on all of their IT systems due to vendor requirements or working instructions. This general requirement can lead to problems for IT systems that are neither desktops nor simple file servers. Usual interpretations of baseline security requirements demand... 06-03 Alexander Sosna
カテゴリー: postgresql

ページ