MySQL Forums
Forum List  »  InnoDB

Re: MySQL 5.5 hangs frequently in production environment
Posted by: Murtaza Doctor
Date: June 30, 2014 05:22AM

Hi Rick,

On many occassions during the hang incident, we are not able to login into database. If already logged in, queries cannot be executed.

However, in one such incident we were able to obtain the processlist which has been listed below for your reference (ID,command, time and status columns). Please let me know if you need any other information.

Thanks.

=========
Show processlist
==========

Id Command Time State
1 Daemon 877 Waiting for next activation
3653 Sleep 355
3654 Execute 378 Sending data
3660 Execute 372 statistics
3682 Execute 379 Sending data
3684 Prepare 369 Opening tables
3686 Sleep 377
3689 Execute 379 Sending data
3692 Sleep 372
3698 Query 378 statistics
3701 Sleep 370
3712 Query 375 statistics
3714 Query 373 statistics
3715 Query 370 Opening tables
3722 Execute 378 Sending data
3724 Query 365 Opening tables
3736 Query 372 statistics
3743 Sleep 372
3744 Execute 373 Sorting result
3746 Prepare 369 Opening tables
3748 Prepare 368 Opening tables
3749 Sleep 316
3750 Sleep 377
3754 Prepare 357 Opening tables
3760 Execute 372 statistics
3772 Sleep 372
3774 Query 359 Opening tables
3775 Execute 377 statistics
3776 Execute 378 Sending data
3777 Query 328 Opening tables
3778 Execute 382 Sending data
3793 Query 372 statistics
3795 Execute 373 statistics
3802 Execute 396 Sending data
3803 Execute 374 statistics
3804 Execute 391 Sending data
3805 Sleep 328
3806 Execute 390 Sending data
3807 Query 355 Opening tables
3808 Sleep 371
3809 Execute 373 statistics
3810 Query 316 Opening tables
3811 Sleep 330
3812 Prepare 370 Opening tables
3813 Query 377 statistics
3814 Prepare 359 Opening tables
3815 Sleep 342
3816 Sleep 375
3817 Query 371 statistics
3818 Sleep 377
3819 Query 330 Opening tables
3820 Prepare 319 Opening tables
3821 Query 342 Opening tables
3822 Query 377 statistics
3823 Execute 377 statistics
3824 Prepare 320 Opening tables
3825 Sleep 377
3826 Prepare 367 Opening tables
3827 Query 315
3828 Prepare 324 Opening tables
3829 Sleep 315
3830 Query 377 statistics
3831 Query 377 statistics
3832 Query 370 Opening tables
3833 Execute 377 statistics
3834 Sleep 370
3835 Sleep 365
3836 Sleep 355
3838 Prepare 369 Opening tables
3839 Execute 377 statistics
3840 Sleep 375
3841 Execute 372 statistics
3842 Sleep 375
3843 Sleep 372
3844 Query 372 statistics
3845 Sleep 359
3846 Execute 372 statistics
3847 Execute 377 statistics
3848 Prepare 363 Opening tables
3849 Query 355
3850 Execute 382 Sending data
3851 Query 375 statistics
3852 Query 375 statistics
3853 Prepare 369 Opening tables
3854 Execute 371 statistics
3855 Query 377 statistics
3856 Sleep 372
3857 Sleep 372
3858 Query 372 statistics
3859 Execute 373 statistics
3860 Sleep 373
3861 Sleep 370
3862 Query 372 statistics
3863 Execute 375 Sorting result
3864 Query 372 statistics
3865 Sleep 377
3866 Query 369
3867 Execute 372 statistics
3868 Execute 392 Sending data
3869 Execute 378 statistics
3870 Execute 397 Sending data
3871 Prepare 370 Opening tables
3872 Sleep 369
3873 Execute 391 Sending data
3874 Query 370
3875 Sleep 169
3876 Sleep 169
3877 Sleep 169
3878 Sleep 169
3879 Sleep 169
3880 Sleep 169
3881 Sleep 169
3882 Sleep 169
3883 Sleep 169
3884 Sleep 169
3885 Execute 376 statistics
3886 Execute 382 Sending data
3887 Sleep 370
3888 Sleep 370
3889 Sleep 371
3890 Query 370
3891 Query 371 Opening tables
3892 Query 369
3893 Sleep 367
3894 Sleep 367
3895 Query 367
3896 Prepare 367 Opening tables
3897 Query 369
3898 Sleep 368
3899 Prepare 367 Opening tables
3900 Query 368
3901 Sleep 369
3902 Query 367
3903 Query 365
3904 Prepare 364 Opening tables
3905 Prepare 365 Opening tables
3906 Sleep 365
3907 Sleep 367
3908 Sleep 367
3909 Prepare 367 Opening tables
3910 Query 366
3911 Query 367
3912 Prepare 366 Opening tables
3913 Query 366
3914 Sleep 365
3915 Sleep 366
3916 Query 365
3917 Prepare 364 Opening tables
3918 Prepare 361 Opening tables
3919 Prepare 364 Opening tables
3920 Prepare 363 Opening tables
3921 Prepare 364 Opening tables
3922 Sleep 359
3923 Prepare 359 Opening tables
3924 Query 359
3925 Prepare 360 Opening tables
3926 Sleep 360
3927 Prepare 359 Opening tables
3928 Prepare 357 Opening tables
3929 Prepare 355 Opening tables
3930 Prepare 359 Opening tables
3931 Sleep 354
3932 Query 359
3933 Query 356
3934 Sleep 356
3935 Sleep 353
3936 Prepare 355 Opening tables
3937 Sleep 355
3938 Prepare 353 Opening tables
3939 Query 355
3940 Query 355
3941 Sleep 355
3942 Query 353
3943 Query 354
3944 Prepare 351 Opening tables
3945 Prepare 352 Opening tables
3946 Sleep 353
3947 Prepare 349 Opening tables
3948 Prepare 347 Opening tables
3949 Query 353
3950 Query 351
3951 Sleep 351
3952 Prepare 344 Opening tables
3953 Sleep 353
3954 Prepare 352 Opening tables
3955 Prepare 344 Opening tables
3956 Query 353
3957 Query 350
3958 Prepare 350 Opening tables
3959 Sleep 351
3960 Query 351
3961 Sleep 350
3963 Sleep 348
3964 Sleep 348
3965 Query 348
3966 Sleep 343
3967 Query 348
3968 Query 340
3969 Prepare 346 Opening tables
3970 Sleep 340
3971 Prepare 344 Opening tables
3972 Prepare 340 Opening tables
3973 Query 344
3974 Sleep 338
3975 Prepare 344 Opening tables
3976 Prepare 338 Opening tables
3977 Prepare 342 Opening tables
3978 Prepare 337 Opening tables
3979 Prepare 343 Opening tables
3980 Prepare 343 Opening tables
3981 Prepare 340 Opening tables
3982 Query 343
3983 Prepare 338 Opening tables
3984 Prepare 338 Opening tables
3985 Prepare 338 Opening tables
3986 Prepare 334 Opening tables
3987 Sleep 332
3988 Query 338
3989 Prepare 335 Opening tables
3990 Prepare 336 Opening tables
3991 Sleep 335
3992 Query 335
3993 Query 332
3994 Prepare 325 Opening tables
3995 Prepare 328 Opening tables
3996 Sleep 327
3997 Query 327
3998 Sleep 331
3999 Query 331
4000 Sleep 331
4001 Sleep 331
4002 Query 331
4003 Query 325
4004 Sleep 325
4005 Query 325
4006 Sleep 325
4007 Query 331
4008 Sleep 326
4009 Prepare 321 Opening tables
4010 Prepare 318 Opening tables
4011 Query 326
4012 Prepare 324 Opening tables
4013 Sleep 307
4014 Prepare 316 Opening tables
4015 Sleep 316
4016 Query 316
4017 Prepare 322 Opening tables
4018 Sleep 314
4019 Prepare 321 Opening tables
4020 Query 314
4021 Prepare 309 Opening tables
4022 Prepare 305 Opening tables
4024 Query 310
4025 Sleep 317
4026 Prepare 311 Opening tables
4027 Query 317
4028 Sleep 310
4029 Sleep 309
4030 Prepare 315 Opening tables
4031 Query 309
4032 Sleep 305
4033 Prepare 310 Opening tables
4034 Sleep 306
4035 Query 306
4036 Query 307
4037 Sleep 305
4038 Query 305
4039 Sleep 290
4040 Prepare 307 Opening tables
4041 Prepare 301 Opening tables
4042 Prepare 306 Opening tables
4043 Query 290
4044 Prepare 305 Opening tables
4045 Query 302
4046 Sleep 302
4047 Query 304
4048 Sleep 304
4049 Query 305
4050 Prepare 304 Opening tables
4051 Prepare 304 Opening tables
4052 Query 295
4053 Sleep 295
4054 Sleep 282
4055 Prepare 291 Opening tables
4056 Prepare 286 Opening tables
4057 Prepare 283 Opening tables
4058 Prepare 301 Opening tables
4059 Query 296
4060 Prepare 296 Opening tables
4061 Sleep 296
4062 Prepare 295 Opening tables
4063 Prepare 292 Opening tables
4064 Prepare 281 Opening tables
4065 Query 292
4066 Sleep 292
4067 Prepare 292 Opening tables
4068 Prepare 291 Opening tables
4069 Prepare 288 Opening tables
4070 Prepare 276 Opening tables
4071 Prepare 270 Opening tables
4072 Prepare 276 Opening tables
4073 Prepare 276 Opening tables
4075 Sleep 282
4076 Sleep 283
4077 Query 286
4078 Sleep 286
4079 Query 283
4080 Query 282
4081 Prepare 280 Opening tables
4082 Prepare 268 Opening tables
4083 Prepare 278 Opening tables
4084 Prepare 271 Opening tables
4085 Query 282
4086 Prepare 276 Opening tables
4087 Query 279
4088 Sleep 279
4089 Prepare 278 Opening tables
4090 Sleep 280
4091 Query 278
4092 Sleep 273
4093 Query 280
4094 Sleep 278
4095 Query 273
4096 Prepare 270 Opening tables
4097 Query 270
4098 Sleep 271
4099 Prepare 265 Opening tables
4100 Prepare 265 Opening tables
4101 Sleep 272
4102 Prepare 265 Opening tables
4103 Prepare 271 Opening tables
4104 Query 272
4105 Query 268
4106 Prepare 261 Opening tables
4107 Sleep 268
4108 Prepare 257 Opening tables
4109 Prepare 268 Opening tables
4110 Sleep 265
4111 Sleep 263
4112 Prepare 266 Opening tables
4113 Prepare 264 Opening tables
4114 Query 265
4115 Prepare 264 Opening tables
4116 Prepare 263 Opening tables
4117 Prepare 265 Opening tables
4118 Prepare 264 Opening tables
4119 Prepare 264 Opening tables
4120 Query 263
4121 Sleep 248
4122 Prepare 253 Opening tables
4123 Sleep 258
4124 Query 258
4125 Prepare 263 Opening tables
4126 Prepare 263 Opening tables
4127 Prepare 258 Opening tables
4128 Prepare 263 Opening tables
4129 Sleep 256
4130 Prepare 247 Opening tables
4131 Prepare 259 Opening tables
4132 Prepare 249 Opening tables
4133 Prepare 259 Opening tables
4134 Prepare 256 Opening tables
4136 Sleep 252
4137 Query 255
4138 Query 252
4139 Query 256
4140 Sleep 255
4141 Prepare 251 Opening tables
4142 Prepare 242 Opening tables
4143 Sleep 240
4144 Prepare 252 Opening tables
4145 Query 240
4146 Sleep 251
4147 Prepare 251 Opening tables
4148 Query 241
4149 Query 251
4150 Sleep 241
4151 Prepare 244 Opening tables
4152 Prepare 239 Opening tables
4153 Query 240
4154 Query 248
4155 Sleep 240
4156 Sleep 236
4157 Prepare 247 Opening tables
4158 Query 236
4159 Prepare 231 Opening tables
4160 Prepare 246 Opening tables
4161 Prepare 229 Opening tables
4162 Prepare 241 Opening tables
4163 Prepare 232 Opening tables
4164 Prepare 235 Opening tables
4165 Prepare 236 Opening tables
4166 Query 235
4167 Prepare 238 Opening tables
4168 Sleep 235
4169 Prepare 238 Opening tables
4170 Prepare 238 Opening tables
4171 Prepare 236 Opening tables
4172 Sleep 238
4173 Sleep 215
4174 Prepare 218 Opening tables
4175 Query 238
4176 Query 225
4177 Prepare 228 Opening tables
4178 Prepare 220 Opening tables
4179 Prepare 209 Opening tables
4180 Sleep 225
4181 Prepare 215 Opening tables
4182 Prepare 220 Opening tables
4183 Prepare 216 Opening tables
4184 Prepare 228 Opening tables
4185 Prepare 223 Opening tables
4187 Sleep 215
4188 Query 215
4189 Prepare 222 Opening tables
4190 Query 219
4191 Sleep 219
4192 Query 215
4193 Sleep 215
4194 Prepare 215 Opening tables
4195 Prepare 207 Opening tables
4196 Query 215
4197 Prepare 210 Opening tables
4198 Query 212
4199 Prepare 213 Opening tables
4200 Sleep 205
4201 Sleep 212
4202 Query 209
4203 Prepare 210 Opening tables
4204 Sleep 209
4205 Prepare 209 Opening tables
4206 Prepare 210 Opening tables
4207 Prepare 204 Opening tables
4208 Prepare 208 Opening tables
4209 Prepare 186 Opening tables
4210 Prepare 192 Opening tables
4211 Prepare 188 Opening tables
4212 Prepare 207 Opening tables
4213 Sleep 199
4214 Sleep 201
4215 Query 201
4216 Prepare 205 Opening tables
4217 Prepare 205 Opening tables
4218 Query 205
4219 Query 202
4220 Prepare 202 Opening tables
4221 Sleep 202
4222 Prepare 205 Opening tables
4223 Sleep 180
4224 Prepare 197 Opening tables
4225 Prepare 200 Opening tables
4226 Prepare 187 Opening tables
4227 Prepare 189 Opening tables
4228 Query 199
4229 Prepare 189 Opening tables
4230 Prepare 192 Opening tables
4231 Prepare 189 Opening tables
4233 Killed 43 Opening tables
4234 Prepare 189 Opening tables
4235 Prepare 173 Opening tables
4236 Prepare 175 Opening tables
4237 Prepare 193 Opening tables
4238 Prepare 186 Opening tables
4239 Prepare 184 Opening tables
4240 Prepare 177 Opening tables
4241 Prepare 179 Opening tables
4242 Prepare 182 Opening tables
4243 Prepare 189 Opening tables
4244 Prepare 177 Opening tables
4245 Prepare 175 Opening tables
4246 Query 180
4247 Prepare 168 Opening tables
4248 Prepare 180 Opening tables
4249 Sleep 165
4250 Prepare 161 Opening tables
4251 Prepare 169 Opening tables
4252 Query 165
4253 Prepare 177 Opening tables
4254 Prepare 170 Opening tables
4255 Query 174
4256 Prepare 173 Opening tables
4257 Prepare 168 Opening tables
4258 Sleep 174
4259 Prepare 158 Opening tables
4260 Prepare 169 Opening tables
4261 Prepare 171 Opening tables
4262 Prepare 168 Opening tables
4263 Prepare 171 Opening tables
4265 Prepare 159 Opening tables
4266 Prepare 162 Opening tables
4267 Prepare 162 Opening tables
4268 Prepare 155 Opening tables
4269 Prepare 164 Opening tables
4270 Prepare 161 Opening tables
4271 Prepare 152 Opening tables
4272 Prepare 151 Opening tables
4273 Prepare 145 Opening tables
4274 Prepare 153 Opening tables
4275 Prepare 158 Opening tables
4276 Prepare 137 Opening tables
4277 Prepare 149 Opening tables
4278 Prepare 138 Opening tables
4279 Prepare 146 Opening tables
4280 Prepare 151 Opening tables
4281 Prepare 142 Opening tables
4282 Sleep 131
4283 Prepare 148 Opening tables
4284 Prepare 148 Opening tables
4285 Query 141
4286 Sleep 141
4287 Prepare 142 Opening tables
4288 Prepare 143 Opening tables
4289 Prepare 151 Opening tables
4290 Prepare 135 Opening tables
4291 Prepare 142 Opening tables
4292 Prepare 144 Opening tables
4293 Prepare 128 Opening tables
4294 Prepare 145 Opening tables
4296 Prepare 136 Opening tables
4297 Prepare 135 Opening tables
4298 Sleep 135
4299 Prepare 136 Opening tables
4300 Prepare 136 Opening tables
4301 Sleep 126
4302 Query 135
4303 Prepare 135 Opening tables
4304 Prepare 135 Opening tables
4305 Prepare 135 Opening tables
4306 Prepare 132 Opening tables
4307 Prepare 118 Opening tables
4308 Prepare 118 Opening tables
4309 Prepare 115 Opening tables
4310 Prepare 120 Opening tables
4311 Query 131
4312 Prepare 130 Opening tables
4313 Prepare 127 Opening tables
4314 Prepare 127 Opening tables
4315 Prepare 131 Opening tables
4316 Prepare 119 Opening tables
4317 Prepare 119 Opening tables
4318 Prepare 115 Opening tables
4319 Prepare 101 Opening tables
4320 Prepare 127 Opening tables
4321 Sleep 120
4322 Query 120
4323 Prepare 96 Opening tables
4324 Query 126
4325 Prepare 126 Opening tables
4326 Prepare 116 Opening tables
4327 Prepare 126 Opening tables
4328 Prepare 85 Opening tables
4329 Prepare 115 Opening tables
4330 Prepare 123 Opening tables
4331 Prepare 105 Opening tables
4332 Prepare 109 Opening tables
4333 Prepare 113 Opening tables
4334 Prepare 107 Opening tables
4335 Prepare 110 Opening tables
4337 Prepare 104 Opening tables
4338 Prepare 97 Opening tables
4339 Prepare 104 Opening tables
4340 Prepare 98 Opening tables
4341 Prepare 99 Opening tables
4342 Prepare 93 Opening tables
4343 Prepare 92 Opening tables
4344 Prepare 99 Opening tables
4345 Prepare 80 Opening tables
4346 Prepare 84 Opening tables
4347 Prepare 89 Opening tables
4348 Prepare 93 Opening tables
4349 Prepare 94 Opening tables
4350 Prepare 96 Opening tables
4351 Prepare 96 Opening tables
4352 Prepare 96 Opening tables
4353 Prepare 96 Opening tables
4354 Prepare 96 Opening tables
4355 Prepare 96 Opening tables
4356 Prepare 96 Opening tables
4357 Prepare 94 Opening tables
4358 Prepare 95 Opening tables
4359 Prepare 94 Opening tables
4360 Prepare 95 Opening tables
4361 Prepare 95 Opening tables
4362 Prepare 94 Opening tables
4363 Prepare 91 Opening tables
4364 Prepare 93 Opening tables
4365 Prepare 93 Opening tables
4366 Prepare 93 Opening tables
4367 Prepare 91 Opening tables
4368 Prepare 84 Opening tables
4369 Query 78
4370 Sleep 78
4371 Prepare 78 Opening tables
4372 Prepare 87 Opening tables
4373 Prepare 85 Opening tables
4374 Prepare 87 Opening tables
4375 Prepare 84 Opening tables
4376 Prepare 86 Opening tables
4377 Prepare 84 Opening tables
4378 Prepare 78 Opening tables
4379 Prepare 78 Opening tables
4380 Prepare 78 Opening tables
4381 Prepare 78 Opening tables
4382 Prepare 79 Opening tables
4383 Prepare 78 Opening tables
4384 Prepare 73 Opening tables
4385 Prepare 73 Opening tables
4386 Prepare 70 Opening tables
4387 Prepare 79 Opening tables
4388 Prepare 76 Opening tables
4389 Prepare 79 Opening tables
4390 Prepare 78 Opening tables
4391 Prepare 76 Opening tables
4393 Prepare 60 Opening tables
4394 Prepare 66 Opening tables
4395 Prepare 78 Opening tables
4396 Prepare 66 Opening tables
4397 Prepare 74 Opening tables
4398 Prepare 74 Opening tables
4399 Prepare 56 Opening tables
4400 Prepare 58 Opening tables
4401 Prepare 56 Opening tables
4402 Prepare 63 Opening tables
4403 Prepare 56 Opening tables
4404 Prepare 70 Opening tables
4405 Prepare 54 Opening tables
4406 Prepare 56 Opening tables
4407 Prepare 60 Opening tables
4408 Prepare 58 Opening tables
4409 Prepare 60 Opening tables
4410 Prepare 56 Opening tables
4411 Prepare 56 Opening tables
4412 Prepare 64 Opening tables
4413 Prepare 56 Opening tables
4414 Prepare 43 Opening tables
4415 Prepare 54 Opening tables
4416 Prepare 51 Opening tables
4417 Prepare 56 Opening tables
4418 Prepare 51 Opening tables
4419 Prepare 56 Opening tables
4420 Prepare 45 Opening tables
4421 Prepare 45 Opening tables
4422 Prepare 54 Opening tables
4423 Prepare 34 Opening tables
4424 Prepare 49 Opening tables
4425 Prepare 43 Opening tables
4426 Prepare 46 Opening tables
4427 Prepare 46 Opening tables
4429 Prepare 35 Opening tables
4430 Prepare 43 Opening tables
4431 Prepare 39 Opening tables
4432 Prepare 35 Opening tables
4433 Prepare 41 Opening tables
4434 Prepare 41 Opening tables
4435 Prepare 19 Opening tables
4436 Query 28
4437 Sleep 28
4438 Prepare 38 Opening tables
4439 Prepare 25 Opening tables
4440 Prepare 34 Opening tables
4441 Prepare 31 Opening tables
4442 Prepare 20 Opening tables
4443 Prepare 32 Opening tables
4444 Prepare 34 Opening tables
4445 Prepare 23 Opening tables
4446 Prepare 28 Opening tables
4447 Prepare 31 Opening tables
4448 Prepare 29 Opening tables
4450 Prepare 25 Opening tables
4451 Prepare 16 Opening tables
4452 Prepare 18 Opening tables
4453 Prepare 23 Opening tables
4454 Prepare 22 Opening tables
4455 Sleep 22
4456 Prepare 22 Opening tables
4457 Prepare 6 Opening tables
4458 Prepare 9 Opening tables
4459 Prepare 18 Opening tables
4460 Prepare 21 Opening tables
4461 Prepare 19 Opening tables
4462 Sleep 19
4463 Sleep 19
4464 Sleep 19
4465 Prepare 13 Opening tables
4466 Binlog Dump 18 Master has sent all binlog to slave; waiting for binlog to be updated
4467 Prepare 9 Opening tables
4468 Prepare 17 Opening tables
4469 Prepare 11 Opening tables
4470 Prepare 16 Opening tables
4471 Prepare 12 Opening tables
4472 Query 0
4473 Sleep 9
4474 Prepare 9 Opening tables
4475 Sleep 9
4476 Prepare 6 Opening tables
4477 Sleep 9
4478 Prepare 4 Opening tables
4479 Prepare 5 Opening tables
4480 Prepare 6 Opening tables
4481 Prepare 8 Opening tables
4482 Prepare 6 Opening tables
4483 Sleep 4
4484 Sleep 4
4485 Prepare 4 Opening tables
4486 Prepare 2 Opening tables
4487 Sleep 4
4488 Sleep 1
4489 Prepare 1 Opening tables
4490 Sleep 1
4491 Prepare 1 Opening tables
4492 Sleep 1
==================

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.