Getting in touch with Michigan DBA Drew Zhu
- Prepare initial email, list of questions:
- Context:
We are currently running a sakai pilot where we have up to 400 concurrent connections. This Fall, we are making sakai available to the whole campus where we initially expect up to 2000? concurrent users and in the long run up to 4000?. We would like to learn from you, how you configured and tuned your sakai oracle instance to handle such a load. If you have time, we would like to setup a conference call with you to discuss the following items and any information that you could provide that we haven't thought of.
- Database Server Info
OS: Linux RHEL 4.0 2.6.9-55.0.2.ELsmp #1
RAM: 16GB
Oracle: 10.2.0.3 Standard Edition
- Questions:
1. Memory management configured to auto. Is there any specific memory component needed specific values.
2. We use dedicated server process, Is Shared server recommended for SAKAI.
3. Is there any tools used for monitoring / tuning
4. IO is to SAN partitions and OFA was used. Any recommended file distribution.
5. Any Documentation on building database instance for SAKAI.
6. Any specific INIT parameters to look into.
7. What is the base line for load monitoring.
...
- Sent mail to Drew on Aug. 9, 2007:
Drew, Linda told us in an earlier email that we can get in touch with you regarding some questions that we have regarding Sakai oracle DB setup/tuning. We are currently running a Sakai pilot where we have up to 400 concurrent connections. This Fall, we are making Sakai available to the whole campus where we initially expect up to 2000 concurrent users and in the long run up to 4000. We would like to learn from you, how you configured and tuned your sakai oracle instance to handle such a load. If you have time, we would like to setup a conference call with you to discuss the following items and any information that you could provide that we haven't thought of. Our DB environment is: OS: Linux RHEL 4.0 2.6.9-55.0.2.ELsmp #1 RAM: 16GB Oracle: 10.2.0.3 Standard Edition Questions: 1. Memory management configured to auto. Is there any specific memory component needed specific values. 2. We use dedicated server process, Is Shared server recommended for SAKAI. 3. Is there any tools used for monitoring / tuning 4. IO is to SAN partitions and OFA was used. Any recommended file distribution. 5. Any Documentation on building database instance for SAKAI. 6. Any specific INIT parameters to look into. 7. What is the base line for load monitoring. If we can discuss these questions during a phone conference, that would be great, otherwise, we have to correspond via email, Please let me know. Thank you for your time. Best, -- Thomas Amsler
Drew responded
I don't know if you are using 32 or 64 bit machine. If 64 bit, the sga_target can be larger. I set the sga_target equal to sga_max_size, which is 6.5 gigs in our case. The pga_aggregate_target can be much smaller. We used to use 512M for that parameter and now we use 256M and it's enough in our case. The memory saved by this can be reallocated to SGA (considering multiple processes, it can be gigs).
You can add more db_writer_processes to 2 or 4. That may help write intensive process.
By default, optimizer_mode is set ALL_ROWS. You can try FIRST_ROWS (also in the remote database where MVs are in) to see is this can help the MVs. Sometimes, getting all rows may consume a lot of resources for queries retuning many many rows. This parameter can be adjusted by session so that it won't affect the whole database. You can also test the effect of this parameter by simply adding a hint in your MV queries /+FIRST_ROWS/.
Do you have the queries or the MV DDL for the MVs that causing the problems? I may be able to test the MVs here in our site and see what can be tuned.