Обсуждение: Issue with refreshing materialized view from another system
We have a connection from a PostgreSQL server to a MySQL server which is used to update a materialized view on the PostgreSQL server from tables on the MySQL server (running CIVI-CRM, which may not be relevant.)
We are trying to move the PostgreSQL server to a new cloud server.
On the current production system, all the materialized views work, but the biggest of them can take about an hour.
On the new system, the smallest of the materialized views works, but the larger ones all seem to time out.
Could this be some kind of setting on PostgreSQL, like a memory or buffer issue?
Mike Nolan
On 2/26/26 1:59 PM, Michael Nolan wrote: > We have a connection from a PostgreSQL server to a MySQL server which is > used to update a materialized view on the PostgreSQL server from tables > on the MySQL server (running CIVI-CRM, which may not be relevant.) > > We are trying to move the PostgreSQL server to a new cloud server. > > On the current production system, all the materialized views work, but > the biggest of them can take about an hour. > > On the new system, the smallest of the materialized views works, but the > larger ones all seem to time out. "... move the PostgreSQL server to a new cloud server", where is that relative to the MySQL server compared to old Postgres server? > > Could this be some kind of setting on PostgreSQL, like a memory or > buffer issue? > > Mike Nolan > htfoot@gmail.com <mailto:htfoot@gmail.com> -- Adrian Klaver adrian.klaver@aklaver.com
My understanding is that the mysql server is at a Linode facility in PA, the current production postgres server is in Asheville NC, and the intended new server is a Azure server in the eastern US, not sure exactly where.
The two small matviews refresh, the two bigger ones fail, so it seems size-related, which is why I was wondering if the settings might make a difference because this server isn't fully production-scale yet. But weve transferred 175 GB files to it in about 6 hours so I think the net connection itself is probably not the issue.
Mike Nolan
On Thu, Feb 26, 2026 at 4:11 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 2/26/26 1:59 PM, Michael Nolan wrote:
> We have a connection from a PostgreSQL server to a MySQL server which is
> used to update a materialized view on the PostgreSQL server from tables
> on the MySQL server (running CIVI-CRM, which may not be relevant.)
>
> We are trying to move the PostgreSQL server to a new cloud server.
>
> On the current production system, all the materialized views work, but
> the biggest of them can take about an hour.
>
> On the new system, the smallest of the materialized views works, but the
> larger ones all seem to time out.
"... move the PostgreSQL server to a new cloud server", where is that
relative to the MySQL server compared to old Postgres server?
>
> Could this be some kind of setting on PostgreSQL, like a memory or
> buffer issue?
>
> Mike Nolan
> htfoot@gmail.com <mailto:htfoot@gmail.com>
--
Adrian Klaver
adrian.klaver@aklaver.com
Mike,
1. You're using mysql_fdw to connect?
2. What happens when you just run the raw SELECT statement (redirecting stdout to /dev/null, since we only need timings and error messages) from psql?
On Thu, Feb 26, 2026 at 6:37 PM Michael Nolan <htfoot@gmail.com> wrote:
My understanding is that the mysql server is at a Linode facility in PA, the current production postgres server is in Asheville NC, and the intended new server is a Azure server in the eastern US, not sure exactly where.The two small matviews refresh, the two bigger ones fail, so it seems size-related, which is why I was wondering if the settings might make a difference because this server isn't fully production-scale yet. But weve transferred 175 GB files to it in about 6 hours so I think the net connection itself is probably not the issue.Mike NolanOn Thu, Feb 26, 2026 at 4:11 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:On 2/26/26 1:59 PM, Michael Nolan wrote:
> We have a connection from a PostgreSQL server to a MySQL server which is
> used to update a materialized view on the PostgreSQL server from tables
> on the MySQL server (running CIVI-CRM, which may not be relevant.)
>
> We are trying to move the PostgreSQL server to a new cloud server.
>
> On the current production system, all the materialized views work, but
> the biggest of them can take about an hour.
>
> On the new system, the smallest of the materialized views works, but the
> larger ones all seem to time out.
"... move the PostgreSQL server to a new cloud server", where is that
relative to the MySQL server compared to old Postgres server?
>
> Could this be some kind of setting on PostgreSQL, like a memory or
> buffer issue?
>
> Mike Nolan
> htfoot@gmail.com <mailto:htfoot@gmail.com>
--
Adrian Klaver
adrian.klaver@aklaver.com
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
I can run select statements on the mysql server from the azure server, but the command we're using to refresh the matview is:
REFRESH MATERIALIZED VIEW memmast_ratings;
Mike Nolan
On Thu, Feb 26, 2026 at 5:45 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
Mike,1. You're using mysql_fdw to connect?2. What happens when you just run the raw SELECT statement (redirecting stdout to /dev/null, since we only need timings and error messages) from psql?On Thu, Feb 26, 2026 at 6:37 PM Michael Nolan <htfoot@gmail.com> wrote:My understanding is that the mysql server is at a Linode facility in PA, the current production postgres server is in Asheville NC, and the intended new server is a Azure server in the eastern US, not sure exactly where.The two small matviews refresh, the two bigger ones fail, so it seems size-related, which is why I was wondering if the settings might make a difference because this server isn't fully production-scale yet. But weve transferred 175 GB files to it in about 6 hours so I think the net connection itself is probably not the issue.Mike NolanOn Thu, Feb 26, 2026 at 4:11 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:On 2/26/26 1:59 PM, Michael Nolan wrote:
> We have a connection from a PostgreSQL server to a MySQL server which is
> used to update a materialized view on the PostgreSQL server from tables
> on the MySQL server (running CIVI-CRM, which may not be relevant.)
>
> We are trying to move the PostgreSQL server to a new cloud server.
>
> On the current production system, all the materialized views work, but
> the biggest of them can take about an hour.
>
> On the new system, the smallest of the materialized views works, but the
> larger ones all seem to time out.
"... move the PostgreSQL server to a new cloud server", where is that
relative to the MySQL server compared to old Postgres server?
>
> Could this be some kind of setting on PostgreSQL, like a memory or
> buffer issue?
>
> Mike Nolan
> htfoot@gmail.com <mailto:htfoot@gmail.com>
--
Adrian Klaver
adrian.klaver@aklaver.com--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
There has to be a view definition stored somewhere in the PG database catalog. Find it and run the SELECT statement.
That'll at least narrow down the problem (as well as letting you run EXPLAIN on it).
On Thu, Feb 26, 2026 at 6:48 PM Michael Nolan <htfoot@gmail.com> wrote:
I can run select statements on the mysql server from the azure server, but the command we're using to refresh the matview is:REFRESH MATERIALIZED VIEW memmast_ratings;Mike NolanOn Thu, Feb 26, 2026 at 5:45 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:Mike,1. You're using mysql_fdw to connect?2. What happens when you just run the raw SELECT statement (redirecting stdout to /dev/null, since we only need timings and error messages) from psql?On Thu, Feb 26, 2026 at 6:37 PM Michael Nolan <htfoot@gmail.com> wrote:My understanding is that the mysql server is at a Linode facility in PA, the current production postgres server is in Asheville NC, and the intended new server is a Azure server in the eastern US, not sure exactly where.The two small matviews refresh, the two bigger ones fail, so it seems size-related, which is why I was wondering if the settings might make a difference because this server isn't fully production-scale yet. But weve transferred 175 GB files to it in about 6 hours so I think the net connection itself is probably not the issue.Mike NolanOn Thu, Feb 26, 2026 at 4:11 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:On 2/26/26 1:59 PM, Michael Nolan wrote:
> We have a connection from a PostgreSQL server to a MySQL server which is
> used to update a materialized view on the PostgreSQL server from tables
> on the MySQL server (running CIVI-CRM, which may not be relevant.)
>
> We are trying to move the PostgreSQL server to a new cloud server.
>
> On the current production system, all the materialized views work, but
> the biggest of them can take about an hour.
>
> On the new system, the smallest of the materialized views works, but the
> larger ones all seem to time out.
"... move the PostgreSQL server to a new cloud server", where is that
relative to the MySQL server compared to old Postgres server?
>
> Could this be some kind of setting on PostgreSQL, like a memory or
> buffer issue?
>
> Mike Nolan
> htfoot@gmail.com <mailto:htfoot@gmail.com>
--
Adrian Klaver
adrian.klaver@aklaver.com--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
You mean this one:
CREATE MATERIALIZED VIEW public.memmast AS
SELECT memmast_simulant.civicrm_contact_id,
memmast_simulant.memid,
....
SELECT memmast_simulant.civicrm_contact_id,
memmast_simulant.memid,
....
FROM public.memmast_simulant
WITH NO DATA;
WITH NO DATA;
And here's the foreign table:
CREATE FOREIGN TABLE public.memmast_simulant (
civicrm_contact_id integer,
...
civicrm_contact_id integer,
...
SERVER mysql_civicrm
OPTIONS (
dbname 'skvare8_uscf_civicrm',
table_name 'memmast_simulant'
);
OPTIONS (
dbname 'skvare8_uscf_civicrm',
table_name 'memmast_simulant'
);
My understanding is that this view on mysql pulls fields from a lot of different mysql tables because that's how CIVI-CRM organizes data.
I can try running that with explain, but my understanding is that foreign tables can't be analyzed.
Mike Nolan
On Thu, Feb 26, 2026 at 6:01 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
There has to be a view definition stored somewhere in the PG database catalog. Find it and run the SELECT statement.That'll at least narrow down the problem (as well as letting you run EXPLAIN on it).On Thu, Feb 26, 2026 at 6:48 PM Michael Nolan <htfoot@gmail.com> wrote:I can run select statements on the mysql server from the azure server, but the command we're using to refresh the matview is:REFRESH MATERIALIZED VIEW memmast_ratings;Mike NolanOn Thu, Feb 26, 2026 at 5:45 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:Mike,1. You're using mysql_fdw to connect?2. What happens when you just run the raw SELECT statement (redirecting stdout to /dev/null, since we only need timings and error messages) from psql?On Thu, Feb 26, 2026 at 6:37 PM Michael Nolan <htfoot@gmail.com> wrote:My understanding is that the mysql server is at a Linode facility in PA, the current production postgres server is in Asheville NC, and the intended new server is a Azure server in the eastern US, not sure exactly where.The two small matviews refresh, the two bigger ones fail, so it seems size-related, which is why I was wondering if the settings might make a difference because this server isn't fully production-scale yet. But weve transferred 175 GB files to it in about 6 hours so I think the net connection itself is probably not the issue.Mike NolanOn Thu, Feb 26, 2026 at 4:11 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:On 2/26/26 1:59 PM, Michael Nolan wrote:
> We have a connection from a PostgreSQL server to a MySQL server which is
> used to update a materialized view on the PostgreSQL server from tables
> on the MySQL server (running CIVI-CRM, which may not be relevant.)
>
> We are trying to move the PostgreSQL server to a new cloud server.
>
> On the current production system, all the materialized views work, but
> the biggest of them can take about an hour.
>
> On the new system, the smallest of the materialized views works, but the
> larger ones all seem to time out.
"... move the PostgreSQL server to a new cloud server", where is that
relative to the MySQL server compared to old Postgres server?
>
> Could this be some kind of setting on PostgreSQL, like a memory or
> buffer issue?
>
> Mike Nolan
> htfoot@gmail.com <mailto:htfoot@gmail.com>
--
Adrian Klaver
adrian.klaver@aklaver.com--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
You can try and run the query, to see how long it takes.
Do that both from psql and mysql. That'll tell you something about where the problem is.
On Thu, Feb 26, 2026 at 7:09 PM Michael Nolan <htfoot@gmail.com> wrote:
You mean this one:CREATE MATERIALIZED VIEW public.memmast AS
SELECT memmast_simulant.civicrm_contact_id,
memmast_simulant.memid,
....FROM public.memmast_simulant
WITH NO DATA;And here's the foreign table:CREATE FOREIGN TABLE public.memmast_simulant (
civicrm_contact_id integer,
...SERVER mysql_civicrm
OPTIONS (
dbname 'skvare8_uscf_civicrm',
table_name 'memmast_simulant'
);My understanding is that this view on mysql pulls fields from a lot of different mysql tables because that's how CIVI-CRM organizes data.I can try running that with explain, but my understanding is that foreign tables can't be analyzed.Mike NolanOn Thu, Feb 26, 2026 at 6:01 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:There has to be a view definition stored somewhere in the PG database catalog. Find it and run the SELECT statement.That'll at least narrow down the problem (as well as letting you run EXPLAIN on it).On Thu, Feb 26, 2026 at 6:48 PM Michael Nolan <htfoot@gmail.com> wrote:I can run select statements on the mysql server from the azure server, but the command we're using to refresh the matview is:REFRESH MATERIALIZED VIEW memmast_ratings;Mike NolanOn Thu, Feb 26, 2026 at 5:45 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:Mike,1. You're using mysql_fdw to connect?2. What happens when you just run the raw SELECT statement (redirecting stdout to /dev/null, since we only need timings and error messages) from psql?On Thu, Feb 26, 2026 at 6:37 PM Michael Nolan <htfoot@gmail.com> wrote:My understanding is that the mysql server is at a Linode facility in PA, the current production postgres server is in Asheville NC, and the intended new server is a Azure server in the eastern US, not sure exactly where.The two small matviews refresh, the two bigger ones fail, so it seems size-related, which is why I was wondering if the settings might make a difference because this server isn't fully production-scale yet. But weve transferred 175 GB files to it in about 6 hours so I think the net connection itself is probably not the issue.Mike NolanOn Thu, Feb 26, 2026 at 4:11 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:On 2/26/26 1:59 PM, Michael Nolan wrote:
> We have a connection from a PostgreSQL server to a MySQL server which is
> used to update a materialized view on the PostgreSQL server from tables
> on the MySQL server (running CIVI-CRM, which may not be relevant.)
>
> We are trying to move the PostgreSQL server to a new cloud server.
>
> On the current production system, all the materialized views work, but
> the biggest of them can take about an hour.
>
> On the new system, the smallest of the materialized views works, but the
> larger ones all seem to time out.
"... move the PostgreSQL server to a new cloud server", where is that
relative to the MySQL server compared to old Postgres server?
>
> Could this be some kind of setting on PostgreSQL, like a memory or
> buffer issue?
>
> Mike Nolan
> htfoot@gmail.com <mailto:htfoot@gmail.com>
--
Adrian Klaver
adrian.klaver@aklaver.com--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
On 2/26/26 3:37 PM, Michael Nolan wrote: > My understanding is that the mysql server is at a Linode facility in PA, > the current production postgres server is in Asheville NC, and the > intended new server is a Azure server in the eastern US, not sure > exactly where. > > The two small matviews refresh, the two bigger ones fail, so it seems > size-related, which is why I was wondering if the settings might make a > difference because this server isn't fully production-scale yet. But > weve transferred 175 GB files to it in about 6 hours so I think the net > connection itself is probably not the issue. 1) If my math is right that works out to 8.1 MB/s. Testing here(Bellingham WA) to my Linode in Fremont CA I get somewhere around an average of 65 MB/s download/upload. Not saying this is the issue, but you might want to test network speed between your current server and new server to the MySQL server. 2) Is there an error raised in the Postgres logs? 3) What are the 'hardware' specifications for the Azure server vs the existing(Linode?) one? 4) Define small view vs large view. > > Mike Nolan > > On Thu, Feb 26, 2026 at 4:11 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 2/26/26 1:59 PM, Michael Nolan wrote: > > We have a connection from a PostgreSQL server to a MySQL server > which is > > used to update a materialized view on the PostgreSQL server from > tables > > on the MySQL server (running CIVI-CRM, which may not be relevant.) > > > > We are trying to move the PostgreSQL server to a new cloud server. > > > > On the current production system, all the materialized views > work, but > > the biggest of them can take about an hour. > > > > On the new system, the smallest of the materialized views works, > but the > > larger ones all seem to time out. > > "... move the PostgreSQL server to a new cloud server", where is that > relative to the MySQL server compared to old Postgres server? > > > > > Could this be some kind of setting on PostgreSQL, like a memory or > > buffer issue? > > > > Mike Nolan > > htfoot@gmail.com <mailto:htfoot@gmail.com> > <mailto:htfoot@gmail.com <mailto:htfoot@gmail.com>> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com