PostgreSQL性能优化综合

create table user_info_0 like user_info including all);                    create table user_info_1 create table user_info_2 create table user_info_3 create table user_info_4 );                                         create table user_session_0 like user_session including allcreate table user_session_1 create table user_session_2 create table user_session_3 create table user_session_4 );      

 user_info_0 )                    4000000'1970-01-01'                    ;                                          user_info_1 40000018000000 user_info_2 800000112000000 user_info_3 1200000116000000 user_info_4 16000001 user_session_0  user_session_1  user_session_2  user_session_3  user_session_4 );                                         ;                    alter table user_info_0 add primary key alter table user_info_1 add primary key alter table user_info_2 add primary key alter table user_info_3 add primary key alter table user_info_4 add primary key alter table user_session_0 add primary key alter table user_session_1 add primary key alter table user_session_2 add primary key alter table user_session_3 add primary key alter table user_session_4 add primary key );      

 f_user_login_0                    OUT o_qq numeric                     $BODY$                    declare                    begin                    qq                    o_qq                    from user_info_0 update user_session_0 $BODY$                     f_user_login_1                    from user_info_1 update user_session_1  f_user_login_2                    from user_info_2 update user_session_2  f_user_login_3                    from user_info_3 update user_session_3  f_user_login_4                    from user_info_4 update user_session_4  f_user_logout_0                    int                    exception                     others then                     f_user_logout_1                     f_user_logout_2                     f_user_logout_3                     f_user_logout_4                      【调优阶段7】       cat login*.sql       
4000000                     f_user_login_040000018000000                     f_user_login_1800000112000000                     f_user_login_21200000116000000                     f_user_login_316000001 f_user_login_4     cat logout*.sql       
 f_user_logout_0 f_user_logout_1 f_user_logout_2 f_user_logout_3 f_user_logout_4login0U digoal digoal >./loglogin0 &              login1login1 login2login2 login3login3 login4login4 &    
       cat log.log*       
 prepared                    233348                    1281.8180971281.8371090.0034924000000                    0.771932    SELECT f_user_login_0233466                    1282.5147741282.5735000.0035468000000                    0.771399    SELECT f_user_login_12                    475466                    2612.2007832612.2815260.00360512000000                    0.757312    SELECT f_user_login_2468904                    2576.3804432576.4884850.00358716000000                    0.767869    SELECT f_user_login_3439381                    2414.3470862414.4256000.0044310.817879    SELECT f_user_login_4);                    总计                    10167.261183)      
      到这里我们还没有关注过表空间,其实这些表拆分后它们还在同一个表空间里面. 把它们放在不同的表空间可以扩展它们整体的IO吞吐能力.  
               # \db+                                    List of tablespaces                 name  | Owner  |         Location          | Access privileges | Description               ------------+----------+---------------------------------------------+---------------------+-------------               digoal   | postgres | /pgdata/digoal/1921/data02/pg_tbs/digoal  | postgres=C/postgres+|                     |     |                       | digoal=C/postgres  |               digoal_01 | postgres | /pgdata/digoal/1921/data03/pg_tbs/digoal_01 | postgres=C/postgres+|               digoal_02 | postgres | /pgdata/digoal/1921/data04/pg_tbs/digoal_02 | postgres=C/postgres+|               digoal_03 | postgres | /pgdata/digoal/1921/data05/pg_tbs/digoal_03 | postgres=C/postgres+|               digoal_04 | postgres | /pgdata/digoal/1921/data06/pg_tbs/digoal_04 | postgres=C/postgres+|                     |     |                       | digoal=C/postgres  |                       digoal=> alter table user_info_0 set tablespace digoal_04;               ALTER table               digoal=> alter table user_info_2 set tablespace digoal_01;               digoal=> alter table user_info_3 set tablespace digoal_02;               digoal=> alter table user_info_4 set tablespace digoal_03;                                alter index user_info_0_pkey  tablespace digoal_04;               ALTER INDEX               digoal alter index user_info_2_pkey  tablespace digoal_01 alter index user_info_3_pkey  tablespace digoal_02 alter index user_info_4_pkey  tablespace digoal_03;                                alter table user_session_0 table                alter table user_session_2  alter table user_session_3  alter table user_session_4  alter index user_session_0_pkey  alter index user_session_2_pkey  alter index user_session_3_pkey  alter index user_session_4_pkey ;        
       
       重新把它们加载到内存.      下节 :    http://blog.163.com/digoal@126/blog/static/163877040201221333411196/  
   
    【附】    pgbench simple|extended|prepare 部分源码 :    1. pgbench.c  
                   if (commands[st->state]->type == sql_COMMAND)                   {                       const Command*command = commands];               int           r;                               querymode  query_SIMPLE                    charsql            sql  xstrdupcommandargv[0]);                assignVariables sql);                               deBUG                fprintfstderr "clIEnt %d sending %s\n" stID);                           r PQsendquerycon            free}               else  query_EXTENDEDsql  command*paramsMAX_ARGS];                                           getqueryParamsPQsendqueryParamsargc - 1                                 NulL NulL query_PREPARED      nameMAX_PREPARE_name(!prepareduse_file])                                                      j;                       forj ;j]!= j++)                                                   PGresult  res;                                                           continue                    preparedStatementnamename                    res PQprepare name                         commands],0)">(PQresultStatus) PGRES_COMMAND_OK                        fprintf"%s"PQerrorMessage));               PQclear                sttrue}                                           preparedStatementnamePQsendqueryPrepared                                    }        

以上是内存溢出为你收集整理的PostgreSQL性能优化综合全部内容，希望文章能够帮你解决PostgreSQL性能优化综合所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。