In The Godfather: Part II Frankie Pentangeli tells Tom a story about the Roman Empire and then he kills himself. Why exactly does he do that?
To avoid retribution for his betrayal of the Corleone family falling on Frankie's children & family.
Note...it was Tom who told the story, not Frank.
After the [Senate] hearing, consigliere Tom Hagen (Robert Duvall) visits Frank in custody. Hagen tells Frank that he did the right thing by recanting. Tom and Pentangeli talk nostalgically about the good old days when the Corleone Family, as Pentangeli says, "was like the Roman Empire." Hagen tells a story about what would happen to traitors during those Roman days, implying that the correct thing for Pentangeli to do now is to kill himself. Hagen tells Pentangeli if he accepts responsibility for turning on the Corleone family and kills himself, Frank's family will always be taken care of, just as the families of confessed traitors in the Roman days who killed themselves were allowed to keep all of their possessions. He thanks Hagen, returns to his assigned quarters, and despite the presence in the other room of his federal guards, commits suicide by slitting his wrists while taking a bath.
The finished film leaves unclear exactly what about his brother [Vincenzo]'s presence [at the hearings] motivated Frank to change his story. The final film only states that Vincenzo is a powerful and ruthless Mafia chieftain in Sicily.
An early draft of the film's script explains that Vincenzo, shocked that Frankie is about to break his blood oath and betray the Corleones to government authorities, attends the hearing to remind Frankie that he must not break the Mafia's code of silence, omertà. His brother's presence [at the Senate hearing], as well as the stare they exchanged, serves as a threat that if Frankie follows through with his planned testimony, retribution will be taken against his children, who are living in Sicily under Vincenzo's guardianship.
A second reason is that seeing his brother makes him feel ashamed for breaking the omertà.
So while the avoidance of retribution for his family is the practical reason, the deeper, psychological reason comes from a feeling of deep shame that rekindles his sense of honor.
Respect, a form of honor, is an nearly all important to Sicilian Mafiosi. "Mafiosi are known among themselves as 'men of honour' or 'men of respect'."
I've read the book and seen the film many times and my feeling is the psychological aspect is more of a factor than the implied threat against his children. For all of the action, the films are deeply psychological, which is an element of their greatness.
Upon the non-verbal communication with his brother in the court room (someone he absolutely trusts) he realized that it was Hyman Roth that tried to have in killed and not Michael. Upon this realization, he recants on his statements against Michael that would have put him in prison for perjury (lying under oath) Knowing that now Frankie had broken his FBI deal, and was looking at a life sentence, he cuts a deal with Tom as so his family and possible Willy Cici's family could keep their fortunes as Frankie is the head of his own New York Crime Family.