Why did Frank Pentangeli kill himself in Godfather 2?

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.

From Wikipedia

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'."

Honor is also a factor in the suicides of Ancient Roman noblemen—it is a symbolic act that can expiate shame (see Metellus Scipio). It is used as a form of honorable protest (see Cato the Younger).

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.

Roth had played Frankie against Michael with a feigned assassination attempt, as he knew Frankie was angry at Michael for banning him from moving against the Rosato brothers. This got Frankie scared enough for his life to break omerta by talking to the FBI. This act itself already means he is dead from the perspective of any of the mafia families, and could only hope to live under an FBI programme with a false identity.

Michael responded by bringing Frankie's brother in the courtroom signifying they have his family. This shows that they don't need to get him, they'll get his family instead. At this point Frankie cuts a deal with the Corleones to end his own life for the sake of his family to keep their fortune instead of being wiped out.

Frankie is portrayed as a crude man with only superficial respect to the mafia families and no deeper understanding. This made him somebody who could be played to make fatal mistakes. In an earlier scene, Frankie, in a clear and obvious manner, shows his anger and disrespect to Michael because he is banning him from defending his own territory. In this scene, Frankie was just acting drunk, the crude man he is, but this behaviour was a display of disrespect, and it had the consequence that his angry and dissatisfied feelings were revealed. This scene is played very blatantly to bring out it has a deeper meaning, which is that this evidence of disloyalty was then obviously leaked to Roth, who used it to play his card: he faked as if Michael was trying to kill Frankie to punish his disloyalty. And this broke their relations and turned out fatal to Frankie: Roth managed to weaken their mafia family, which, in turn, makes the Corleones more reliant on the deal they are currently negotiating in Cuba.