Обсуждение: Create tables performance
Hi to all,
I run Postgresql 8.3.9 on a dedicated server running with Debian 5.0.4, a strong bi quad-proc with RAM 16Go. My biggest db contains at least 100 000 tables. Last time, I started a Java process I use to make some change on it, it created 170 new tables and it took one full minute. That is a very long time for such a process on such a server !
Do you think there could be some configuration tuning to do to improve the performance for create tables ?
Or do I have to use tablespaces because 100000 files in a single folder is a too many for OS ?
It's possible to migrate the DB in 9.1 version. Do you think it could solve the trouble ?
Thank you all for your advices,
Best regards
Sylvain
On Fri, Jul 6, 2012 at 8:15 AM, Sylvain CAILLET <scaillet@alaloop.com> wrote: > Hi to all, > > I run Postgresql 8.3.9 on a dedicated server running with Debian 5.0.4, a > strong bi quad-proc with RAM 16Go. My biggest db contains at least 100 000 > tables. Last time, I started a Java process I use to make some change on it, > it created 170 new tables and it took one full minute. That is a very long > time for such a process on such a server ! What if you create those 170 tables in a database without 100,000 pre-existing tables? What else does your script do? I can create 170 tables each with 10 rows in a database containing 100,000 other tables in less than a second on 8.3.9, either all in one transaction or in ~340 separate transactions. So whatever problem you are having is probably specific to your details, not a generic issue. It is hard to say if an upgrade would help if the root cause is not known. What do the standard monitoring tools show? Are you IO bound, or CPU bound? If CPU, is it in postgres or in java? > Do you think there could be some configuration tuning to do to improve the > performance for create tables ? > Or do I have to use tablespaces because 100000 files in a single folder is a > too many for OS ? I doubt that that is a problem on any reasonably modern Linux. Cheers, Jeff
On 06/07/12 16:15, Sylvain CAILLET wrote: > Hi to all, > > I run Postgresql 8.3.9 on a dedicated server running with Debian 5.0.4, > a strong bi quad-proc with RAM 16Go. My biggest db contains at least 100 > 000 tables. That is a *lot* of tables and it's probably going to be slow whatever you do. > Last time, I started a Java process I use to make some > change on it, it created 170 new tables and it took one full minute. What are you using all these tables for? I'm assuming most of them have identical structure. -- Richard Huxton Archonet Ltd
On 07/06/2012 11:15 PM, Sylvain CAILLET wrote:
If you create and drop a lot of tables, you need to make sure you're vacuuming the pg_catalog tables frequently. Newer versions mostly take care of this for you, but on 8.3 you'll at minimum have to turn autovaccum right up.Hi to all,I run Postgresql 8.3.9 on a dedicated server running with Debian 5.0.4, a strong bi quad-proc with RAM 16Go. My biggest db contains at least 100 000 tables. Last time, I started a Java process I use to make some change on it, it created 170 new tables and it took one full minute. That is a very long time for such a process on such a server !
See what happens if you run in psql, as a Pg superuser (usually the "postgres" account):
CLUSTER pg_class_oid_index ON pg_catalog.pg_class;
CLUSTER pg_type_oid_index ON pg_catalog.pg_type;
CLUSTER pg_attribute_relid_attnam_index ON pg_catalog.pg_attribute;
CLUSTER pg_index_indexrelid_index ON pg_catalog.pg_index;
I'm guessing you have severe table bloat in your catalogs, in which case this may help. I use CLUSTER instead of VACCUUM FULL because on old versions like 8.3 it'll run faster and sort the indexes for you too.
Do you think there could be some configuration tuning to do to improve the performance for create tables ?Or do I have to use tablespaces because 100000 files in a single folder is a too many for OS ?
That won't be a problem unless your OS and file system are truly crap.
--
Craig Ringer
Hi,
Thank you all for your help.
@Jeff : my daemon creates these tables at start time so it doesn't do anything else at the same time. The CPU is loaded between 20% and 25%.
@Richard : Sure the DB number of table is quite big and sure most of them have the same structure, but it's very hard to move it now so I have to deal with it for a while !
@Craig : I can't run any of the queries. Fo example, "CLUSTER pg_class_oid_index ON pg_catalog.pg_class;" throws a "ERROR: "pg_class" is a system catalog" exception. But, using VACUUM FULL, it's done in less than a second. Autovacuum is on but not tuned in postgresql configuration file.
Sylvain Caillet
On 07/06/2012 11:15 PM, Sylvain CAILLET wrote:If you create and drop a lot of tables, you need to make sure you're vacuuming the pg_catalog tables frequently. Newer versions mostly take care of this for you, but on 8.3 you'll at minimum have to turn autovaccum right up.Hi to all,I run Postgresql 8.3.9 on a dedicated server running with Debian 5.0.4, a strong bi quad-proc with RAM 16Go. My biggest db contains at least 100 000 tables. Last time, I started a Java process I use to make some change on it, it created 170 new tables and it took one full minute. That is a very long time for such a process on such a server !
See what happens if you run in psql, as a Pg superuser (usually the "postgres" account):
CLUSTER pg_class_oid_index ON pg_catalog.pg_class;
CLUSTER pg_type_oid_index ON pg_catalog.pg_type;
CLUSTER pg_attribute_relid_attnam_index ON pg_catalog.pg_attribute;
CLUSTER pg_index_indexrelid_index ON pg_catalog.pg_index;
I'm guessing you have severe table bloat in your catalogs, in which case this may help. I use CLUSTER instead of VACCUUM FULL because on old versions like 8.3 it'll run faster and sort the indexes for you too.Do you think there could be some configuration tuning to do to improve the performance for create tables ?Or do I have to use tablespaces because 100000 files in a single folder is a too many for OS ?
That won't be a problem unless your OS and file system are truly crap.
--
Craig Ringer
On Sun, Jul 8, 2012 at 11:49 PM, Sylvain CAILLET <scaillet@alaloop.com> wrote: > Hi, > > Thank you all for your help. > > @Jeff : my daemon creates these tables at start time so it doesn't do > anything else at the same time. The CPU is loaded between 20% and 25%. How does it decide which tables to create? Is it querying the existing tables to figure out what new ones to make? Is the rest of the time going to IO wait? Cheers, Jeff
Yes, you're right ! The process checks if all these tables exist before creating them. So it might be the SELECT that takes time. To check existence, I use the following query :
select * from pg_tables where tablename='the_table';
May be it's not the best way. And I launch a query per table ! Not good at all.
Thank you all, I will optimize this.
Sylvain
On Sun, Jul 8, 2012 at 11:49 PM, Sylvain CAILLET <scaillet@alaloop.com> wrote:
> Hi,
>
> Thank you all for your help.
>
> @Jeff : my daemon creates these tables at start time so it doesn't do
> anything else at the same time. The CPU is loaded between 20% and 25%.
How does it decide which tables to create? Is it querying the
existing tables to figure out what new ones to make? Is the rest of
the time going to IO wait?
Cheers,
Jeff