V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
shentucao
V2EX  ›  问与答

网站每隔一个小时就有 MySQL 慢查询日志,遍历博客上的所有文章,导致我的网站会非常卡的,完全不明白这是什么原理?

  •  
  •   shentucao · 2019-10-25 14:59:32 +08:00 · 1511 次点击
    这是一个创建于 1885 天前的主题,其中的信息可能已经有所发展或是发生改变。
    有大神帮忙看看吗?
    5 条回复    2019-11-06 09:47:52 +08:00
    shentucao
        1
    shentucao  
    OP
       2019-10-25 15:00:50 +08:00
    SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) ORDER BY wp_posts.post_date DESC;
    # Time: 191025 2:03:02
    # User@Host: tucao_f[tucao_f] @ localhost [127.0.0.1]
    # Query_time: 9.852998 Lock_time: 0.037784 Rows_sent: 4562 Rows_examined: 14511
    SET timestamp=1571983382;
    SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (5734, 5610, 5730, 5729, 5724, 5717, 5712, 5714, 5701, 5705, 5650, 5688, 5685, 5676, 5672, 5668, 5665, 5663, 5660, 5658, 5648, 5651, 5643, 5641, 5636, 5639, 5634, 5633, 5626, 5623, 5616, 5619, 5614, 5605, 5608, 5603, 5601, 5599, 5596, 5594, 5591, 5587, 5585, 5580, 5577, 5571, 5566, 5563, 5560, 5557, 5554, 5551, 5542, 5544, 5536, 5534, 5535, 5531, 5529, 5524, 5522, 5517, 5515, 5511, 5506, 5499, 5498, 5494, 5495, 5491, 5488, 5479, 5469, 5462, 5457, 5442, 5440, 5438, 5437, 5429, 5428, 5425, 5420, 5415, 5416, 5403, 5395, 5391, 5385, 5388, 5383, 5364, 5355, 5356, 5353, 5348, 5342, 5337, 5331, 5325, 5322, 5320, 5312, 5309, 5306, 5304, 5298, 5296, 5294, 5289, 5285, 5280, 5277, 5275, 5273, 5269, 5266, 5267, 5188, 5258, 5252, 5255, 5248, 5244, 5241, 5238, 5229, 5228, 5219, 5215, 5216, 5212, 5209, 5200, 5197, 5190, 5186, 5181, 5177, 5179, 5173, 5170, 5168, 5166, 5161, 5159, 5156, 5151, 5148, 5145, 5143, 5141, 5137, 5134, 5132, 5123, 5129, 5119, 5116, 5109, 5106, 5084, 5098, 5094, 5092, 5090, 5088, 5086, 5077, 5082, 5078, 5065, 5062, 5035, 5056, 4997, 5052, 5046, 5042, 5026, 5029, 5027, 5023, 5021, 5019, 5010, 5004, 5002, 5000, 4995, 4994, 4991, 4987, 4974, 4983, 4821, 4971, 4967, 4965, 4960, 4957, 4955, 4953, 4947, 4945, 4942, 4940, 4936, 4932, 4924, 4912, 4913, 4907, 4908, 4902, 2373, 4897, 4892, 4887, 4882, 4879, 4876, 4860, 4870, 4867, 4861, 4857, 4855, 4853, 4850, 4848, 4843, 4839, 4835, 958, 4832, 4830, 4826, 4793, 4808, 4812, 4810, 4805, 4802, 4800, 4794, 4791, 4784, 4780, 4775, 4765, 4771, 4767, 4759, 4756, 4754, 4752, 4750, 4746, 4744, 4741, 4739, 4735, 4728, 4725, 4723, 4721, 4719, 4712, 4707, 4704, 4699, 4693, 4691, 4685, 4683, 4679, 4667, 4672, 4671, 4668, 4664, 4656, 4652, 4650, 4630, 4644, 4642, 4640, 3098, 4637, 4631, 4628, 4622, 4621, 4618, 4613, 4610, 4592, 4589, 4586, 4580, 4577, 4572, 4561, 4555, 4549, 4550, 4545, 4541, 4535, 4408, 4532, 4529, 4524, 4521, 4516, 4514, 4511, 4508, 4503, 4501, 4499, 4496, 4494, 4490, 4487, 4484, 4481, 4476, 4474, 4470, 4472, 4467, 4462, 4459, 4456, 4454, 4452, 4449, 4447, 4445, 4443, 4436, 4438, 4432, 4430, 4426, 4424, 4418, 4414, 4409, 4404, 4398, 4394, 4392, 4390, 4389, 4377, 4366, 4299, 4363, 4358, 4356, 4354, 4348, 4344, 4338, 4341, 4336, 4330, 4317, 4325, 4321, 4316, 4312, 4308, 4303, 4293, 4286, 4278, 4280, 4277, 4272, 4263, 4260, 4254, 4248, 4245, 4242, 4240, 4238, 4236, 4229, 4226, 4214, 4212, 4206, 4203, 4192, 4189, 4187, 4178, 4167, 4159, 4157, 4163, 4148, 4143, 4141, 4139, 4134, 4132, 4129, 4117, 4124, 4119, 4115, 4111, 4108, 4102, 4093, 4092, 4089, 4085, 4083, 4079, 3693, 4076, 4074, 3056, 4071, 4067, 4061, 4063, 4059, 4056, 4046, 4047, 4044, 4038, 4036, 4029, 4027, 4017, 4022, 3943, 4019, 4012, 4011, 3997, 4003, 4001, 3870, 3995, 3992, 3990, 3988, 3983, 3984, 3977, 3975, 3972, 3965, 3963, 3960, 3956, 3954, 3946, 3941, 3938, 3935, 3931, 3929, 3927, 3924, 3922, 3919, 3917, 3911, 3905, 3906, 3897, 3890, 3121, 3889, 3884, 3846, 3877, 3855, 3873, 3871, 3865, 3860, 3856, 3842, 3674, 3841, 3837, 3836, 3703, 3832, 3830, 3828, 3826, 3788, 3821, 3819, 3809, 3810, 3814, 3808, 3803, 3804, 3800, 3798, 3793, 3760, 3784, 3767, 3764, 3762, 3756, 3751, 3754, 3743, 3744, 3739, 3735, 3733, 3730, 3728, 3724, 3721, 3715, 3706, 3704, 3700, 3697, 3694, 3691, 3685, 3682, 3679, 3675, 3676, 3671, 3669, 3665, 3626, 3662, 3660, 3648, 3641, 3638, 3632, 3628, 3621, 3617, 3618, 3615, 3611, 3612, 3609, 3603, 3598, 3595, 3584, 3585, 3581, 3580, 3575, 3570, 3564, 3561, 3558, 3555, 3553, 3534, 3549, 3545, 3536, 3532, 3529, 3525, 3521, 3515, 3511, 3510, 3508, 3506, 3502, 3499, 3463, 3497, 3493, 3491, 3489, 3485, 3482, 3480, 3476, 3474, 3472, 3468, 3460, 3458, 3455, 3445, 3442, 3439, 3436, 3430, 3427, 3426, 3425, 3419, 3412, 3417, 3408, 3403, 3401, 3400, 3395, 3393, 3390, 3391, 3386, 3384, 3382, 3380, 3373, 3376, 3374, 3367, 3368, 3365, 3361, 3359, 3356, 3351, 3349, 3344, 3340, 3338, 3335, 3332, 3329, 3325, 3317, 3311, 3304, 3259, 3297, 3284, 3272, 3274, 3271, 3265, 3263, 3249, 3257, 3245, 3240, 3238, 3235, 3233, 3231, 3229, 3226, 3215, 3211, 3209, 3206, 3207, 3201, 3203, 3199, 3197, 3195, 3193, 3185, 3188, 3182, 3180, 3178, 3175, 3173, 3171, 3162, 3163, 3160, 3158, 3155, 3151, 3148, 3142, 3140, 3135, 3129, 3127, 3122, 3117, 3114, 3110, 3111, 3102, 3103, 3099, 3095, 3091, 3086, 3081, 3076, 3078, 3061, 3057, 3054, 3052, 3050, 3046, 3036, 3043, 3033, 3028, 3024, 3019, 3017, 3012, 3011, 3008, 3006, 3003, 3002, 2997, 2985, 2983, 2981, 2979, 2978, 2974, 2967, 2966, 2960, 2957, 2955, 2622, 2951, 2945, 2942, 2939, 2936, 2931, 2927, 2919, 2916, 2903, 2905, 2906, 2902, 2897, 2895, 2892, 2889, 2887, 2884, 2525, 2881, 2879, 2877, 2871, 2869, 2867, 2865, 2863, 2860, 2858, 2853, 2851, 2848, 2845, 2841, 2839, 2837, 2835, 2824, 2823, 2832, 2827, 2819, 2817, 2815, 2813, 2809, 2811, 2805, 2801, 2803, 2800, 2795, 2792, 2790, 2787, 2784, 2782, 2780, 2778, 2775, 2770, 2764, 2762, 2761, 2741, 2757, 2743, 2758, 2752, 2750, 2746, 2744, 2739, 2735, 2732, 2714, 2720, 2725, 2721, 2722, 2715, 2712, 2710, 2664, 2702, 2698, 2696, 2688, 2684, 2680, 2678, 2675, 2668, 2666, 2662, 2661, 2658, 2656, 2652, 2648, 2645, 2643, 2641, 2639, 2637, 2635, 2632, 2625, 2617, 2615, 2613, 2611, 2608, 2607, 2603, 2601, 2599, 2596, 2592, 2590, 2588, 2582, 2577, 2576, 2574, 2572, 2569, 2371, 2565, 2564, 2559, 2560, 2557, 2546, 2548, 2545, 2542, 2540, 2538, 2536, 2534, 2532, 2530, 2528, 2526, 2517, 2522, 2520, 2515, 2513, 2509, 2507, 2505, 2501, 2498, 2495, 2493, 2479, 2490, 2485, 2488, 2473, 2482, 2475, 2471, 2468, 2464, 2465, 2462, 2460, 2457, 2455, 2453, 2451, 2448, 2446, 2442, 2425, 2437, 2434, 2431, 2428, 2426, 2423, 2420, 2418, 2402, 2399, 2396, 2392, 2393, 2389, 2385, 2381, 2374, 2369, 2367, 2362, 2359, 2353, 2346, 2344, 2342, 2340, 2339, 2334, 2331, 2329, 2325, 2323, 2320, 2318, 2314, 2313, 2311, 2309, 2306, 2304, 2300, 2298, 2296, 2292, 2290, 2288, 2286, 2279, 2273, 2272, 2270, 2268, 2266, 2250, 2245, 2243, 2241, 2239, 2237, 2234, 2230, 2194, 2219, 2222, 2224, 2217, 2215, 2207, 2202, 2198, 2186, 2187, 2178, 2176, 2174, 2171, 2170, 2164, 2163, 2109, 2156, 2154, 2147, 2150, 2148, 2145, 2139, 2140, 2135, 2134, 2131, 2129, 2125, 2126, 2123, 2119, 2117, 2115, 2113, 2110, 2107, 2106, 2104, 2102, 2100, 2095, 2091, 2087, 2088, 2084, 2082, 2080, 2079, 2077, 2075, 2073, 2068, 2066, 2063, 2061, 2058, 2056, 2054, 2052, 2050, 2048, 2046, 2043, 2041, 2036, 2033, 2032, 2029, 2026, 2021, 2019, 2017, 2014, 2012, 2009, 2007, 2005, 2003, 2001, 1999, 1997, 1995, 1990, 1988, 1986, 1980, 1972, 1977, 1970, 1968, 1964, 1962, 1959, 1957, 1955, 1949, 1945, 1943, 1941, 1939, 1935, 1932, 1929, 1927, 1924, 1913, 1920, 1914, 1901, 1899, 1898, 1897, 1896, 1894, 1893, 1892, 1890, 1889, 1886, 1883, 1881, 1878, 1876, 1873, 1871, 1866, 1864, 1860, 1859, 1855, 1853, 1851, 1845, 1843, 1841, 1839, 1837, 1821, 1832, 1831, 1829, 1827, 1824, 1822, 1802, 1819, 1817, 1813, 1808, 1805, 1798, 1796, 1794, 1792, 1785, 1790, 1788, 1784, 1782, 1778, 1776, 1773, 1769, 1767, 1765, 1762, 1759, 1755, 1753, 1748, 1745, 1739, 1743, 1741, 1737, 1735, 1727, 1720, 1714, 1708, 1705, 1704, 1701, 1699, 1697, 1695, 1692, 1685, 1682, 1680, 1678, 1674, 1671, 1669, 1667, 1665, 1663, 1654, 1649, 1646, 1642, 1640, 1638, 1636, 1633, 1630, 1628, 1626, 1623, 1621, 1615, 1617, 1610, 1608, 1606, 1603, 1601, 1599, 1597, 1595, 1592, 1589, 1587, 1583, 1580, 1578, 1577, 1545, 1535, 1530, 1525, 1520, 1517, 1514, 1512, 1509, 1507, 1501, 1498, 1496, 1493, 1490, 1484, 1481, 1478, 1469, 1468, 1466, 1463, 1461, 1459, 1456, 1454, 1449, 1448, 1437, 1432, 1428, 1424, 1420, 1416, 1413, 1411, 1407, 1405, 1388, 1383, 1378, 1375, 1371, 1365, 1361, 1358, 1355, 1351, 1343, 1334, 1324, 1320, 1312, 1305, 1306, 1298, 1302, 1300, 1297, 1293, 1286, 1284, 1280, 1278, 1271, 1265, 1260, 1257, 1254, 1251, 1248, 1242, 1239, 1237, 1235, 1232, 1228, 1226, 1224, 1219, 1213, 1208, 1204, 1198, 1196, 1192, 1190, 1187, 1183, 1175, 1170, 1168, 1166, 1163, 1161, 1158, 1156, 1149, 1146, 1144, 1141, 1137, 1132, 1128, 1125, 1123, 1122, 1114, 1112, 1110, 1106, 1103, 1093, 1087, 1083, 1079, 1078, 1068, 1062, 1059, 1041, 1034, 1031, 1029, 1026, 1021, 1018, 1016, 1010, 1006, 1002, 998, 993, 983, 981, 975, 969, 967, 965, 963, 961, 959, 953, 948, 945, 943, 939, 937, 927, 1044, 918, 915, 907, 905, 901, 897, 895, 893, 892, 884, 879, 876, 872, 869, 867, 865, 858, 859, 856, 854, 852, 850, 847, 845, 843, 841, 839, 837, 834, 830, 824, 819, 817, 813, 800, 797, 792, 789, 785, 782, 775, 770, 766, 764, 758, 756, 725, 752, 750, 747, 742, 728, 723, 716, 713, 704, 740, 700, 698, 696, 694, 692, 687, 682, 675, 673, 863, 659, 665, 660, 662, 657, 655, 780, 652, 650, 649, 647, 644, 637, 635, 626, 604, 601, 594, 591, 589, 580, 581, 571, 569, 567, 565, 561, 554, 552, 545, 531, 529, 526, 524, 522, 520, 518, 516, 506, 504, 502, 500, 498, 496, 494, 490, 488, 486, 484, 481, 478, 476, 473, 745, 736, 462, 459, 450, 448, 446, 443, 440, 437, 435, 433, 430, 427, 424, 420, 418, 416, 414, 412, 409, 407, 402, 398, 393, 387, 384, 381, 378, 370, 367, 363, 361, 356, 354, 349, 344, 340, 334, 330, 324, 320, 313, 306, 302, 288, 282, 276, 267, 261, 257, 253, 250, 247, 240, 237, 230, 227, 224, 220, 217, 209, 206, 199, 195, 191, 187, 184, 169, 163, 141, 126, 6, 4, 1, 778, 890, 888, 886, 678, 632, 608) ORDER BY t.name ASC;
    /www/server/mysql/bin/mysqld, Version: 5.5.62-log (Source distribution). started with:
    linauror
        2
    linauror  
       2019-10-25 15:00:59 +08:00
    那就排查下哪里执行了这个语句,优化掉
    shentucao
        3
    shentucao  
    OP
       2019-10-25 15:02:03 +08:00
    怎么查询?完全不懂数据库,只会导入导出。
    freelancher
        4
    freelancher  
       2019-10-26 06:20:56 +08:00 via iPhone
    不懂就要花钱请懂的人查
    shentucao
        5
    shentucao  
    OP
       2019-11-06 09:47:52 +08:00
    这个问题我自己解决了,我没找到哪个程序出了问题。二十部 mysql 数据库限制在 500M 内存运行,之前给了 2G 内存运行,网站就没有卡顿了。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2587 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 22ms · UTC 10:24 · PVG 18:24 · LAX 02:24 · JFK 05:24
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.