Правильно настроить my.cnf под таблицу в 18 000 000 записей.

[lexey2006.90]

Таблица, из которой идет сортировка весит 4 Гб. Вся база 30 Гб.
Знакомый тестировал на своем серваке - и у него нормально выборка идет - у меня иногда нормально, а иногда виснет на 2-3 мин.
Вот сам файл my.cnf:

[mysqld]
datadir=/var/lib/mysql
tmpdir=/tmp
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
back_log = 50

max_allowed_packet = 16M



default-storage-engine = InnoDB
#skip-bdb

query_cache_size = 128M
query_cache_limit = 16M

thread_cache_size = 300
thread_concurrency = 16

table_cache = 10000

tmp_table_size = 256M
max_heap_table_size = 256M


sort_buffer_size = 16M
join_buffer_size = 4M

max_connections = 50

#log-bin=mysql-bin
server-id = 1
sync-binlog = 1
#binlog_format=mixed
max_binlog_size = 100M

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2048M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 4M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb-file-per-table = 1
innodb_flush_method=O_DIRECT

transaction_isolation = READ-COMMITTED

ft_min_word_len = 4

expire_logs_days = 3

key_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
myisam_sort_buffer_size = 1M

long_query_time = 2
log_slow_queries= /var/log/mysql-slow.log
slow_query_log = 1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



[ndbd]
# If you are running a MySQL Cluster storage daemon (ndbd) on this machine,
# adjust its connection to the management daemon here.
# Note: ndbd init script requires this to include nodeid!
connect-string="nodeid=2;host=localhost:1186"

[ndb_mgm]
# connection string for MySQL Cluster management tool
connect-string="host=localhost:1186"

Характеристика самой машины:

model name : Intel(R) Core(TM) i7 CPU 870 @ 2.93GHz
stepping : 5
cpu MHz : 1200.000
cache size : 8192 KB

 

[lexey2006.90]

cpu MHz : 2934.000

 

[Gas]

А памяти сколько на сервере и это чисто db-сервер или на нём что-то ещё крутится ?

При таком размере данных неплохобы увеличить innodb_buffer_pool_size, но до какого предела - зависит от заданых выше вопросов.

и sort_buffer_size можно попробовать увеличить раза в 2-3, потому что, по тем запросам что ты раньше приводил, filesort будет в большинстве случаев.

И по поводу дампа что ты выкладывал, ты конечно извини, но это полный капец, дамп который изменяет системную базу данных mysql. Я ожидал дамп с одной табличкой в 18M записей, а там такое счастье. Даже разбираться не стал, просто удалил.

 

[lexey2006.90]

Я просто не знаю как сделать с помощью mysqldump - дамп 1 таблицы
Памяти 4 Гб, больше на нем ничего не крутится - сервер чисто для db.
Чем поможет изменение sort_buffer_size?

 

[Gas]

для большинства случаев подойдёт такой вариант: mysqldump -u [username] -p [database] > [path_and_filename]

ну если 4GB, то думаю 2GB под innodb_buffer_pool_size лучше и оставить

sort_buffer_size - память для сортировок, когда её не хватает, то используется диск, что конечно медленней.

В целом, каких-то явных косяков с настройками не вижу (но я естественно не последняя инстанция).
Хотя, для полноты картины можно посмотреть и на show global status, show variables и SHOW ENGINE INNODB STATUS;

Но думаю лучше сосредоточиться на расстановке правильных индексов на таблице + не помешал бы дамп одной таблицы, я бы поглядел.

 

[lexey2006.90]

И по поводу дампа что ты выкладывал, ты конечно извини, но это полный капец, дамп который изменяет системную базу данных mysql. Я ожидал дамп с одной табличкой в 18M записей, а там такое счастье. Даже разбираться не стал, просто удалил.

Вот дамп одной таблицы:
ftp://piklya.com/tmp/ файл sumpceudbpeh96cmh8oqrmsum6.7z