SQLinfo.ru - Все о MySQL

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 26.03.2012 18:42:33

sv
Участник
Зарегистрирован: 26.03.2012
Сообщений: 22

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

В наличии 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


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

Неактивен

 

#2 27.03.2012 00:23:51

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

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

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

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

Неактивен

 

#3 27.03.2012 16:38:40

sv
Участник
Зарегистрирован: 26.03.2012
Сообщений: 22

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

Выполняется все-таки полнотекстовый поиск. Так как в самой форме ОТРС поле в которое заносится значение так и называется полнотестовым - 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. Я так понимаю, что для интеграции ОТРС и сфинкс придется ОТРС обрабатывать напильником, то есть править код?

Неактивен

 

#4 28.03.2012 10:51:22

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

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

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

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

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

Неактивен

 

#5 29.03.2012 11:56:35

sv
Участник
Зарегистрирован: 26.03.2012
Сообщений: 22

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

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

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson