Обсуждение: Fwd: Process scheduling in postgres
Hi,
I have a 4 core CPU for postgres and I have submitted 6 queries parallely in 6 different sessions. But I am seeing only 1 query per CPU and the remainig queries are queued. I am not sure why is it happening. Do you have any special scheduler for postgres? I can see that CPU has been shared with other non postgres processes but postgres processes are not sharing any CPU within a core. The following is the process states.lms 2868 2209 98 07:20 ? 00:02:30 postgres: lms controller [local] SELECT
lms 2869 2209 98 07:20 ? 00:02:30 postgres: lms controller [local] SELECT
lms 2870 2209 0 07:20 ? 00:00:00 postgres: lms controller [local] SELECT
lms 2871 2209 0 07:20 ? 00:00:00 postgres: lms controller [local] SELECT
lms 2872 2209 0 07:20 ? 00:00:00 postgres: lms controller [local] SELECT
lms 2873 2209 98 07:20 ? 00:02:30 postgres: lms controller [local] SELECT
If you see above, three Select queries have been queued. They are acquiring CPU only when there is a core without any postgres query running on it.
I know the postgres limitation of one query using only one core. But is there any limitation of one CPU running only one query at a time?On 4/22/2016 1:30 AM, raghu vineel wrote:
I have a 4 core CPU for postgres and I have submitted 6 queries parallely in 6 different sessions. But I am seeing only 1 query per CPU and the remainig queries are queued. I am not sure why is it happening. Do you have any special scheduler for postgres? I can see that CPU has been shared with other non postgres processes but postgres processes are not sharing any CPU within a core. The following is the process states.
lms 2868 2209 98 07:20 ? 00:02:30 postgres: lms controller [local] SELECT
lms 2869 2209 98 07:20 ? 00:02:30 postgres: lms controller [local] SELECT
lms 2870 2209 0 07:20 ? 00:00:00 postgres: lms controller [local] SELECT
lms 2871 2209 0 07:20 ? 00:00:00 postgres: lms controller [local] SELECT
lms 2872 2209 0 07:20 ? 00:00:00 postgres: lms controller [local] SELECT
lms 2873 2209 98 07:20 ? 00:02:30 postgres: lms controller [local] SELECTIf you see above, three Select queries have been queued. They are acquiring CPU only when there is a core without any postgres query running on it.I know the postgres limitation of one query using only one core. But is there any limitation of one CPU running only one query at a time?
no such limitation. any chance there's locking involved here? take a look at pg_locks and pg_stat_activity the next time you run such a test.
-- john r pierce, recycling bits in santa cruz
On Fri, Apr 22, 2016 at 5:21 PM raghu vineel <raghuvineel@gmail.com> wrote:
Hi,I have a 4 core CPU for postgres and I have submitted 6 queries parallely in 6 different sessions. But I am seeing only 1 query per CPU and the remainig queries are queued. I am not sure why is it happening. Do you have any special scheduler for postgres? I can see that CPU has been shared with other non postgres processes but postgres processes are not sharing any CPU within a core. The following is the process states.
lms 2868 2209 98 07:20 ? 00:02:30 postgres: lms controller [local] SELECT
lms 2869 2209 98 07:20 ? 00:02:30 postgres: lms controller [local] SELECT
lms 2870 2209 0 07:20 ? 00:00:00 postgres: lms controller [local] SELECT
lms 2871 2209 0 07:20 ? 00:00:00 postgres: lms controller [local] SELECT
lms 2872 2209 0 07:20 ? 00:00:00 postgres: lms controller [local] SELECT
lms 2873 2209 98 07:20 ? 00:02:30 postgres: lms controller [local] SELECTIf you see above, three Select queries have been queued. They are acquiring CPU only when there is a core without any postgres query running on it.I know the postgres limitation of one query using only one core. But is there any limitation of one CPU running only one query at a time?
Are all the queries related to the same table(s)?
Which version of PostgreSQL? What's the version of OS?
Check out the locks which are not granted yet - pg_locks.granted
Vineel.Your help is appreciated.Thanks and regards,
--
--
Best Regards
Sameer Kumar | DB Solution Architect
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
On Sat, Apr 23, 2016 at 11:21 AM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:
On Fri, Apr 22, 2016 at 5:21 PM raghu vineel <raghuvineel@gmail.com> wrote:Hi,I have a 4 core CPU for postgres and I have submitted 6 queries parallely in 6 different sessions. But I am seeing only 1 query per CPU and the remainig queries are queued. I am not sure why is it happening. Do you have any special scheduler for postgres? I can see that CPU has been shared with other non postgres processes but postgres processes are not sharing any CPU within a core. The following is the process states.
lms 2868 2209 98 07:20 ? 00:02:30 postgres: lms controller [local] SELECT
lms 2869 2209 98 07:20 ? 00:02:30 postgres: lms controller [local] SELECT
lms 2870 2209 0 07:20 ? 00:00:00 postgres: lms controller [local] SELECT
lms 2871 2209 0 07:20 ? 00:00:00 postgres: lms controller [local] SELECT
lms 2872 2209 0 07:20 ? 00:00:00 postgres: lms controller [local] SELECT
lms 2873 2209 98 07:20 ? 00:02:30 postgres: lms controller [local] SELECTIf you see above, three Select queries have been queued. They are acquiring CPU only when there is a core without any postgres query running on it.I know the postgres limitation of one query using only one core. But is there any limitation of one CPU running only one query at a time?Are all the queries related to the same table(s)?Which version of PostgreSQL? What's the version of OS?Check out the locks which are not granted yet - pg_locks.grantedVineel.Your help is appreciated.Thanks and regards,----Best RegardsSameer Kumar | DB Solution ArchitectASHNIK PTE. LTD.101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
Yes, they query the same table. But all queries are select only. Postgres is 8.3 and OS is LINUX 2.6.32-431.el6.x86_64.
Also I could see that pg_locks.granted is true for the queries I have submitted and lock mode is AccessShareLock.
On 4/25/2016 12:40 AM, raghu vineel wrote:
Yes, they query the same table. But all queries are select only. Postgres is 8.3 and OS is LINUX 2.6.32-431.el6.x86_64.
Also I could see that pg_locks.granted is true for the queries I have submitted and lock mode is AccessShareLock.
select * from pg_stat_activity when its in that state, and look at ... oh wow, 8.3? thats WAY out of support and obsolete.
anyways, pg_stat_activity is an internal table, and its fields have changed a few times over the releases, I don't have anything that old to look at, but there should be a field 'state' or 'status' or something that shows if the query is waiting, locked, or running. pg_stat_activity has one row for each open connection.
-- john r pierce, recycling bits in santa cruz