You should tip in Pesos because they have to take the USDs and have them exchanged. Also, many people from the USA give out torn, worn, marked, or outdated bills that are hard to exchange so giving Pesos are best. But nobody there will refuse the USDs as most of the help is grateful to get extra money. But Pesos are better to deal with.

You should tip just like you do in your home country. Tip 15-20% of the bill. Housekeepers daily since they could change daily.

You can get Pesos from the ATMs in Cancun, order them from your bank online with mail delivery or go into a branch, but call first to make sure they have enough on hand..

As a side note, the USD has lost value compared to the Mexican Peso so tipping in the USDs is giving a tip less than it should be plus they have to pay currency exchange fees. Tip in Pesos.