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

>А что касается проблемы — табличка MyISAM, наверное?
да.
делал эксперимент, удалил все добавленные строки, все стало хорошо,
добавил вновь но уже меньше 60К строк, все повторилось

кстати апдейты этой таблицы идут не в реальном времени, не юзеры инициируют апдейты, от юзеров только селекты,
селекты от юзеров не только по ключу конкретному на выборку строки одной, но и селекты на много строк - диапазонные, поиски
для апдейтов создан механизм кеширования действий и по крону происходят порционные апдейты
и именно они начинают так лочить таблицу, крон работает с режимом low_priority_update, хотя его отключал и это ни как не влияло на ситуацию

ключевой вопрос в том, может быть это проблема какаято местная, типа увеличение какого нибудь буфера или у мускула есть баг, изменив что то маленькое система заработает
на такую мысль наталкивает, то что небольшое количество строк убивает все. Я все таки надеюсь на решение в таком духе )

Но если нет, я бы рад был бы услышать какую нибудь здравую идею), я сам думал про следущее:
1) сделать эту таблицу Innodb, но есть сомнения, Innodb сама по себе медленее + есть селекты, которые охватывают много строк и этих селектов много, то построчный лок в InnoDB тут ведь не помогает мускул же все равно будет ждать освобождения лока этой отдельной строки что бы сделать многострочную выборку?
2)сделать паритцию на эту таблицу уже бы сделал, но остановило, то что есть ограничение на создание партиций на таблицы с уникальными ключами, у меня их там 3
3)до оптимизация падающих селектов, их уменьшение в объеме, путем кеширования до мускула (мемкеш), ну и еще более продуманные апдейты, что бы их было еще меньше
4)вертикальное разделение таблицы, самый тяжелый вариант. т.к. сопряжено с изменение кучи кода

структура этой таблицы, мускул стоит 5.1.48
CREATE TABLE members (
  member_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  username varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `host` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  domain varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0',
  cell_status tinyint(1) NOT NULL DEFAULT '0',
  hidden tinyint(1) unsigned NOT NULL DEFAULT '0',
  online tinyint(1) unsigned NOT NULL DEFAULT '0',
  last_action int(10) unsigned NOT NULL DEFAULT '0',
  guest_views mediumint(8) unsigned NOT NULL DEFAULT '0',
  `language` varchar(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  agree_ar tinyint(1) unsigned NOT NULL DEFAULT '0',
  show_on tinyint(1) unsigned NOT NULL DEFAULT '1',
  show_off tinyint(1) unsigned NOT NULL DEFAULT '0',
  `password` char(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  name_ax varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  email varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  valid_email tinyint(1) unsigned NOT NULL DEFAULT '1',
  country varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  genre tinyint(1) unsigned NOT NULL DEFAULT '0',
  look_genre tinyint(1) unsigned NOT NULL DEFAULT '0',
  phone varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  returns tinyint(1) unsigned NOT NULL DEFAULT '0',
  tid smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Tracker ID',
  rid int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Refferent ID',
  photo mediumint(8) unsigned NOT NULL DEFAULT '0',
  photo_modest tinyint(2) NOT NULL DEFAULT '0',
  photo_hot tinyint(1) unsigned NOT NULL DEFAULT '0',
  geo_city int(10) unsigned NOT NULL DEFAULT '0',
  geo_look int(10) unsigned NOT NULL DEFAULT '0',
  signup_ip int(11) NOT NULL DEFAULT '0',
  signup_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  signup_date_paid datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  activate_date int(10) unsigned NOT NULL DEFAULT '0',
  dob date NOT NULL DEFAULT '0000-00-00',
  notification tinyint(1) unsigned NOT NULL DEFAULT '1',
  notification_mail tinyint(1) unsigned NOT NULL DEFAULT '1',
  notification_sent tinyint(1) unsigned NOT NULL DEFAULT '1',
  notification_agent tinyint(1) unsigned NOT NULL DEFAULT '1',
  notification_show tinyint(1) unsigned NOT NULL DEFAULT '1',
  notification_specials tinyint(1) unsigned NOT NULL DEFAULT '1',
  notification_birthday tinyint(1) unsigned NOT NULL DEFAULT '1',
  msgs_with_photo_only tinyint(1) unsigned NOT NULL DEFAULT '0',
  msgs_with_look_filter tinyint(1) unsigned NOT NULL DEFAULT '0',
  height tinyint(3) unsigned NOT NULL DEFAULT '0',
  weight tinyint(3) unsigned NOT NULL DEFAULT '0',
  about_yourself text COLLATE utf8_unicode_ci NOT NULL,
  about_yourself_wait text COLLATE utf8_unicode_ci,
  age_from tinyint(2) unsigned NOT NULL DEFAULT '0',
  age_to tinyint(2) unsigned NOT NULL DEFAULT '0',
  look_target varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  club_id smallint(6) NOT NULL DEFAULT '0',
  `code` smallint(4) unsigned NOT NULL DEFAULT '0',
  profile_template tinyint(3) unsigned NOT NULL DEFAULT '0',
  agree_with_other_sites_banner tinyint(1) unsigned NOT NULL DEFAULT '1',
  agree_with_adult_sites_banner tinyint(1) unsigned NOT NULL DEFAULT '1',
  message_id varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `comment` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (member_id),
  UNIQUE KEY username (username),
  UNIQUE KEY email (email),
  UNIQUE KEY phone (phone),
  KEY dob (dob),
  KEY totals (`status`,`language`,genre,look_genre),
  KEY geo_city (geo_city),
  KEY signup_date (signup_date),
  KEY show_on (show_on),
  KEY `status` (`status`,genre,show_off,signup_date)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

конфиг такой
max_connections      = 1024
connect_timeout      = 20
table_cache          = 2048M
key_buffer_size      = 1024M
sort_buffer_size     = 128M
read_buffer_size     = 64M
join_buffer_size     = 24512000
read_rnd_buffer_size = 128M
myisam_sort_buffer_size = 64M
tmp_table_size       = 256M
max_heap_table_size  = 256M
max_tmp_tables       = 32
max_allowed_packet   = 5M
query_cache_size     = 512M
query_cache_limit    = 32M
#query_cache_type    = 2
thread_cache_size    = 256

я наблюдал как используется этот кеш, он не заполняется на 100% всегда есть свободные блоки, сейчас данные после сброса, но и вмомент нагрузки там добрая половина свободна

Key_blocks_not_flushed     0     The number of key blocks in the key cache that have changed but haven't yet been flushed to disk. It used to be known as Not_flushed_key_blocks.
Key_blocks_unused     823 k     The number of unused blocks in the key cache. You can use this value to determine how much of the key cache is in use.
Key_blocks_used     165 k     The number of used blocks in the key cache. This value is a high-water mark that indicates the maximum number of blocks that have ever been in use at one time.
Key_read_requests     248 M     The number of requests to read a key block from the cache.
Key_reads     398 k     The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.
Key_write_requests     865 k     The number of requests to write a key block to the cache.
Key_writes     335 k     The number of physical writes of a key block to disk.
Key_buffer_fraction_%     21.47 %    
Key_write_ratio_%     38.72 %    
Key_read_ratio_%     0.16 %

учитывая все это, видимо увеличивать буфер ключей уже нет смысла это не повлияет на улучшение ситуации?
   

>SELECT не блокируют строки
это без сомнений так, я имел ввиду другое, выйгрыш InnoDB в том что на апдейт он не лочит таблицу, но лочит одну строку для обновления.
Но у меня селекты которые охватывают много строк, выборка в селекте не потому ключу по которому происходит обновление, значит оптимизатор не может знать какие строки конкретно ему понадобятся для выборки и следовательно он будет ждать освобождения локов на всей таблице что бы сделать селект. В таком сценарии это так будет работать или я ошибаюсь?


> В Вашем случае, если обновления действительно приходят пачкой,
да отдельным кроном, но апдейты идут один за другим по циклу, у мускула же вроде нет возможности дать одну команду SQL на обновление пачки строк, как с INSERT


>стоит выставить флаг DELAY_KEY_WRITE
конкретно тот апдей который зависает UPDATE members SET online=1, last_action=10000003 WHERE member_id=92800 не меняет ни одного индекса
в этом сценарии DELAY_KEY_WRITE разве может чемто помочь или в таблице все равно что то меняется из ключей что нужно сбросить на диск, даже если колонки входящие в индекс не изменились?

Отредактированно denisimus (26.10.2010 12:55:34)