DB Ops
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Subtitle |
| |
| Alternative Title |
| |
| Title of Series | ||
| Number of Parts | 20 | |
| Author | ||
| Contributors | ||
| License | CC Attribution - NonCommercial - ShareAlike 3.0 Unported: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this | |
| Identifiers | 10.5446/19030 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language | ||
| Producer |
Content Metadata
| Subject Area | |
| Genre |
PGCon 201211 / 20
1
3
6
7
9
11
12
14
15
16
19
20
00:00
VideoconferencingMetropolitan area networkSoftwareOpen setMilitary operationSystem programmingOperator (mathematics)Medical imagingWeightPresentation of a groupDigital electronicsXMLUMLComputer animation
00:41
Military operationLevel (video gaming)Physical systemDisintegrationInclusion mapMobile WebSoftware developerCore dumpOpen setSoftwareCloud computingSigma-algebraArchaeological field surveyPort scannerSystem programmingOperator (mathematics)DatabasePhysical systemHigh availabilityOperating systemLevel (video gaming)Open sourceSoftwareCore dumpProfil (magazine)NumberProduct (business)Service (economics)Telecommunication1 (number)Characteristic polynomialWord2 (number)Point (geometry)Archaeological field surveySystems integratorLine (geometry)Group actionLevel of measurementTotal S.A.Reduction of orderPrisoner's dilemmaCondition numberForm (programming)Decision theoryOpen setPhysical lawFocus (optics)LinearizationMathematical analysisKey (cryptography)QuicksortData managementPattern languageUtility softwareVariable (mathematics)Interactive televisionAsynchronous Transfer ModeAreaPersonal digital assistantCellular automatonDivisorObservational studyVector spaceExecution unitComputer animation
08:20
OracleMilitary operationParameter (computer programming)InformationBefehlsprozessorComputer hardwareCodeTask (computing)Human migrationTemporal logicRadio-frequency identificationNumberGroup actionPoint (geometry)Procedural programmingTask (computing)Line (geometry)Software maintenanceStudent's t-testComputer programmingPhysical systemHypermedia1 (number)Product (business)Block (periodic table)Characteristic polynomialPersonal digital assistantElectronic mailing listDatabaseCategory of beingMereologyReduction of orderSet (mathematics)Right angleCASE <Informatik>Video gameInformationParameter (computer programming)StatisticsField (computer science)Menu (computing)Instance (computer science)Form (programming)Operator (mathematics)Similarity (geometry)Row (database)State observerMathematical optimizationBackupComputing platformUsabilityMultiplication signComputer animation
15:58
OracleKnotMathematical analysisRadio-frequency identificationData recoveryPort scannerMilitary operationData managementReal numberCellular automatonBlock (periodic table)Rule of inferenceFrictionSource codeOperator (mathematics)Point (geometry)Wind tunnelGenderDatabaseView (database)Product (business)CASE <Informatik>Direction (geometry)QuicksortSampling (statistics)CausalityAreaEndliche ModelltheorieComputer programmingMultiplication signDependent and independent variablesStatisticsLocal ringFrequencyVector spaceLoginOnlinecommunityMedical imagingMachine visionWebsiteMarginal distributionProcedural programmingObservational studyAsynchronous Transfer ModePhysical systemMultiplicationData recoveryStructural loadBackupMathematical analysisElectric generatorFile archiverInternet service providerState observerComputer fileAxiom of choiceBootingData managementQuery languageError messageBasis <Mathematik>Scaling (geometry)PlastikkarteComputer animation
23:37
Internet service providerMilitary operationBackupMetropolitan area networkData acquisitionPort scannerProduct (business)AreaSoftware developerCuboidOperator (mathematics)Scaling (geometry)Category of beingPhysical systemUtility softwareReplication (computing)SummierbarkeitShared memoryLine (geometry)PredictabilityComputer animation
24:26
Open setMilitary operationSystem programmingBackupDisintegrationPhysical systemSoftware developerNumberPoint (geometry)Physical systemMultiplication signVector potentialInformationScaling (geometry)GradientMereologyLine (geometry)Software developerData recoveryINTEGRALProcedural programmingBuildingMonster groupProduct (business)Presentation of a groupThresholding (image processing)Personal digital assistantReplication (computing)DatabaseInstallation artBackupSystems integratorShooting methodOperator (mathematics)Revision controlUtility softwareSystems engineeringHigh availabilityReduction of orderComputer animation
Transcript: English(auto-generated)
00:14
Okay, ladies and gentlemen, my name is Tetsu Sakata from NTT OSA Center Japan.
00:22
Today, I'll talk about PostgreSQL Operation Tools, which NTT has developed and released. After my talk, Mr. Sakamoto, please stand up. He's a member of our NTT OSA Center, too. He will give a brief demonstration after my talk.
00:41
Okay. In this session, shown here, I'll introduce NTT and NTT system characteristics, which has influence on our operation tools. And then, I'll talk about background needs to operation tools in our systems.
01:02
Finally, I'll show you an overview of NTT tools and to show our experience about operations.
01:21
Okay, about NTT. Before I talk about PostgreSQL things, I'll introduce our company shortly. NTT is acronym for Nippon, telegram, and telephone. And this is a telecommunication company. Its revenue is about 10 trillion yen, is about 100 billion dollars.
01:44
And number of employees is about 200,000. It is huge company. And some business facts, number of consolidated subsidiaries is about 500. And our user and subscriber is about 93 million.
02:05
It is almost the same as the Japanese population. And we run a system integration business also. And our customer is a large company and Japanese government agencies.
02:20
And their system is large and high availability. It is our company's short profile. So, then I'll talk about background to PostgreSQL operations in NTT. NTT started to use open source software for its production system since 2006.
02:46
This is an unexperienced trial because using OSS means we have to manage such software ourselves. So NTT established OSS Center, first mission is to reduce total cost of operation with open source software.
03:06
In other words, replacing proprietary software with open source software. To realize this mission, we support OSS Center support NTT group companies to OSS introduction.
03:32
For example, we provide some technical support like Q&A support, or technical consultation, or some operation manuals or operation tools.
03:45
And also, we develop and improve open source software. For example, some of our colleagues developed PostgreSQL core features. Today, we talk about peripheral tools to support PostgreSQL operations.
04:14
This is our PostgreSQL introduction target. This map shows the requirement to availability in the obsessive like this.
04:29
And the database size in the ordinate like this. The requirement to availability is shown the ratio of service availability.
04:44
For example, 99.99% around here and 5,900 around here and so on. The database size is ordinate and about 1 TB and 100 GB and so on.
05:01
When OSS Center established, we have surveys of production systems in the NTT group. And we find about 80% of them can be replaced or can be introduced PostgreSQL database.
05:27
We proceed into this gradually from smaller system to the large system, and the less reliable system to the more reliable system.
05:40
The present possible target is around about availability is about four or five lines and the database size is about a few TB. This is present around here, present introduction target.
06:09
So I'll summarize NTT system characteristics because that has influence on PostgreSQL operations. Our main production systems are telecommunication operation systems.
06:26
We call them OPS in short. And OPS characteristics is the database size is relatively large and it requires high availability and reliability because telecommunication system itself is reliable about 509 or more.
06:47
So the operation system should be reliable at the same.
07:01
When we started OSS promotion, promotion to our production systems, our production systems were thought to have some issues or some problems derived from depending on the proprietary database systems.
07:22
The first point is DBMS itself was very costly and this point will be easily improved using OSS databases. The second point, second issue is vendor lock-in.
07:44
I mean the word vendor lock-in implies a difficulty to switch database systems from proprietary ones to the OSS systems. To cope with this issue, NTT OSS center provides various technical services
08:06
including operation tools and so on. And to realize the effective introducing PostgreSQL to our production systems,
08:21
we have to understand what kind of characteristics our production engineers have. The first one is they are familiar with proprietary database, not PostgreSQL. Especially their operation skills are based on proprietary databases.
08:47
So they prefer or require a similar operation method to PostgreSQL. And the second one is they prefer simple and reliable operations
09:06
to use user-friendly manuals. Sometimes when the system, I skipped one point, sorry.
09:21
For example, I repeat from this point now. The second one, they prefer a simpler and reliable operation. For example, a set of database parameters is required.
09:43
PostgreSQL has a rich set of parameters. However, it sometimes makes things complicated. So we provide a set of recommended parameters to our production engineers. The second example of the preference to the simple operation
10:03
is they require simple operation tools with user-friendly manuals because they sometimes run the system by the other engineers who are not always enough skillful about PostgreSQL.
10:21
So the simple tools may help such engineers. There you go. Thank you.
10:56
It's the first time I have two microphones. Yeah, and one is not operating, sorry.
11:04
And the second point is, sorry. And then I talk about our user's needs when PostgreSQL is introduced. The first one is information.
11:40
And then I talk about our user's needs when PostgreSQL is introduced. The first one is information about performance is required. I have already talked about them last year, so I don't talk about them in detail.
12:03
And the second one is technical support, as I've already talked, and the entity also provides. The last one is needs for operation tools. As I will talk after, PostgreSQL has many third-party tools, but many of our engineers feel they don't have enough good usability
12:30
to solve their issues. So we have developed our own tools to solve these problems.
12:43
Another point is capability with the other tools. I mean two things. I mean two things.
13:00
In this point, our tools should work together with the other tools. In our production systems, many operation platforms like Hewlett-Packard OpenView or Hitachi JP1 are used to optimize operation efforts. I'll not talk this point in detail today.
13:23
However, I'd like to point it important for our useful operation tools. Thus far, I talked a background about PostgreSQL introduction and importance of operation assistance.
13:43
Then I'll introduce some entities and popular tools with episodes why we need to develop them. At first, I summarize what kind of operation tasks require operation tools and why. As you know, to operate the database properly,
14:04
we have to do many housekeeping tasks. For example, as an initial task, we have to load data into the database. In some cases, the data comes from the other database.
14:22
In other words, we have to migrate data from other databases. Such a kind of tasks are required. About daily tasks, we have to take back up daily or weekly and to cope with performance problems,
14:47
we have to observe PostgreSQL performance observation. In some systems, we have to get audit records and some maintenance tasks are required also.
15:10
As to mention, we need only operation tools.
15:27
We need only operation tools, but also operation know-hows. For example, taking a backup via PITR, we have to know its procedure.
15:43
The other example is observing PostgreSQL activities via statistics. As you know, PostgreSQL provides many database statistics, but many novice users don't know which statistics are useful to know database activities.
16:11
Now I'll talk about three episodes on operation tools. Why we have developed them. At first, about data loading, around 2005,
16:25
we were asked if PostgreSQL can be introduced to a production system. At that time, duration to data load became an issue because at that time, copy command takes two or three times longer duration
16:43
than Oracle's SQL loader did. We couldn't solve this problem, so PostgreSQL was unable to introduce me.
17:01
About copy command, we have another problem. Copy command, I think, is too restrictive. If an error occurs, the huge and whole data loading will be failed.
17:24
More flexible data handling is desirable in this case. We often consulted migrated data from other databases, and such a database has sometimes inverted data, too.
17:41
In such a case, we have to filter out inverted data from the data source and to correct them before loading. So we need first and flexible data load at that time. The second episode is database observation.
18:02
AT2 assessment provides technical support in which we help troubleshooting. Some troubles about performance are difficult to solve because the reproduction of the trouble often takes a long time.
18:21
So this problem is generic to other databases. When using Oracle, engineers correct performance statistics and the data are used for post-mortem analysis. So we think if PostgreSQL had the same kind of data,
18:41
it should be very useful. Such a tool is a kind of data loader that should correct performance statistics. As you know, PostgreSQL statistics are useful to know these problems.
19:16
The tool should correct which queries are issued
19:21
and some of them are slow and how they are planned or some resource consumption problem is there or not. So PostgreSQL statistics are useful to know these problems, but with the data to fix them requires a kind of PostgreSQL internals know-how.
19:48
Such a kind of know-how is not known by our production engineers. And the third point, third episode is about the backup.
20:05
Since PostgreSQL 8.0, PITR and incremental backup has been furnished. Now this method is first choice for database backup in OSC Center. However, its procedures are relatively complex
20:23
and the user should know many things. For example, which archive logs are required to recover database. Continuing taking backups,
20:43
you should have many generation of backup data so when we can expire a huge bunch of card log files. And the third point is how can we recover database with backup data.
21:07
For example, how to use the latest log file to be used while recovering. So we often ask such questions from our customers or production engineers
21:23
and especially recovery procedure is relatively complex and sometimes they are wrongly used to destroy database files. So we think more useful backup tools and recovery tools are required.
21:51
I have introduced three episodes on database operation tools. Finally, we have developed these tools. Before I demonstrate them, I'd like to point out some insights from our experiences.
22:07
The first point is only user knows real problems. For example, data loading sometimes includes embedded data or database activity data can be used for post-mortem analysis.
22:29
And backup tools should manage multiple generation of backup files.
22:44
Not only simple operations, but operations know how are required. In other words, we need to provide basis for reliable operation as our know-how.
23:06
And thus far, I have mentioned some insights. I will summarize them. Users should have much operation know-how so user community can share them and they should improve database operation, which makes the tool much better.
23:28
Such things have possible scale introduction to production systems, I think.
23:41
Entity has developed some utility tools to promote possible scale for production systems. This picture shows utility tools that are classified into three categories. The first one is Operation 8. The second one is Data Replication and Data Sharing.
24:02
The third one is Development 8. By now, we focus on operation area and data replication area. Entity-made tools are shown by red boxes. These tools are essential to most of our production systems now.
24:30
Among many tools, especially these four tools, must have items for our system integration engineers.
24:41
The first one is PgStatsInfo. It is used for logging activity data to shoot performance troubles. The second one is PgAlman to assist in taking database backup and database recovery
25:03
because it simplifies backup and recovery procedures. The third and fourth one is PgReall and PgParkload. These are used to reduce operation efforts.
25:23
After, we will give a brief demonstration of these four tools. Finally, I will show you Entity's introduction to possible scale of production systems and simultaneously our development of utility tools.
25:45
We established this in 2006. In this year, only one possible scale installation was introduced. This year, we released PgParkload.
26:02
Next year, we released the early version of PgRex, which is a kind of database replication for the high availability system. The turning point is 2009.
26:22
This year, a number of possible scale introductions were increased notably. And we released PgAlman and PgStatsInfo tools
26:41
to assist production system engineers' database operation. And finally, last year, we had introduced about 90 possible scale installations into entity production systems.
27:03
That is my presentation. And then Mr. Sakamoto will give a demonstration about four tools I have talked about.