產品模組
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
*
*
Bi server 3.10.0-stable
Bi server 4.5.0-stable
安裝
安裝 jdk1.6 or jdk1.7
些改環境變數Java_home and Path
Add the line: export JAVA_HOME=”/usr/lib/jvm/java-6-openjdk”
修改start-pentaho
set JRE_HOME=C:\Program Files\Java\jre6
|
調整JVM參數 C:\biserver-ce-3.10.0-stable\biserver-ce\
set CATALINA_OPTS=-Xms512m -Xmx512m -XX:MaxPermSize=256m -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000
|
根據 1/2 原則，我們通常要保證-Xmx 參數取值不要超過實體記憶體的1/2。比如，物理機
器記憶體為2G，則-Xmx 取值最好不要超過1G（2G*1/2），而-Xms 取值最好不要低於-Xmx
的1/2，即512m 左右。不過，不同生產環境的差異性太大，建議用戶能夠在生產前進行嚴
格的壓力及調優測試，並靈活調整JVM 參數。
調整Apache Tomcat 參數C:\biserver-ce-3.10.0-stable\biserver-ce\tomcat\conf\server.zml
<Connector port="8080" maxHttpHeaderSize="8192"
maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" redirectPort="8443" acceptCount="100"
connectionTimeout="20000" disableUploadTimeout="true" />
|
通常，我們需要調整maxThreads、minSpareThreads、maxSpareThreads、acceptCount 等
參數取值。大部分情況下，可以考慮將它們的取值設置成默認的2 倍左右，即maxThreads
設置成300、minSpareThreads 設置成50、maxSpareThreads 設置成150、acceptCount 設置成
200。
注意，不同生產環境的差異性較大，建議用戶能夠在上線前進行嚴格的壓力及調優測試，
並靈活調整Apache Tomcat 參數，畢竟參數不是越大越好，因為這些參數取值要同硬體能力
相匹配，否則得不償失。
將Pentaho BI 服務器的資料遷移到PostgreSQL 數據庫
1.將驅動(postgresql-9.1-901.jdbc4.jar)複製到C:\biserver-ce-3.10.0-stable\administration-console\lib、C:\biserver-ce-3.10.0-stable\administration-console\jdbc、C:\biserver-ce-3.10.0-stable\biserver-ce\tomcat\lib、C:\biserver-ce-3.10.0-stable\biserver-ce\tomcat\webapps\pentaho\WEB-INF\lib
2.創建hibernate and quartz 資料庫，
hibernate 執行C:\biserver-ce-3.10.0-stable\biserver-ce\data\postgresql\create_repository_postgresql.sql
drop database if exists hibernate;
drop user if exists hibuser;
CREATE USER hibuser PASSWORD 'password';
CREATE DATABASE hibernate WITH OWNER = hibuser ENCODING = 'UTF8' TABLESPACE = pg_default;
GRANT ALL PRIVILEGES ON DATABASE hibernate to hibuser;
\connect hibernate hibuser
|
C:\biserver-ce-3.10.0-stable\biserver-ce\data\postgresql\ create_sample_datasource_postgresql.sql
\connect hibernate hibuser ---刪除
|
quartz 執行
C:\biserver-ce-3.10.0-stable\biserver-ce\data\postgresql\ create_quartz_postgresql.sql
---以下刪除
drop database if exists quartz;
drop user if exists pentaho_user;
CREATE USER pentaho_user PASSWORD 'password';
CREATE DATABASE quartz WITH OWNER = pentaho_user ENCODING = 'UTF8' TABLESPACE = pg_default;
GRANT ALL ON DATABASE quartz to pentaho_user;
\connect quartz pentaho_user
|
3.修改 context.xml 把以前的mark<!-- -->掉
C:\biserver-ce-3.10.0-stable\biserver-ce\tomcat\webapps\pentaho\META-INF\
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/pentaho" docbase="webapps/pentaho/">
<!-- <Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="hibuser" password="password"
driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/hibernate"
validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES" />
<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="pentaho_user" password="password"
driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/quartz"
validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES"/>
-->
<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="hibuser" password="password"
driverClassName="org.postgresql.Driver" url="jdbc:postgresql://192.168.2.149:5432/hibernate"
validationQuery="select 1" />
<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="pentaho_user" password="password"
driverClassName="org.postgresql.Driver" url="jdbc:postgresql://192.168.2.149:5432/quartz"
validationQuery="select 1"/>
</Context>
|
4.修改 hibernate-settings.xml
C:\biserver-ce-3.10.0-stable\biserver-ce\pentaho-solutions\system\hibernate
<config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>
|
5.修改 postgresql.hibernate.cfg.xml
C:\biserver-ce-3.10.0-stable\biserver-ce\pentaho-solutions\system\hibernate
<property name="connection.username">postgres</property>
<property name="connection.password">!QAZ2wsx</property>
|
6.修改 applicationContext-spring-security-hibernate.properties
C:\biserver-ce-3.10.0-stable\biserver-ce\pentaho-solutions\system
#jdbc.driver=org.hsqldb.jdbcDriver
#jdbc.url=jdbc:hsqldb:hsql://localhost:9001/hibernate
#jdbc.username=hibuser
#jdbc.password=password
#hibernate.dialect=org.hibernate.dialect.HSQLDialect
jdbc.driver=org.postgresql.Driver
jdbc.url=jdbc:postgresql://192.168.2.149:5432/hibernate
jdbc.username=postgres
jdbc.password=!QAZ2wsx
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
|
7.修改 quartz.properties
C:\biserver-ce-3.10.0-stable\biserver-ce\pentaho-solutions\system\quartz
#org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
|
一、修改Pentaho 資料庫鏈結
3. Set the spring-security to the MySQL database
Navigate up one folder level, and edit the file:
路徑：/Pentaho/biserver-ce/Pentaho-solutions/system/
(1)applicationContext-spring-security-jdbc.xml
4. Modify the file that list the jndi (datasources) connections Edit:
(1)Pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
5.jdbc.properties
(1)/Pentaho/biserver-ce/tomcat/conf/Catalina/localhost/pentaho.xml
二、Linux 需要修改讓執行檔可執行
Sudo chmod +x *.sh Pentaho/
三、修改Java 環境
(1)biserver-ce/start-pentaho.bat or start-pentaho.sh
default：set CATALINA_OPTS=-Xms256m -Xmx768m -XX:MaxPermSize=256m -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000
After：set CATALINA_OPTS=-Xms2048m -Xmx2048m -XX:MaxPermSize=512m -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000
修改記憶體
(2)biserver-ce\pentaho-solutions\system\mondrian\mondrian.properties
Default：
mondrian.result.limit=5000000
mondrian.rolap.queryTimeout=300
After：
mondrian.result.limit=20000000
mondrian.rolap.queryTimeout=0
四、加入.jar
Tomcat/lib 加入postgres1-9.1-901.jdbc4.jar、jtds-1.2.5.jar、mysql.jar
Administrator/jdbc/ 加入postgres1-9.1-901.jdbc4.jar、jtds-1.2.5.jar
五、還原quartz
Biserver-ce/data/postgresql/create_quartz_postgresql.sql 以下內容刪掉
--
-- note: this script assumes pg_hba.conf is configured correctly
--
-- \connect postgres postgres
drop database if exists quartz;
drop user if exists pentaho_user;
CREATE USER pentaho_user PASSWORD 'password';
CREATE DATABASE quartz WITH OWNER = pentaho_user ENCODING = 'UTF8' TABLESPACE = pg_default;
GRANT ALL ON DATABASE quartz to pentaho_user;
\connect quartz pentaho_user
begin;
drop table if exists qrtz_job_listeners;
drop table if exists qrtz_trigger_listeners;
drop table if exists qrtz_fired_triggers;
drop table if exists qrtz_paused_trigger_grps;
drop table if exists qrtz_scheduler_state;
drop table if exists qrtz_locks;
drop table if exists qrtz_simple_triggers;
drop table if exists qrtz_cron_triggers;
drop table if exists qrtz_blob_triggers;
drop table if exists qrtz_triggers;
drop table if exists qrtz_job_details;
drop table if exists qrtz_calendars;
|
六、Publish
修改/pentaho-solutions/system/publisher_config.xml
<publisher-config>
<publisher-password>123456</publisher-password>
</publisher-config>
|
七、修改quartz.properties
#org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
|
八、jpivor.jar
上傳jpivor.jar to tomcat/webapps/pentaho/WEB-INF/lib
九、上傳datasource.xml
修改加入pentaho-solutions/system/olap/datasources.xml
<Catalog name="AnalysisCube"> <DataSourceInfo>Provider=mondrian;DataSource=bi_postgres;EnableXmla=False</DataSourceInfo>
<Definition>solution:/voir/analysis/AnalysisCube-Schema.xml</Definition>
</Catalog>
|
十、create/voir/analysis
創資料夾 pentaho-solutions/voir/analysis
上傳AnalysisCub-Schema.xml 到創立資料夾/voir/analysis
七、修改
上傳 tomcat/webapps/pentaho and tomcat/webapps/pentaho and /pentaho-solutions/saiku
取代原本，
並且刪除
Tomcat/conf/Catalina 資料夾
修改
\biserver-ce-3.10.0-stable\biserver-ce\pentaho-solutions\system\olap\datasources.xml
<?xml version="1.0" encoding="UTF-8"?>
<DataSources>
<DataSource>
<DataSourceName>Provider=Mondrian;DataSource=Pentaho</DataSourceName>
<DataSourceDescription>Pentaho BI Platform Datasources</DataSourceDescription>
<URL>http://localhost:8080/pentaho/Xmla?userid=joe&password=password</URL>
<DataSourceInfo>Provider=mondrian</DataSourceInfo>
<ProviderName>PentahoXMLA</ProviderName>
<ProviderType>MDP</ProviderType>
<AuthenticationMode>Unauthenticated</AuthenticationMode>
<Catalogs>
<Catalog name="SteelWheels">
<DataSourceInfo>Provider=mondrian;DataSource=SampleData</DataSourceInfo>
<Definition>solution:steel-wheels/analysis/steelwheels.mondrian.xml</Definition>
</Catalog>
<Catalog name="SampleData">
<DataSourceInfo>Provider=mondrian;DataSource=SampleData</DataSourceInfo>
<Definition>solution:steel-wheels/analysis/SampleData.mondrian.xml</Definition>
</Catalog>
<Catalog name="AnalysisCube">
<DataSourceInfo>Provider=mondrian;DataSource=bi_postgres;EnableXmla=False</DataSourceInfo>
<Definition>solution:/voir/analysis/AnalysisCube-Schema.xml</Definition>
</Catalog>
</Catalogs>
</DataSource>
</DataSources>
|
設定
自動執行(Edit analysis to disable and enable auto run query) Settings.js
C:\soft\biserver-ce-3.10.0-stable\biserver-ce\pentaho-solutions\system\saiku\ui\js\saiku
關閉
'saiku.olap.query.automatic_execution': 'false',
開啟
'saiku.olap.query.automatic_execution': 'true',
|
問題:SQLException: invalid schema name: public in statement [select "bi_closing_openinig"."f_brncde" as "c0" from "public"."bi_closing_openinig
A:檢查biserver-ce-3.10.0-stable-local\biserver-ce\pentaho-solutions\system\olap\datasources.xml
<Catalog name="AnalysisCube">
<DataSourceInfo>Provider=mondrian;DataSource=bi_postgres;EnableXmla=False</DataSourceInfo>
<Definition>solution:/voir/analysis/AnalysisCube-Schema.xml</Definition>
</Catalog>
|
修改Pentaho 資料庫鏈結
3. Modify system connections to MySql: hibernate and quartz
Use the file file navigator ‘nautilus’ (on the desktop menu: Places->Personal folder) to the following folder:
/Pentaho/bi-server/tomcat/webapps/pentaho/META-INF/
Note that this are the tomcat (web server) files. Edit (right click->open with->gedit):
*context.xml
4. Redirect Hibernate to MySql
Navigate to:
/Pentaho/bi-server/pentaho-solutions/system/hibernate/
This are the Pentaho config files. Open with gedit:
*hibernate-settings.xml
Save the file. In gedit open in the same folder:
*mysql5.hibernate.cfg.xml
5. Set the spring-security to the MySQL database
We’ll change the spring security files (until 3.5 the technology was ‘acegi’, but on 3.6 they rename it). Navigate up one folder level, and edit the file:
/Pentaho/biserver-ce/Pentaho-solutions/system/
*applicationContext-spring-security-hibernate.properties
*applicationContext-spring-security-jdbc.xml
6. Modify the file that list the jndi (datasources) connections
Edit:
*Pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
/Pentaho/biserver-ce/tomcat/conf/Catalina/localhost/pentaho.xml
*Lim HongShin: jdbc.properties
Perforenece Tuning
Set Tomcat
a.vi Tomcat/bin/catalina.sh
JAVA_OPTS="-Xms2304m -Xmx2304m -XX:MaxPermSize=512m -Djava.rmi.server.hostname=192.168.0.101"
|
Set tomcat memory
Min & max
-Xms = min
-Xmx = max
MaxPermSize is Java Setting
All together is 2304+542 MB memory allocated for this tomcat
b.vi Tomcat/conf/server.xml
<Executor name="tomcatThreadPool" namePrefix="catalina-exec-" maxThreads="3000" minSpareThreads="4"/>
|
Set maxThreads =3000
第59行 設定Tomcat最大連接數3000
Set System
- ipcs –l
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 67108864
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1
------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 32
semaphore max value = 32767
------ Messages: Limits --------
max queues system wide = 16
max size of message (bytes) = 65536
default max size of queue (bytes) = 65536
b.vi /etc/sysctl.conf
kernel.sem=1250 2560000 100 1024
|
第31行加入上面
c./sbin/sysctl –p
d.ipcs –l
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 67108864
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1
------ Semaphore Limits --------
max number of arrays = 1024
max semaphores per array = 1250
max semaphores system wide = 2560000
max ops per semop call = 100
semaphore max value = 32767
------ Messages: Limits --------
max queues system wide = 16
max size of message (bytes) = 65536
default max size of queue (bytes) = 65536
Set Postgresql
a.vi data/postgresql.conf
shared_buffers = 1024MB
|
Default is 32MB setting total RAM 1/4 memory
沒有留言:
張貼留言