The Oxford English Dictionary says:
Murphy, n. colloq. Now rare. = Morpheus n. Frequently in in the arms of Murphy: asleep.
Morpheus was the Greek god of dreams; hence a metonym for sleep. The OED's citations include:
1750 T. Smollett Roderick Random (ed. 3) I. xiv. 110 When Murfy sends his puppies to the heys of slipping mortals.
1823 M. Wilmot Let. 3 Sept. (1935) 194 I am cheating Murphy, who is making me yawn till my jaws are almost dislocated.
1841 F. Marryat Poacher xi We dropped into the arms of Murfy.
Note that these citations all precede the founding of Murphy's Brewery in Cork in 1856, so it seems doubtful that there was originally any connotation of drunkenness in the phrase.
In episode 2 ("Nestor") of James Joyce's Ulysses (1922) we meet Gumley, night-watchman for Dublin corporation, who is apt to sleep on the job:
Slightly disturbed in his sentrybox by the brazier of live coke the watcher of the corporation stones who, though now broken down and fast breaking up, was none other in stern reality than the Gumley aforesaid, now practically on the parish rates, given the temporary job by Pat Tobin in all human probability from dictates of humanity knowing him before shifted about and shuffled in his box before composing his limbs again in to the arms of Morpheus, a truly amazing piece of hard lines in its most virulent form on a fellow most respectably connected and familiarised with decent home comforts all his life who came in for a cool 100 pounds a year at one time which of course the doublebarrelled ass proceeded to make general ducks and drakes of.
Much later on, in episode 16 ("Eumaeus") we meet Gumley again:
Anyhow they passed the sentrybox with stones, brazier etc. where the municipal supernumerary, ex Gumley, was still to all intents and purposes wrapped in the arms of Murphy, as the adage has it, dreaming of fresh fields and pastures new.