Pkold

java nosql orientdb sql

orientdb的restfulapi调用

orientdb

本来想用 python 版本的驱动 但是 - - 已经有好久没更新了

只好用http的

    • 看起来还行
import requests
import json
class SqlSdk():
    def __init__(self,url="http://192.168.1.91:2480",name="free",password="free",database="rpg"):
        self.url = url
        self.name = name
        self.password = password
        self.database = database
        self.auth = (self.name,self.password)
    def exec(self,sql,params=[]):
        return requests.post(f"{self.url}/command/{self.database}/sql",auth=self.auth,data=json.dumps({
            "command": sql,
            "parameters": params
        }))

以下为官方文档的


search: keywords: ['SQL']


Introduction

When it comes to query languages, SQL is the most widely recognized standard. The majority of developers have experience and are comfortable with SQL. For this reason Orient DB uses SQL as its query language and adds some extensions to enable graph functionality. There are a few differences between the standard SQL syntax and that supported by OrientDB, but for the most part, it should feel very natural. The differences are covered in the OrientDB SQL dialect section of this page.

If you are looking for the most efficient way to traverse a graph, we suggest to use the SQL-Match instead.

Many SQL commands share the WHERE condition. Keywords and class names in OrientDB SQL are case insensitive. Field names and values are case sensitive. In the following examples keywords are in uppercase but this is not strictly required.

If you are not yet familiar with SQL, we suggest you to get the course on KhanAcademy.

For example, if you have a class MyClass with a field named id, then the following SQL statements are equivalent:

SELECT FROM MyClass WHERE id = 1
select from myclass where id = 1

The following is NOT equivalent. Notice that the field name 'ID' is not the same as 'id'.

SELECT FROM MyClass WHERE ID = 1

Automatic usage of indexes

OrientDB allows you to execute queries against any field, indexed or not-indexed. The SQL engine automatically recognizes if any indexes can be used to speed up execution. You can also query any indexes directly by using INDEX:<index-name> as a target. Example:

SELECT FROM INDEX:myIndex WHERE key = 'Jay'

Extra resources

OrientDB SQL dialect

OrientDB supports SQL as a query language with some differences compared with SQL. Orient Technologies decided to avoid creating Yet-Another-Query-Language. Instead we started from familiar SQL with extensions to work with graphs. We prefer to focus on standards.

If you want learn SQL, there are many online courses such as: - Online course Introduction to Databases by Jennifer Widom from Stanford university - Introduction to SQL at W3 Schools - Beginner guide to SQL - SQLCourse.com - YouTube channel Basic SQL Training by Joey Blue

To know more, look to OrientDB SQL Syntax.

Or order any book like these

No JOINs

The most important difference between OrientDB and a Relational Database is that relationships are represented by LINKS instead of JOINs.

For this reason, the classic JOIN syntax is not supported. OrientDB uses the "dot (.) notation" to navigate LINKS. Example 1 : In SQL you might create a join such as:

SELECT *
FROM Employee A, City B
WHERE A.city = B.id
AND B.name = 'Rome'

In OrientDB, an equivalent operation would be:

SELECT * FROM Employee WHERE city.name = 'Rome'

This is much more straight forward and powerful! If you use multiple JOINs, the OrientDB SQL equivalent will be an even larger benefit. Example 2: In SQL you might create a join such as:

SELECT *
FROM Employee A, City B, Country C,
WHERE A.city = B.id
AND B.country = C.id
AND C.name = 'Italy'

In OrientDB, an equivalent operation would be:

SELECT * FROM Employee WHERE city.country.name = 'Italy'

Projections

In SQL, projections are mandatory and you can use the star character * to include all of the fields. With OrientDB this type of projection is optional. Example: In SQL to select all of the columns of Customer you would write:

SELECT * FROM Customer

In OrientDB, the * is optional:

SELECT FROM Customer

See SQL projections

DISTINCT

In OrientDB v 3.0 you can use DISTINCT keyword exactly as in a relational database:

SELECT DISTINCT name FROM City

Until v 2.2, DISTINCT keyword was not allowed; there was a DISTINCT() function instead, with limited capabilities

//legacy

SELECT DISTINCT(name) FROM City

HAVING

OrientDB does not support the HAVING keyword, but with a nested query it's easy to obtain the same result. Example in SQL:

SELECT city, sum(salary) AS salary
FROM Employee
GROUP BY city
HAVING salary > 1000

This groups all of the salaries by city and extracts the result of aggregates with the total salary greater than 1,000 dollars. In OrientDB the HAVING conditions go in a select statement in the predicate:

SELECT FROM ( SELECT city, SUM(salary) AS salary FROM Employee GROUP BY city ) WHERE salary > 1000

Select from multiple targets

OrientDB allows only one class (classes are equivalent to tables in this discussion) as opposed to SQL, which allows for many tables as the target. If you want to select from 2 classes, you have to execute 2 sub queries and join them with the UNIONALL function:

SELECT FROM E, V

In OrientDB, you can accomplish this with a few variable definitions and by using the expand function to the union:

SELECT EXPAND( $c ) LET $a = ( SELECT FROM E ), $b = ( SELECT FROM V ), $c = UNIONALL( $a, $b )
参数 pg_hba.conf postgresql postgresql.auto.conf postgresql.conf 模板

PostgreSQL 11 参数模板 - 珍藏级 --- digoal

PostgreSQL , 参数 , 模板 , postgresql.conf , pg_hba.conf , postgresql.auto.conf

PostgreSQL 11 参数模板 - 珍藏级

作者

digoal

原文地址 https://github.com/digoal/blog/blob/master/201812/20181203_01.md

日期

2018-12-03

标签

PostgreSQL , 参数 , 模板 , postgresql.conf , pg_hba.conf , postgresql.auto.conf


背景

PostgreSQL 11 postgresql.conf 参数模板

# -----------------------------  
# PostgreSQL configuration file  
# -----------------------------  
#  
# This file consists of lines of the form:  
#  
#   name = value  
#  
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with  
# "#" anywhere on a line.  The complete list of parameter names and allowed  
# values can be found in the PostgreSQL documentation.  
#  
# The commented-out settings shown in this file represent the default values.  
# Re-commenting a setting is NOT sufficient to revert it to the default value;  
# you need to reload the server.  
#  
# This file is read on server startup and when the server receives a SIGHUP  
# signal.  If you edit the file on a running system, you have to SIGHUP the  
# server for the changes to take effect, run "pg_ctl reload", or execute  
# "SELECT pg_reload_conf()".  Some parameters, which are marked below,  
# require a server shutdown and restart to take effect.  
#  
# Any parameter can also be given as a command-line option to the server, e.g.,  
# "postgres -c log_connections=on".  Some parameters can be changed at run time  
# with the "SET" SQL command.  
#  
# Memory units:  kB = kilobytes        Time units:  ms  = milliseconds  
#                MB = megabytes                     s   = seconds  
#                GB = gigabytes                     min = minutes  
#                TB = terabytes                     h   = hours  
#                                                   d   = days  


#------------------------------------------------------------------------------  
# FILE LOCATIONS  
#------------------------------------------------------------------------------  

# The default values of these variables are driven from the -D command-line  
# option or PGDATA environment variable, represented here as ConfigDir.  

#data_directory = 'ConfigDir'           # use data in another directory  
                                        # (change requires restart)  
#hba_file = 'ConfigDir/pg_hba.conf'     # host-based authentication file  
                                        # (change requires restart)  
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file  
                                        # (change requires restart)  

# If external_pid_file is not explicitly set, no extra PID file is written.  
#external_pid_file = ''                 # write an extra PID file  
                                        # (change requires restart)  


#------------------------------------------------------------------------------  
# CONNECTIONS AND AUTHENTICATION  
#------------------------------------------------------------------------------  

# - Connection Settings -  

listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;  
                                        # comma-separated list of addresses;  
                                        # defaults to 'localhost'; use '*' for all  
                                        # (change requires restart)  
# 根据业务需求设定监听  
port = 1921                             # (change requires restart)  

# 建议不要大于 200 * 四分之一物理内存(GB), 例如四分之一物理内存为16G,则建议不要超过3200.      
# (假设一个连接耗费5MB,实际上syscache很大时,可能更多)   
# [《PostgreSQL relcache在长连接应用中的内存霸占"坑"》](201607/20160709_01.md)   
max_connections = 2000                  # (change requires restart)  
superuser_reserved_connections = 13      # (change requires restart)  

# $PGDATA, /tmp中 创建unix socket监听  
unix_socket_directories = '., /tmp'        # comma-separated list of directories  
                                        # (change requires restart)  
#unix_socket_group = ''                 # (change requires restart)  

# 除了OWNER和超级用户,其他用户无法从/tmp unix socket连接该实例  
unix_socket_permissions = 0700          # begin with 0 to use octal notation     
                                        # (change requires restart)  
#bonjour = off                          # advertise server via Bonjour  
                                        # (change requires restart)  
#bonjour_name = ''                      # defaults to the computer name  
                                        # (change requires restart)  

# - TCP Keepalives -  
# see "man 7 tcp" for details  

# 如果你连接数据库空闲一段时间会端口,可能是网络中存在会话超时的设备,建议可以设置一下这个心跳时间,TCP心跳间隔会缩短到60秒。  
tcp_keepalives_idle = 60                # TCP_KEEPIDLE, in seconds;  
                                        # 0 selects the system default  
tcp_keepalives_interval = 10            # TCP_KEEPINTVL, in seconds;  
                                        # 0 selects the system default  
tcp_keepalives_count = 10               # TCP_KEEPCNT;  
                                        # 0 selects the system default  

# - Authentication -  

#authentication_timeout = 1min          # 1s-600s  

# md5 or scram-sha-256   # 如果MD5会泄露,建议使用scram-sha-256,但是相互不兼容,请注意。   
# [《PostgreSQL 10.0 preview 安全增强 - SASL认证方法 之 scram-sha-256 安全认证机制》](201703/20170309_01.md)    
#password_encryption = md5              # md5 or scram-sha-256  
#db_user_namespace = off  

# GSSAPI using Kerberos  
#krb_server_keyfile = ''  
#krb_caseins_users = off  

# - SSL -  

#ssl = off  
#ssl_ca_file = ''  
#ssl_cert_file = 'server.crt'  
#ssl_crl_file = ''  
#ssl_key_file = 'server.key'  
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers  
#ssl_prefer_server_ciphers = on  
#ssl_ecdh_curve = 'prime256v1'  
#ssl_dh_params_file = ''  
#ssl_passphrase_command = ''  
#ssl_passphrase_command_supports_reload = off  


#------------------------------------------------------------------------------  
# RESOURCE USAGE (except WAL)  
#------------------------------------------------------------------------------  

# - Memory -  

# 1/4 主机内存   
shared_buffers = 24GB                  # min 128kB  
                                        # (change requires restart)  
# 当不使用huge page,并且连接数大于3000时,建议shared buffer不要超过48G  
# 建议shared buffer设置超过32GB时 使用大页,页大小 /proc/meminfo Hugepagesize    
huge_pages = try                # on, off, or try  
                                        # (change requires restart)  
#temp_buffers = 8MB                     # min 800kB  

# 如果用户需要使用两阶段提交,需要设置为大于0,建议与max_connections一样大  
max_prepared_transactions = 2000                # zero disables the feature  
                                        # (change requires restart)  
# Caution: it is not advisable to set max_prepared_transactions nonzero unless  
# you actively intend to use prepared transactions.  

# 可以在会话中设置,如果有大量JOIN,聚合操作,并且期望使用hash agg或hash join。   
# 可以设大一些,但是不建议大于    四分之一内存除以最大连接数  .   
# (一条QUERY中可以使用多倍WORK_MEM,与执行计划中的NODE有关)    
# 建议给一个输入,AP模式TP模式OR混合模式。三种模式使用三种不同的计算公式  
work_mem = 8MB                          # min 64kB  

# min( 2G, (1/4 主机内存)/autovacuum_max_workers )    
maintenance_work_mem = 2GB              # min 1MB  
#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem  
#max_stack_depth = 2MB                  # min 100kB  
dynamic_shared_memory_type = posix      # the default is the first option  
                                        # supported by the operating system:  
                                        #   posix  
                                        #   sysv  
                                        #   windows  
                                        #   mmap  
                                        # use none to disable dynamic shared memory  
                                        # (change requires restart)  

# - Disk -  

# 如果需要限制临时文件使用量,可以设置。  
# 例如防止有异常的递归调用,无限使用临时文件。  
#temp_file_limit = -1                   # limits per-process temp file space  
                                        # in kB, or -1 for no limit  

# - Kernel Resources -  

## 如果你的数据库有非常多小文件(比如有几十万以上的表,还有索引等,并且每张表都会被访问到时),  
# 建议FD可以设多一些,避免进程需要打开关闭文件。  
## 但是不要大于前面章节系统设置的ulimit -n(open files)  
# max_files_per_process=655360  

#max_files_per_process = 1000           # min 25  
                                        # (change requires restart)  

# - Cost-Based Vacuum Delay -  

# 如果你的IO非常好,则可以关闭vacuum delay   
vacuum_cost_delay = 0                   # 0-100 milliseconds  
#vacuum_cost_page_hit = 1               # 0-10000 credits  
#vacuum_cost_page_miss = 10             # 0-10000 credits  
#vacuum_cost_page_dirty = 20            # 0-10000 credits  

# io很好,CPU核数很多的机器,设大一些。如果设置了vacuum_cost_delay = 0 ,则这个不需要配置  
vacuum_cost_limit = 10000                # 1-10000 credits  

# - Background Writer -  

bgwriter_delay = 10ms                   # 10-10000ms between rounds  
bgwriter_lru_maxpages = 1000            # max buffers written/round, 0 disables  
bgwriter_lru_multiplier = 10.0          # 0-10.0 multiplier on buffers scanned/round  
bgwriter_flush_after = 512kB            # measured in pages, 0 disables  

# - Asynchronous Behavior -  

effective_io_concurrency = 0            # 1-1000; 0 disables prefetching  

# wal sender, user 动态fork的process, parallel worker等都算作 worker process, 所以你需要设置足够大.   
max_worker_processes = 128              # (change requires restart)  

#  如果需要使用并行创建索引,设置为大于1 ,不建议超过 主机cores-2  
max_parallel_maintenance_workers = 6    # taken from max_parallel_workers  

#  如果需要使用并行查询,设置为大于1 ,不建议超过 主机cores-2  
max_parallel_workers_per_gather = 0     # taken from max_parallel_workers  
parallel_leader_participation = on  

#  如果需要使用并行查询,设置为大于1 ,不建议超过 主机cores-2  
#  必须小于 max_worker_processes   
max_parallel_workers = 32               # maximum number of max_worker_processes that  
                                        # can be used in parallel operations  
#old_snapshot_threshold = -1            # 1min-60d; -1 disables; 0 is immediate  
                                        # (change requires restart)  
#backend_flush_after = 256               # measured in pages, 0 disables  


#------------------------------------------------------------------------------  
# WRITE-AHEAD LOG  
#------------------------------------------------------------------------------  

# - Settings -  

# 需要流复制物理备库、归档、时间点恢复时,设置为replica,需要逻辑订阅或逻辑备库则设置为logical  
wal_level = replica  # minimal, replica, or logical  
                                        # (change requires restart)  
#fsync = on                             # flush data to disk for crash safety  
                                        # (turning this off can cause  
                                        # unrecoverable data corruption)  

# 如果双节点,设置为ON,如果是多副本,同步模式,建议设置为remote_write。   
# 如果磁盘性能很差,并且是OLTP业务。可以考虑设置为off降低COMMIT的RT,提高吞吐(设置为OFF时,可能丢失部分XLOG RECORD)  
synchronous_commit = off                # synchronization level;  
                                        # off, local, remote_write, remote_apply, or on  

# 建议使用pg_test_fsync测试后,决定用哪个最快。通常LINUX下open_datasync比较快。  
#wal_sync_method = fsync                # the default is the first option  
                                        # supported by the operating system:  
                                        #   open_datasync  
                                        #   fdatasync (default on Linux)  
                                        #   fsync  
                                        #   fsync_writethrough  
                                        #   open_sync  

# 如果文件系统支持COW例如ZFS,则建议设置为OFF。 如果文件系统可以保证datafile block size的原子写,在对齐后也可以设置为OFF。  
# 如果底层存储能保证IO的原子写,也可以设置为OFF。  
full_page_writes = on                  # recover from partial page writes  

# 当写FULL PAGE的io是瓶颈时建议开启  
wal_compression = on                  # enable compression of full-page writes  
#wal_log_hints = off                    # also do full page writes of non-critical updates  
                                        # (change requires restart)  
# 建议 min( 512MB, shared_buffers/32 )   
#wal_buffers = -1                       # min 32kB, -1 sets based on shared_buffers  
                                        # (change requires restart)  

# 如果设置了synchronous_commit = off,可以设置wal_writer_delay  
wal_writer_delay = 10ms         # 1-10000 milliseconds  
wal_writer_flush_after = 1MB            # measured in pages, 0 disables  

# 如果synchronous_commit=on, 并且已知业务系统为高并发,对数据库有写操作的小事务,则可以设置commit_delay来实现分组提交,合并WAL FSYNCIO 。  
#commit_delay = 10                       # range 0-100000, in microseconds  
# 同时处于提交状态的事务数超过commit_siblings时,使用分组提交  
#commit_siblings = 5                    # range 1-1000  

# - Checkpoints -  

#  不建议频繁做检查点,否则XLOG会产生很多的FULL PAGE WRITE(when full_page_writes=on)。  
checkpoint_timeout = 30min              # range 30s-1d  

# 建议等于SHARED BUFFER,或2倍。  
# 同时需要考虑崩溃恢复时间, 越大,检查点可能拉越长导致崩溃恢复耗时越长。但是越小,开启FPW时,WAL日志写入量又越大。 建议采用COW文件系统,关闭FPW。  
max_wal_size = 48GB  
# 建议是SHARED BUFFER的2分之一  
min_wal_size = 12GB  

# 硬盘好的情况下,可以让检查点快速结束,恢复时也可以快速达到一致状态。否则建议0.5~0.9  
checkpoint_completion_target = 0.1    # checkpoint target duration, 0.0 - 1.0  

# IO很好的机器,不需要考虑平滑调度, 否则建议128~256kB  
checkpoint_flush_after = 256kB          # measured in pages, 0 disables  
#checkpoint_flush_after = 0             # measured in pages, 0 disables  
#checkpoint_warning = 30s               # 0 disables  

# - Archiving -  

# 建议默认打开,因为修改它需要重启实例  
#archive_mode = off             # enables archiving; off, on, or always  
                                # (change requires restart)  

#  后期再修改,如  'test ! -f /disk1/digoal/arch/%f && cp %p /disk1/digoal/arch/%f'  
#archive_command = ''           # command to use to archive a logfile segment  
                                # placeholders: %p = path of file to archive  
                                #               %f = file name only  
                                # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  
#archive_timeout = 0            # force a logfile segment switch after this  
                                # number of seconds; 0 disables  


#------------------------------------------------------------------------------  
# REPLICATION  
#------------------------------------------------------------------------------  

# - Sending Servers -  

# Set these on the master and on any standby that will send replication data.  

# 同时需要几个流复制连接,根据实际需求设定  
max_wal_senders = 10             # max number of walsender processes  
                                # (change requires restart)  

# 根据实际情况设置保留WAL的数量,主要是防止过早的清除WAL,导致备库因为主库的WAL清除而中断。根据实际情况设定。  
#wal_keep_segments = 0          # in logfile segments; 0 disables  
#wal_sender_timeout = 60s       # in milliseconds; 0 disables  


# 根据实际情况设置需要创建多少replication slot  
# 使用slot,可以保证流复制下游没有接收的WAL会在当前节点永久保留。所以必须留意下游的接收情况,否则可能导致WAL爆仓  
# 建议大于等于max_wal_senders  
#max_replication_slots = 10     # max number of replication slots  
                                # (change requires restart)  
#track_commit_timestamp = off   # collect timestamp of transaction commit  
                                # (change requires restart)  

# - Master Server -  

# These settings are ignored on a standby server.  


# 如果有2个或2个以上的备库,可以考虑使用同步多副本模式。 根据实际情况设置  
[PostgreSQL 一主多从(多副本,强同步)简明手册 - 配置、压测、监控、切换、防脑裂、修复、0丢失 - 珍藏级》](201803/20180326_01.md)    
#synchronous_standby_names = '' # standby servers that provide sync rep  
                                # method to choose sync standbys, number of sync standbys,  
                                # and comma-separated list of application_name  
                                # from standby(s); '*' = all  

# 注意,容易导致膨胀,容易导致VACUUM进程空转,导致IO和CPU飙升。(特别是autovacuum naptime配置很小时)  
#vacuum_defer_cleanup_age = 0   # number of xacts by which cleanup is delayed  

# - Standby Servers -  

# These settings are ignored on a master server.  

#hot_standby = on                       # "off" disallows queries during recovery  
                                        # (change requires restart)  
#max_standby_archive_delay = 30s        # max delay before canceling queries  
                                        # when reading WAL from archive;  
                                        # -1 allows indefinite delay  
#max_standby_streaming_delay = 30s      # max delay before canceling queries  
                                        # when reading streaming WAL;  
                                        # -1 allows indefinite delay  
#wal_receiver_status_interval = 10s     # send replies at least this often  
                                        # 0 disables  

# 建议关闭,以免备库长事务导致 主库无法回收垃圾而膨胀。  
[PostgreSQL物理"备库"的哪些操作或配置,可能影响"主库"的性能、垃圾回收、IO波动](201704/20170410_03.md)    
#hot_standby_feedback = off             # send info from standby to prevent  
                                        # query conflicts  
#wal_receiver_timeout = 60s             # time that receiver waits for  
                                        # communication from master  
                                        # in milliseconds; 0 disables  
#wal_retrieve_retry_interval = 5s       # time to wait before retrying to  
                                        # retrieve WAL after a failed attempt  

# - Subscribers -  

# These settings are ignored on a publisher.  

# [《PostgreSQL 10.0 preview 逻辑订阅 - 原理与最佳实践》](201702/20170227_01.md)    
# These settings are ignored on a publisher.   
# 必须小于  max_worker_processes  
#max_logical_replication_workers = 4    # taken from max_worker_processes  
                                        # (change requires restart)  
#max_sync_workers_per_subscription = 2  # taken from max_logical_replication_workers  


#------------------------------------------------------------------------------  
# QUERY TUNING  
#------------------------------------------------------------------------------  

# - Planner Method Configuration -  

#enable_bitmapscan = on  
#enable_hashagg = on  
#enable_hashjoin = on  
#enable_indexscan = on  
#enable_indexonlyscan = on  
#enable_material = on  
#enable_mergejoin = on  
#enable_nestloop = on  
#enable_parallel_append = on  
#enable_seqscan = on  
#enable_sort = on  
#enable_tidscan = on  
#enable_partitionwise_join = off  
#enable_partitionwise_aggregate = off  
#enable_parallel_hash = on  
#enable_partition_pruning = on  

# - Planner Cost Constants -  

#seq_page_cost = 1.0                    # measured on an arbitrary scale  
# 离散IO很好的机器(例如ssd, nvme ssd),不需要考虑离散和顺序扫描的成本差异   
random_page_cost = 1.1                 # same scale as above  
#cpu_tuple_cost = 0.01                  # same scale as above  
#cpu_index_tuple_cost = 0.005           # same scale as above  
#cpu_operator_cost = 0.0025             # same scale as above  
#parallel_tuple_cost = 0.1              # same scale as above  
#parallel_setup_cost = 1000.0   # same scale as above  

#jit_above_cost = 100000                # perform JIT compilation if available  
                                        # and query more expensive, -1 disables  
#jit_optimize_above_cost = 500000       # optimize JITed functions if query is  
                                        # more expensive, -1 disables  
#jit_inline_above_cost = 500000         # attempt to inline operators and  
                                        # functions if query is more expensive,  
                                        # -1 disables  

#min_parallel_table_scan_size = 8MB  
#min_parallel_index_scan_size = 512kB  

# 扣掉会话连接RSS,shared buffer, autovacuum worker, 剩下的都是OS可用的CACHE。  
effective_cache_size = 80GB  

# - Genetic Query Optimizer -  

#geqo = on  
#geqo_threshold = 12  
#geqo_effort = 5                        # range 1-10  
#geqo_pool_size = 0                     # selects default based on effort  
#geqo_generations = 0                   # selects default based on effort  
#geqo_selection_bias = 2.0              # range 1.5-2.0  
#geqo_seed = 0.0                        # range 0.0-1.0  

# - Other Planner Options -  

#default_statistics_target = 100        # range 1-10000  
#constraint_exclusion = partition       # on, off, or partition  
#cursor_tuple_fraction = 0.1            # range 0.0-1.0  
#from_collapse_limit = 8  
#join_collapse_limit = 8                # 1 disables collapsing of explicit  
                                        # JOIN clauses  
#force_parallel_mode = off  


#------------------------------------------------------------------------------  
# REPORTING AND LOGGING  
#------------------------------------------------------------------------------  

# - Where to Log -  

log_destination = 'csvlog'              # Valid values are combinations of  
                                        # stderr, csvlog, syslog, and eventlog,  
                                        # depending on platform.  csvlog  
                                        # requires logging_collector to be on.  

# This is used when logging to stderr:  
logging_collector = on                  # Enable capturing of stderr and csvlog  
                                        # into log files. Required to be on for  
                                        # csvlogs.  
                                        # (change requires restart)  

# These are only used if logging_collector is on:  
log_directory = 'log'                   # directory where log files are written,  
                                        # can be absolute or relative to PGDATA  
log_filename = 'postgresql-%a.log'      # log file name pattern,  
                                        # can include strftime() escapes  
#log_file_mode = 0600                   # creation mode for log files,  
                                        # begin with 0 to use octal notation  
log_truncate_on_rotation = on           # If on, an existing log file with the  
                                        # same name as the new log file will be  
                                        # truncated rather than appended to.  
                                        # But such truncation only occurs on  
                                        # time-driven rotation, not on restarts  
                                        # or size-driven rotation.  Default is  
                                        # off, meaning append to existing files  
                                        # in all cases.  
log_rotation_age = 1d                   # Automatic rotation of logfiles will  
                                        # happen after that time.  0 disables.  
log_rotation_size = 0                   # Automatic rotation of logfiles will  
                                        # happen after that much log output.  
                                        # 0 disables.  

# These are relevant when logging to syslog:  
#syslog_facility = 'LOCAL0'  
#syslog_ident = 'postgres'  
#syslog_sequence_numbers = on  
#syslog_split_messages = on  

# This is only relevant when logging to eventlog (win32):  
# (change requires restart)  
#event_source = 'PostgreSQL'  

# - When to Log -  

#client_min_messages = notice           # values in order of decreasing detail:  
                                        #   debug5  
                                        #   debug4  
                                        #   debug3  
                                        #   debug2  
                                        #   debug1  
                                        #   log  
                                        #   notice  
                                        #   warning  
                                        #   error  

#log_min_messages = warning             # values in order of decreasing detail:  
                                        #   debug5  
                                        #   debug4  
                                        #   debug3  
                                        #   debug2  
                                        #   debug1  
                                        #   info  
                                        #   notice  
                                        #   warning  
                                        #   error  
                                        #   log  
                                        #   fatal  
                                        #   panic  

#log_min_error_statement = error        # values in order of decreasing detail:  
                                        #   debug5  
                                        #   debug4  
                                        #   debug3  
                                        #   debug2  
                                        #   debug1  
                                        #   info  
                                        #   notice  
                                        #   warning  
                                        #   error  
                                        #   log  
                                        #   fatal  
                                        #   panic (effectively off)  

# 根据实际情况设定,例如业务上认为5秒以上是慢SQL,那么就设置为5秒。  
log_min_duration_statement = 5s        # -1 is disabled, 0 logs all statements  
                                        # and their durations, > 0 logs only  
                                        # statements running at least this number  
                                        # of milliseconds  


# - What to Log -  

#debug_print_parse = off  
#debug_print_rewritten = off  
#debug_print_plan = off  
#debug_pretty_print = on  
log_checkpoints = on   

# 如果业务是短连接,建议设置为OFF,否则建议设置为ON  
log_connections = on  

# 如果业务是短连接,建议设置为OFF,否则建议设置为ON  
log_disconnections = on  
#log_duration = off  
log_error_verbosity = verbose    # terse, default, or verbose messages  
#log_hostname = off  
log_line_prefix = '%m [%p] '            # special values:  
                                        #   %a = application name  
                                        #   %u = user name  
                                        #   %d = database name  
                                        #   %r = remote host and port  
                                        #   %h = remote host  
                                        #   %p = process ID  
                                        #   %t = timestamp without milliseconds  
                                        #   %m = timestamp with milliseconds  
                                        #   %n = timestamp with milliseconds (as a Unix epoch)  
                                        #   %i = command tag  
                                        #   %e = SQL state  
                                        #   %c = session ID  
                                        #   %l = session line number  
                                        #   %s = session start timestamp  
                                        #   %v = virtual transaction ID  
                                        #   %x = transaction ID (0 if none)  
                                        #   %q = stop here in non-session  
                                        #        processes  
                                        #   %% = '%'  
                                        # e.g. '<%u%%%d> '  
#log_lock_waits = off                   # log lock waits >= deadlock_timeout  

# 如果需要审计SQL,则可以设置为all  
#log_statement = 'none'                 # none, ddl, mod, all  
#log_replication_commands = off  
#log_temp_files = -1                    # log temporary files equal or larger  
                                        # than the specified size in kilobytes;  
                                        # -1 disables, 0 logs all temp files  
log_timezone = 'PRC'    

#------------------------------------------------------------------------------  
# PROCESS TITLE  
#------------------------------------------------------------------------------  

#cluster_name = ''                      # added to process titles if nonempty  
                                        # (change requires restart)  
#update_process_title = on  


#------------------------------------------------------------------------------  
# STATISTICS  
#------------------------------------------------------------------------------  

# - Query and Index Statistics Collector -  

#track_activities = on  
#track_counts = on  

# 跟踪IO耗时会带来一定的性能影响,默认是关闭的  
# 如果需要统计IO的时间开销,设置为ON  
# 建议用pg_test_timing测试一下获取时间的开销,如果开销很大,建议关闭这个时间跟踪。  
#track_io_timing = off  
#track_functions = none                 # none, pl, all  
#track_activity_query_size = 1024       # (change requires restart)  
#stats_temp_directory = 'pg_stat_tmp'  


# - Monitoring -  

#log_parser_stats = off  
#log_planner_stats = off  
#log_executor_stats = off  
#log_statement_stats = off  


#------------------------------------------------------------------------------  
# AUTOVACUUM  
#------------------------------------------------------------------------------  

#autovacuum = on                        # Enable autovacuum subprocess?  'on'  
                                        # requires track_counts to also be on.  
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and  
                                        # their durations, > 0 logs only  
                                        # actions running at least this number  
                                        # of milliseconds.  

# CPU核多,并且IO好的情况下,可多点,但是注意最多可能消耗这么多内存:   
# autovacuum_max_workers * autovacuum mem(autovacuum_work_mem),  
# 会消耗较多内存,所以内存也要有基础。       
# 当DELETE\UPDATE非常频繁时,建议设置多一点,防止膨胀严重      
autovacuum_max_workers = 8              # max number of autovacuum subprocesses  
                                        # (change requires restart)  

# 建议不要太高频率,否则会因为vacuum产生较多的XLOG。或者在某些垃圾回收不掉的情况下(例如长事务、feed back on,等),导致一直触发vacuum,CPU和IO都会升高  
[PostgreSQL垃圾回收代码分析 - why postgresql cann't reclaim tuple is HEAPTUPLE_RECENTLY_DEAD》](201505/20150503_01.md)    
[PostgreSQL物理"备库"的哪些操作或配置,可能影响"主库"的性能、垃圾回收、IO波动](201704/20170410_03.md)    
#autovacuum_naptime = 1min              # time between autovacuum runs  
#autovacuum_vacuum_threshold = 50       # min number of row updates before  
                                        # vacuum  
#autovacuum_analyze_threshold = 50      # min number of row updates before  
                                        # analyze  
#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before vacuum  
#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before analyze  

# 除了设置较大的FREEZE值。  
# 还是需要注意FREEZE风暴  [《PostgreSQL Freeze 风暴预测续 - 珍藏级SQL》](201804/20180411_01.md)    
# 表级定制freeze  
autovacuum_freeze_max_age = 1200000000  # maximum XID age before forced vacuum  
                                        # (change requires restart)  
autovacuum_multixact_freeze_max_age = 1400000000        # maximum multixact age  
                                        # before forced vacuum  
                                        # (change requires restart)  

# 如果数据库UPDATE非常频繁,建议设置为0。并且建议使用SSD  
autovacuum_vacuum_cost_delay = 0ms      # default vacuum cost delay for  
                                        # autovacuum, in milliseconds;  
                                        # -1 means use vacuum_cost_delay  
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for  
                                        # autovacuum, -1 means use  
                                        # vacuum_cost_limit  


#------------------------------------------------------------------------------  
# CLIENT CONNECTION DEFAULTS  
#------------------------------------------------------------------------------  

# - Statement Behavior -  

#search_path = '"$user", public'        # schema names  
#row_security = on  
#default_tablespace = ''                # a tablespace name, '' uses the default  
#temp_tablespaces = ''                  # a list of tablespace names, '' uses  
                                        # only default tablespace  
#check_function_bodies = on  
#default_transaction_isolation = 'read committed'  
#default_transaction_read_only = off  
#default_transaction_deferrable = off  
#session_replication_role = 'origin'  

# 可以用来防止风暴,但是不建议全局设置  
#statement_timeout = 0                  # in milliseconds, 0 is disabled  

# 执行DDL时,建议加上超时  
#lock_timeout = 0                       # in milliseconds, 0 is disabled  

# 空闲中事务自动清理,根据业务实际情况设置  
#idle_in_transaction_session_timeout = 0        # in milliseconds, 0 is disabled  

#vacuum_freeze_min_age = 50000000  
vacuum_freeze_table_age = 1150000000  
#vacuum_multixact_freeze_min_age = 5000000  
vacuum_multixact_freeze_table_age = 1150000000  
#vacuum_cleanup_index_scale_factor = 0.1        # fraction of total number of tuples  
                                                # before index cleanup, 0 always performs  
                                                # index cleanup  
#bytea_output = 'hex'                   # hex, escape  
#xmlbinary = 'base64'  
#xmloption = 'content'  

# 限制GIN扫描的返回结果集大小,在想限制超多匹配的返回时可以设置  
#gin_fuzzy_search_limit = 0  

# GIN索引pending list的大小  
#gin_pending_list_limit = 4MB  

# - Locale and Formatting -  

datestyle = 'iso, mdy'  
#intervalstyle = 'postgres'  
timezone = 'PRC'  
#timezone_abbreviations = 'Default'     # Select the set of available time zone  
                                        # abbreviations.  Currently, there are  
                                        #   Default  
                                        #   Australia (historical usage)  
                                        #   India  
                                        # You can create your own file in  
                                        # share/timezonesets/.  
#extra_float_digits = 0                 # min -15, max 3  
#client_encoding = sql_ascii            # actually, defaults to database  
                                        # encoding  

# These settings are initialized by initdb, but they can be changed.  
lc_messages = 'C'                       # locale for system error message  
                                        # strings  
lc_monetary = 'C'                       # locale for monetary formatting  
lc_numeric = 'C'                        # locale for number formatting  
lc_time = 'C'                           # locale for time formatting  

# default configuration for text search  
default_text_search_config = 'pg_catalog.english'  

# - Shared Library Preloading -  

# 需要加载什么LIB,预先加载,对于经常访问的库也建议预加载,例如postgis  
#shared_preload_libraries = 'pg_jieba,pipelinedb'        # (change requires restart)  
#local_preload_libraries = ''  
#session_preload_libraries = ''  

# - Other Defaults -  

#dynamic_library_path = '$libdir'  

jit = off                               # allow JIT compilation  
#jit_provider = 'llvmjit'               # JIT implementation to use  

#------------------------------------------------------------------------------  
# LOCK MANAGEMENT  
#------------------------------------------------------------------------------  

#deadlock_timeout = 1s  
#max_locks_per_transaction = 64         # min 10  
                                        # (change requires restart)  
#max_pred_locks_per_transaction = 64    # min 10  
                                        # (change requires restart)  
#max_pred_locks_per_relation = -2       # negative values mean  
                                        # (max_pred_locks_per_transaction  
                                        #  / -max_pred_locks_per_relation) - 1  
#max_pred_locks_per_page = 2            # min 0  


#------------------------------------------------------------------------------  
# VERSION AND PLATFORM COMPATIBILITY  
#------------------------------------------------------------------------------  

# - Previous PostgreSQL Versions -  

#array_nulls = on  
#backslash_quote = safe_encoding        # on, off, or safe_encoding  
#default_with_oids = off  

# [《PostgreSQL 转义、UNICODE、与SQL注入》](201704/20170402_01.md)    
#escape_string_warning = on  
#lo_compat_privileges = off  
#operator_precedence_warning = off  
#quote_all_identifiers = off  
#standard_conforming_strings = on  
#synchronize_seqscans = on  

# - Other Platforms and Clients -  

#transform_null_equals = off  


#------------------------------------------------------------------------------  
# ERROR HANDLING  
#------------------------------------------------------------------------------  

#exit_on_error = off                    # terminate session on any error?  
#restart_after_crash = on               # reinitialize after backend crash?  


#------------------------------------------------------------------------------  
# CONFIG FILE INCLUDES  
#------------------------------------------------------------------------------  

# These options allow settings to be loaded from files other than the  
# default postgresql.conf.  

#include_dir = 'conf.d'                 # include files ending in '.conf' from  
                                        # directory 'conf.d'  
#include_if_exists = 'exists.conf'      # include file only if it exists  
#include = 'special.conf'               # include file  


#------------------------------------------------------------------------------  
# CUSTOMIZED OPTIONS  
#------------------------------------------------------------------------------  

# Add settings for extensions here  

64G内存,16核,SSD机器的配置例子

listen_addresses = '0.0.0.0'  
port = 1921  
max_connections = 2000  
superuser_reserved_connections = 13  
unix_socket_directories = '/tmp, .'  
unix_socket_permissions = 0700  
tcp_keepalives_idle = 60  
tcp_keepalives_interval = 10  
tcp_keepalives_count = 10  
shared_buffers = 8GB  
max_prepared_transactions = 2000  
maintenance_work_mem = 1GB  
vacuum_cost_delay = 0  
bgwriter_delay = 10ms  
bgwriter_lru_maxpages = 1000  
bgwriter_lru_multiplier = 10.0  
effective_io_concurrency = 0  
max_worker_processes = 128  
max_parallel_maintenance_workers = 8  
max_parallel_workers_per_gather = 8  
max_parallel_workers = 10  
wal_level = replica  
synchronous_commit = off  
full_page_writes = on  
wal_compression = on  
wal_buffers = 64MB  
wal_writer_delay = 10ms  
checkpoint_timeout = 30min  
max_wal_size = 16GB  
min_wal_size = 4GB  
checkpoint_completion_target = 0.1  
archive_mode = on  
archive_command = '/bin/date'  
max_wal_senders = 16  
max_standby_archive_delay = 300s  
max_standby_streaming_delay = 300s  
hot_standby_feedback = off  
max_logical_replication_workers = 10  

# 以下两个参数默认关闭, 分区表JOIN时使用并行JOIN
# [《PostgreSQL 11 preview - 分区表智能并行JOIN (已类似MPP架构,性能暴增)》](201802/20180202_02.md)  
enable_partitionwise_join = on  
enable_partitionwise_aggregate = on  

enable_parallel_hash = on  
enable_partition_pruning = on  
random_page_cost = 1.1  
effective_cache_size = 48GB  

# 如果使用LLVM编译的话,并且业务有复杂SQL的情况下。建议开启JIT。
# jit = on  
log_destination = 'csvlog'  
logging_collector = on  
log_directory = 'log'  
log_filename = 'postgresql-%a.log'  
log_truncate_on_rotation = on  
log_rotation_age = 1d  
log_rotation_size = 0  
log_min_duration_statement = 5s  
log_checkpoints = on  
log_connections = on  
log_disconnections = on  
log_error_verbosity = verbose     
log_line_prefix = '%m [%p] '  
log_lock_waits = on  
log_statement = 'ddl'  
track_activity_query_size = 2048  
autovacuum = on  
log_autovacuum_min_duration = 0  
autovacuum_max_workers = 8  
autovacuum_freeze_max_age = 1200000000  
autovacuum_multixact_freeze_max_age = 1400000000  
autovacuum_vacuum_cost_delay = 0ms  
# statement_timeout = 45min  
lock_timeout = 15s                                
idle_in_transaction_session_timeout = 60s  
vacuum_freeze_table_age = 1150000000  
vacuum_multixact_freeze_table_age = 1150000000  
# shared_preload_libraries = 'pg_stat_statements'  
deadlock_timeout = 1s  

pg_hba.conf 数据库防火墙配置模板

# TYPE  DATABASE        USER            ADDRESS                 METHOD  

# "local" is for Unix domain socket connections only  
local   all             all                                     trust  
# IPv4 local connections:  
host    all             all             127.0.0.1/32            trust  
# IPv6 local connections:  
host    all             all             ::1/128                 trust  
# Allow replication connections from localhost, by a user with the  
# replication privilege.  
local   replication     all                                     trust  
host    replication     all             127.0.0.1/32            trust  
host    replication     all             ::1/128                 trust  

# 禁止超级用户从远程连接  
host all postgres 0.0.0.0/0 reject  

# 应用连接配置:哪个用户,从哪里来,连接什么数据库。规则为使用何种认证方法,或拒绝?  
# TYPE  DATABASE        USER            ADDRESS                 METHOD  

# 如果不想挨个配置,可以使用如下配置,允许所有来源,通过任意用户访问任意数据库  
host all all 0.0.0.0/0 md5  

Flag Counter

digoal's 大量PostgreSQL文章入口

免费领取阿里云RDS PostgreSQL实例、ECS虚拟机

ch341a flashrom

解决flashrom 刷写时候必须大小和flash相等的问题

flashrom,ch341a 解决flashrom 刷写时候必须大小和flash相等的问题

我的ar9331买了得有几个月了。。一直卡在了 刷uboot这里 今天突然搜索到了 一个办法= = 没想到贼简单

size =  8388608 - bin的大小



sudo dd if=/dev/zero bs=1 count=size >> xxx.bin





sudo flashrom -p ch341a_spi -l layout.txt -i a -w xxx.bin
python,cython,linux

cython写的python包,带so/带ddl的包打包到pypi上,上传

cython写的python包,带so/带ddl的包打包到pypi上,上传

由于python版本的混乱- - 打包上传第三方包的教程也是坑的很 这里踩坑一下

cython 的程序 编译成so文件

from distutils.core import setup as cysetup
from Cython.Build import cythonize
cysetup(ext_modules = cythonize("lib.py",language_level=3),)

编译脚本

python ./setup.py build_ext  --inplace

让setup.py支持打包so文件

新建 MANIFEST.in

写入

recursive-include src *

这样打包的时候就会跟进保存

打包时候的setup.py

from setuptools import setup, find_packages


setup(
    name='pythonGroupMsg',
      version='0.0.1',
      description='This is a packet that broadcasts redis multiple queues',
      url='https://github.com/zhenruyan/pythonGroupMsg',
      author='zhenruyan',
      author_email='baiyangwangzhan@hotmail.com',
      license='WTFPL',
      packages=find_packages(),
      zip_safe=False,
      platforms=["linux"],
      long_description=open('README.rst').read(),
classifiers=[
        'Operating System :: OS Independent',
        'Intended Audience :: Developers',
        'Programming Language :: Python :: 3.7',
        'Topic :: Software Development :: Libraries'
    ],include_package_data=True,
          )

打包需要安装两个包

pip install wheel
pip install twine

打包脚本

python setup.py sdist build
python setup.py bdist_wheel --universal

上传

twine upload ./dist/*
broadcast multicast queue redis redisgroupmsg

redisGroupMsg redis队列组播广播

redisGroupMsg redis队列组播广播

redisGroupMsg redis队列组播广播

redis 向多个队列发送广播

最近要做一个类似聊天软件的东西,经过大量测试搞了这么一玩意儿

性能是py直接循环发送的13倍速度

pip install redisGroupMsg
from redisGroupMsg import redisMessage

r = redisMessage()

if __name__ == '__main__':
    for a in range(1,10):
        e = "id:"+str(a)
        # 添加到组
        # r.addGroup("test",e)
        #在组内广播
        # r.sendGroup("test",e)
        #从组内删除
        r.removeGroup("test",e)
java kafka linux python,發佈,訂閱,发布,订阅

kafka 发布订阅分组

kafka 发布订阅分组 發佈訂閱

惹不起的kafka

启动zookeeper

./zookeeper-server-start.sh ../config/zookeeper.pperties

启动kafka

./kafka-server-start.sh ../config/server.propertis

记得给配置文件配置分区数

代码

发布

from kafka import KafkaProducer
producer = KafkaProducer(bootstrap_servers='localhost:9092')

if __name__ == '__main__':
    for a in range(1,2):
        producer.send('chat',partition=1,value=b'some_message_bytes')
        producer.flush()

订阅分区

from kafka import KafkaConsumer

if __name__ == '__main__':

    consumer = KafkaConsumer("chat",bootstrap_servers=['localhost:9092'])
    consumer.subscribe(pattern="1")
    for msg in consumer:
        print(msg)

惹不起 惹不起 rabbitMQ 的2500个 队列才3g内存

kafka 一个topic (概念上的队列) 分区(打的tag) 2000个 竟然 需要40g 储存。。。

口区。。。。

我要去试试 nsq !!!

linux pub rabbitmq sub 廣播

RabbitMQ 路由绑定与广播

RabbitMQ 路由綁定與廣播

rabbitMQ 实现组播与广播

rabbitmq 分路由和队列

路由分三个模式

topic 模糊匹配 * 匹配一个字符 # 匹配多个字符

fanout 广播

direct 全匹配

生产者代码

# !/usr/bin/env python
import pika
import time
credentials = pika.PlainCredentials('guest','guest')



if __name__ == '__main__':
    # 声明queue

        connection = pika.BlockingConnection(pika.ConnectionParameters(
        '127.0.0.1', 5672, '/', credentials))
        # channel.exchange_declare()
        for a in range(1,1000000):
            channel = connection.channel()
            channel.queue_declare(queue="chat."+str(a),durable=False)
            channel.basic_publish(exchange='amq.topic',
                                  routing_key="chat.*",
                                  body='Hello World!')
            channel.queue_bind(exchange='amq.topic',
                               queue="chat."+str(a),
                               routing_key="chat.*")
            channel.basic_publish(exchange='amq.topic',
                                  routing_key="chat.*",
                                  body='Hello World!')
            channel.close()
            time.sleep(1)
            print(" [x] Sent 'Hello World!'")

        connection.close()

消费者代码

# _*_coding:utf-8_*_
import pika
import time
credentials = pika.PlainCredentials('guest','guest')
connection = pika.BlockingConnection(pika.ConnectionParameters(
    '127.0.0.1',5672,'/',credentials))


if __name__ == '__main__':
    while True:
        channel = connection.channel()
        channel.exchange_declare(exchange='topic_logs',type='topic')
        method_frame, header_frame, body = channel.consume("chat")
        if method_frame:
            print(method_frame, header_frame, body)
            channel.basic_ack(method_frame.delivery_tag)
        else:
            time.sleep(1)
            print('No message returned')