Форум пользователей MySQL

sv · 26.03.2012 18:42:33

В наличии OTRS.
Есть старый архивный сервер на который с промышленного сливаются тикиты.

Поднял новый архивный сервер.
Настроил OTRS и DB по аналогии со старым сервером.

При этом удручает производительность полнотекстового поиска.

-------------------------------------------------------------------------
Итак, старый сервер IBM eServer BladeCenter HS21.

[root@server-old ~]# free -m

             total       used       free     shared    buffers     cached

Mem:          3290       3125        165          0          5       1854

-/+ buffers/cache:       1264       2026

Swap:         4031         36       3995

2 x Intel(R) Xeon(R) CPU 5160 @ 3.00GHz

[root@server-old ~]# hdparm -tT /dev/sda

/dev/sda:

 Timing cached reads:   12924 MB in  2.00 seconds = 6469.05 MB/sec

 Timing buffered disk reads:  178 MB in  3.02 seconds =  58.95 MB/sec

 [root@server-old ~]# egrep -v '^#|^$' /etc/my.cnf

[mysqld]

init_connect='SET collation_connection = utf8_general_ci'

init_connect='SET NAMES utf8'

default-character-set = utf8

character-set-server = utf8

collation-server = utf8_general_ci

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

skip-locking

skip-innodb

query_cache_type=1

query_cache_limit=1M

interactive_timeout=100

wait_timeout=15

connect_timeout=10

table_cache=512

thread_cache=32

key_buffer=128M

thread_concurrency=2

long_query_time=2

user=mysql

old_passwords=1

max_allowed_packet = 16M

query_cache_size = 512M

max_connections = 500

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]

quick

max_allowed_packet = 256M

[client]

default-character-set = utf8

[mysql.server]

user=mysql

basedir=/var/lib

[safe_mysqld]

err-log=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

open_files_limit=8192

[isamchk]

key_buffer=512M

sort_buffer=64M

read_buffer=16M

write_buffer=16M

[myisamchk]

key_buffer=512M

sort_buffer=64M

read_buffer=16M

write_buffer=16M

-------------------------------------------------------------------------
Новый сервер. По всей видимости какой-то самосбор и менее мощный чем старый.
Dual Core AMD Opteron(tm) Processor 180

[root@server-new ~]# hdparm -tT /dev/sda

/dev/sda:

 Timing cached reads:   980 MB in  2.00 seconds = 489.55 MB/sec

 Timing buffered disk reads:  324 MB in  3.03 seconds = 107.09 MB/sec

[root@server-new ~]# free -m

             total       used       free     shared    buffers     cached

Mem:          3038       2668        370          0         10       2090

-/+ buffers/cache:        566       2471

Swap:         1023          4       1019

[root@server-new ~]# egrep -v '^#|^$' /etc/my.cnf

[client]

port            = 3306

socket          = /var/lib/mysql/mysql.sock

[mysqld]

port            = 3306

socket          = /var/lib/mysql/mysql.sock

skip-locking

key_buffer_size = 384M

max_allowed_packet = 16M

table_open_cache = 512

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size = 48M

thread_concurrency = 4

myisam_max_sort_file_size = 20G

myisam_max_extra_sort_file_size = 20G

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

---------------------------------------------------------
Вот сам запрос в БД. Отловил используя mytop

SELECT DISTINCT st.id, st.tn, st.create_time_unix

FROM ticket st, queue sq , article_search art

WHERE sq.id = st.queue_id

AND st.id = art.ticket_id

AND sq.group_id IN (1, 2, 3, 4, 13, 31, 39, 40, 42, 43, 44, 51, 52, 53, 54, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 68, 69, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 153, 256, 258, 259, 261, 262, 364, 365, 366, 370, 372, 373, 376, 377, 378, 379, 380, 381, 382, 383, 384, 385, 386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 397, 398, 399, 400, 401, 402, 403, 404, 405, 406, 408, 409, 413, 414, 418, 430, 431, 432, 433, 434, 435, 436, 437, 438, 439, 440, 441, 442, 443, 444, 445, 446, 447, 448, 449, 450, 451, 452, 453, 454, 455, 456, 457, 458, 459, 460, 461, 462, 463, 464, 465, 467, 468, 469, 470, 471, 472, 473, 475, 476, 477, 478, 479, 480, 481, 482, 483, 484, 485, 486, 487, 488, 489, 490, 491, 492, 493, 495, 496, 498, 502, 503, 504, 505, 512, 514, 515, 516, 517, 518, 519, 520, 521, 522, 523, 524, 525, 526, 527, 528, 529, 530, 531, 532, 534, 536, 537, 540, 541, 542, 543, 544, 545, 546, 547, 548, 549, 550, 551, 552, 553, 554, 555, 556, 557, 558, 559, 560, 561, 562, 563, 564, 565, 566, 567, 568, 569, 570, 571, 572, 573, 574, 575, 576, 577, 581, 582, 583, 585, 586, 587, 588, 589, 590, 591, 592, 593, 598, 600, 601, 602, 603, 604, 605, 606, 613, 614, 615, 616, 617, 618, 619, 620, 621, 622, 623, 624, 625, 626, 627, 628, 629, 630, 631, 632, 633, 634, 635, 636, 637, 638, 639, 640, 641, 642, 643, 644, 645, 646, 647, 648, 662, 667, 671, 672, 673, 674, 675, 676, 677, 678, 679, 680, 681, 682, 683, 684, 685, 686, 687, 688, 689, 690, 691, 692, 693, 694, 695, 696, 697, 698, 699, 700, 701, 703, 704, 705, 709, 710, 711, 712, 713, 714, 715, 716, 717, 718, 719, 720, 721, 722, 723, 724, 725, 726, 727, 728, 729, 730, 731, 732, 734, 735, 736, 739, 740, 741, 743, 744, 745, 746, 747, 748, 749, 750, 751, 752, 753, 754, 755, 756, 757, 758, 759, 760, 761, 762, 763, 765, 770, 771, 772, 774, 775, 776, 777, 778, 779, 783, 784, 785, 786, 787, 788, 789, 790, 791, 792, 793, 794, 795, 796, 797, 798, 799, 800, 801, 802, 803, 804, 805, 806, 807, 808, 809, 810, 811, 812, 813, 814, 815, 816, 817, 818, 819, 820, 821, 822, 823, 824, 825, 845, 846, 847, 848, 849, 850, 851, 852, 853, 854, 855, 856, 857, 858, 859, 860, 861, 862, 863, 864, 865, 866, 867, 868, 869, 870, 871, 872, 873, 874, 875, 877, 878, 879, 880, 881, 882, 883, 884, 885, 886, 887, 888, 889, 890, 891, 892, 893, 894, 895, 896, 897, 898, 899, 900, 901, 902, 903, 904, 905, 906, 907, 908, 909, 910, 911, 912, 913, 914, 915, 916, 917, 918, 919, 920, 921, 922, 923, 924, 925, 926, 927, 928, 929, 930, 931, 932, 933, 934, 935, 936, 937, 938, 939, 940, 941, 942, 943, 944, 945, 946, 947, 948, 949, 950, 951, 952, 953, 954, 955, 956, 957, 958, 959, 960, 961, 962, 963, 964, 965, 966, 967, 968, 969, 970, 971, 972, 973, 974, 975, 976, 977, 978, 979, 980, 981, 982, 983, 984, 985, 986, 987, 988, 989, 990, 991, 992, 993, 994, 995, 996, 997, 998, 999, 1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011, 1012, 1013, 1014, 1015, 1016, 1017, 1018, 1019, 1020, 1021, 1022, 1023, 1024, 1025, 1026, 1027, 1028, 1029, 1030, 1031, 1032, 1033, 1034, 1035, 1037, 1038, 1039, 1040, 1041, 1042, 1043, 1044, 1045, 1046, 1047, 1048, 1049, 1050, 1051, 1052, 1053, 1054, 1055, 1056, 1057, 1058, 1059, 1060, 1061, 1062, 1063, 1064, 1065, 1066, 1067, 1068, 1069, 1070, 1071, 1072, 1073, 1074, 1075, 1076, 1077, 1078, 1079, 1080, 1081, 1082, 1083, 1084, 1085, 1086, 1087, 1088, 1089, 1090, 1091, 1092, 1093, 1094, 1095, 1096, 1097, 1098, 1099, 1100, 1101, 1103, 1104, 1105, 1106, 1107, 1108, 1109, 1110, 1111, 1112, 1113, 1114, 1115, 1116, 1117, 1118, 1119, 1121, 1122, 1123, 1124, 1125, 1126, 1127, 1128, 1129, 1130, 1131, 1132, 1133, 1134, 1135, 1136, 1137, 1138, 1139, 1140, 1141, 1142, 1144, 1145, 1146, 1147, 1148, 1149, 1151, 1152, 1153, 1154, 1155, 1156, 1157, 1158, 1159, 1160, 1161, 1162, 1163, 1164, 1165, 1166, 1167, 1168, 1169, 1170, 1171, 1172, 1173, 1174)

AND ( LOWER(art.a_body) LIKE LOWER('%2012021310746825%'))

ORDER BY st.create_time_unix DESC LIMIT 2000

-----------------------------------------------------------------------

Вот какая ситуация на старом сервере:
mysql> show profiles;
| 1 | 15.71905700 | SELECT DISTINCT st.id, st.tn, st.create_time_unix

mysql> show profile for query 1;
+--------------------------------+-----------+
| Status | Duration |
+--------------------------------+-----------+
| (initialization) | 0.000002 |
| checking query cache for query | 0.000607 |
| Opening tables | 0.000014 |
| System lock | 0.000007 |
| Table lock | 0.000083 |
| init | 0.000147 |
| optimizing | 0.000054 |
| statistics | 0.005553 |
| preparing | 0.000083 |
| Creating tmp table | 0.00003 |
| executing | 0.000003 |
| Copying to tmp table | 15.712307 |
| Sorting result | 0.000025 |
| Sending data | 0.00004 |
| end | 0.000003 |
| removing tmp table | 0.000013 |
| end | 0.000004 |
| query end | 0.000004 |
| storing result in query cache | 0.000003 |
| freeing items | 0.000064 |
| closing tables | 0.000009 |
| logging slow query | 0.000002 |
+--------------------------------+-----------+
22 rows in set (0.00 sec)

-------------------------------------------------------

А вот, что имеем на новом:

mysql> show profiles;

|        1 | 1032.64154800 | SELECT DISTINCT st.id, st.tn, st.create_time_unix

mysql> show profile for query 1;

+--------------------------------+------------+

| Status                         | Duration   |

+--------------------------------+------------+

| starting                       |   0.000093 |

| checking query cache for query |   0.000605 |

| checking permissions           |   0.000004 |

| checking permissions           |   0.000003 |

| checking permissions           |   0.000007 |

| Opening tables                 |   0.000025 |

| System lock                    |   0.000008 |

| Table lock                     |   0.000081 |

| init                           |   0.000264 |

| optimizing                     |   0.000090 |

| statistics                     |   0.007940 |

| preparing                      |   0.000225 |

| Creating tmp table             |   0.000059 |

| executing                      |   0.000004 |

| Copying to tmp table           | 999.999999 |

| Sorting result                 |   0.006637 |

| Sending data                   |   0.000031 |

| end                            |   0.000004 |

| removing tmp table             |   0.000026 |

| end                            |   0.000007 |

| query end                      |   0.000004 |

| freeing items                  |   0.004622 |

| storing result in query cache  |   0.000251 |

| logging slow query             |   0.000003 |

| logging slow query             |   0.000004 |

| cleaning up                    |   0.000006 |

+--------------------------------+------------+

26 rows in set (0.07 sec)

При этом если посмотреть, чем же занят mysql, то наблюдаем следующее:
show processlist;
Видим, что наш запрос висит в состоянии:
Copying to tmp table

SELECT DISTINCT st.id, st.tn, st.create_time_unix

FROM ticket st, queue sq , article_search art

WHER

Подскажите что можно подкрутить, чтобы новый сервер не тормозил при полнотекстовом поиске. Он конечно же слабее чем старый, но разница в скорости запросов никак не должна быть такой огромной.

paulus · 27.03.2012 00:23:51

Ну, это не полнотекстовый поиск, насколько я вижу, а полный скан таблицы.
И упираетесь Вы или в диск (если создается достаточно большая временная
табличка), или в процессор. Хотя, скорее таки в диск.

Предложить можно таки использовать полнотекстовый поиск (насколько я по-
нимаю, OTRS умеет отлично работать со sphinx). Ну или на худой конец — под-
нимите значения tmp_table_size и max_heap_table_size так, чтобы оно созда-
валось не на диске, а в памяти.

sv · 27.03.2012 16:38:40

Выполняется все-таки полнотекстовый поиск. Так как в самой форме ОТРС поле в которое заносится значение так и называется полнотестовым - Fulltext-Search in Article (e. g. "Mar*in" or "Baue*") и ищу я по полю Text (поиск в заметках ко всем тикитам).

По поводу указанных вами параметров. Я выставлял еще до создания данной темы следующие значения:
max_heap_table_size=256M
tmp_table_size=256M
Ситуация не изменилась. Поэтому закоментировал эти параметры в конфиге.

-----------------------------------------------------------------------------------------
Итак попытка №2.

Запрос висит в состоянии:
Copying to tmp table

#iotop
Total DISK READ: 9.56 M/s | Total DISK WRITE: 0.00 B/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
16693 be/4 mysql 9.55 M/s 0.00 B/s 0.00 % 90.53 % mysqld --basedir=/usr --datadir=/var/lib/mysql -~id --socket=/var/lib/mysql/mysql.sock --port=3306
1 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % init
2 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kthreadd]
3 rt/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [migration/0]
4 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [ksoftirqd/0]
5 rt/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [migration/0]
6 rt/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [watchdog/0]

Смущает, что нет записи на диск. Хотя временами проскакивает. Но очень редко и запись измеряется даже не в M/s, а K/s.

#top
top - 14:35:20 up 26 days, 2:00, 4 users, load average: 1.11, 0.72, 0.31
Tasks: 133 total, 1 running, 132 sleeping, 0 stopped, 0 zombie
Cpu(s): 7.5%us, 1.8%sy, 0.0%ni, 47.5%id, 43.3%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 3111468k total, 3026696k used, 84772k free, 9480k buffers
Swap: 1048568k total, 4932k used, 1043636k free, 2280360k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14035 mysql 20 0 566m 451m 2584 S 15.9 14.9 191:11.29 mysqld
23086 root 20 0 14140 6772 2572 S 2.3 0.2 1:30.08 iotop
21430 root 20 0 11616 3428 2592 S 0.3 0.1 0:00.88 sshd
23076 root 20 0 2672 1140 868 R 0.3 0.0 0:07.12 top
1 root 21 1 2864 360 264 S 0.0 0.0 0:01.53 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.03 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 0:00.12 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 0:00.42 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
6 root RT 0 0 0 0 S 0.0 0.0 0:00.28 watchdog/0
7 root RT 0 0 0 0 S 0.0 0.0 0:00.09 migration/1
8 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/1
9 root 20 0 0 0 0 S 0.0 0.0 0:21.74 ksoftirqd/1
10 root RT 0 0 0 0 S 0.0 0.0 0:00.33 watchdog/1
11 root 20 0 0 0 0 S 0.0 0.0 0:00.25 events/0
12 root 20 0 0 0 0 S 0.0 0.0 1:50.52 events/1

Тут меня смущает, что пользовательские приложения занимают лишь 7,5%.
При этом проц свободен на 47.5%id. Но при этом 43.3%wa - процент процессорного времени, потраченного на завершение ввода/вывода(IO)

Вот скорость выполнения запроса
4 | 905.51930400

А вот на чем он тормозит.

mysql> show profile for query 4;
+--------------------------------+------------+
| Status | Duration |
+--------------------------------+------------+
| starting | 0.000035 |
| checking query cache for query | 0.017446 |
| checking permissions | 0.000013 |
| checking permissions | 0.000003 |
| checking permissions | 0.000009 |
| Opening tables | 0.000028 |
| System lock | 0.000009 |
| Table lock | 0.000084 |
| init | 0.000309 |
| optimizing | 0.000094 |
| statistics | 0.013600 |
| preparing | 0.000235 |
| Creating tmp table | 0.000057 |
| executing | 0.000005 |
| Copying to tmp table | 905.453003 |
| Sorting result | 0.022623 |
| Sending data | 0.002443 |
| end | 0.000007 |
| removing tmp table | 0.000404 |
| end | 0.000007 |
| query end | 0.000004 |
| freeing items | 0.008828 |
| storing result in query cache | 0.000029 |
| logging slow query | 0.000003 |
| logging slow query | 0.000004 |
| cleaning up | 0.000022 |
+--------------------------------+------------+
26 rows in set (0.03 sec)

На что я намекаю? Мне кажется настолько катастрофическая производительность не из-за диска или проца.
Возможно в чем то еще проблема?

Дополнительная инфа.
БД занимает 25 Гб.
Таблицы
ticket - 1,2 Gb
queue - 400 Kb
article_search - 4,8 Gb

Инфа по ОС и ПО:
Старый сервер - Red Hat Enterprise Linux Server release 5.3 (Tikanga), mysql-server-5.0.45-7.el5
Новый сервер - CentOS release 6.2 (Final), mysql-server-5.1.52-1.el6_0.1.i686

---------------------------------------------------------------------------
Хотелось бы уточнить по поводу полнотекстового поиска с использованием Sphinx. Я так понимаю, что для интеграции ОТРС и сфинкс придется ОТРС обрабатывать напильником, то есть править код?

paulus · 28.03.2012 10:51:22

Я был уверен, что оно нативно поддерживается. Но, похоже, действительно нет

Ок, давайте вернемся к железным проблемам. У Вас система проводит половину
времени в random io по диску, а вы считаете, что диски тут не при чем. Почему?

P.S. Полнотекстовым поиском обычно называют поиск с использованием полнотекс-
тового индекса. У Вас же не используется, поэтому я бы его таким не считал.

sv · 29.03.2012 11:56:35

Я так понимаю проблема вовсе не в mysql, а в дисковой подсистеме? Какие варианты решения проблемы? Заменить сервер, винты, или RAID контроллер? Есть какая-либо альтернатива?

Форум пользователей MySQL

#1 26.03.2012 18:42:33

Тормозит полнотекстовый поиск

#2 27.03.2012 00:23:51

Re: Тормозит полнотекстовый поиск

#3 27.03.2012 16:38:40

Re: Тормозит полнотекстовый поиск

#4 28.03.2012 10:51:22

Re: Тормозит полнотекстовый поиск

#5 29.03.2012 11:56:35

Re: Тормозит полнотекстовый поиск

Board footer