Jalart
Новичок
Что надо поправить в файле my.cnf?
Здравствуйте.
Есть SQL-запрос, который выглядит так:
===============================================================
select u.*,'1' as perm_hit,h.hit,u.descrRUS as descr,u.region as region,u.descrFRUS as descrF,u.bannercode as bannercode,users.name as user_name,users.login as user_login,'0' as order_set,0 as rid from url u left join users on u.uid=users.id left join hit h on u.id=h.mid and h.module='url' where u.confirm order by u.id=36 desc,u.id=76 desc,
...
вырезано около 130 Кб текста
...
desc,u.id=10867 desc,u.id=10819 desc,u.id=10869 desc limit 0,10;
===============================================================
При выполнении этого запроса он обрывается с выдачей сообщения об ошибке:
"Ошибка: Out of memory; restart server and try again (needed 1048568 bytes)".
Если выполнить часть этого запроса, например:
===============================================================
select u.*,'1' as perm_hit,h.hit,u.descrRUS as descr,u.region as region,u.descrFRUS as descrF,u.bannercode as bannercode,users.name as user_name,users.login as user_login,'0' as order_set,0 as rid from url u left join users on u.uid=users.id left join hit h on u.id=h.mid and h.module='url' where u.confirm order by u.id=36 desc,u.id=76 desc limit 0,10;
===============================================================
то все отрабатывает нормально.
Все работает на веб-сервере с 2 Гб памяти.
Что надо поправить в файле my.cnf, чтобы устранить нехватку памяти? Сам файл my.cnf приводится ниже.
Некоторые строки забиты звездочками.
===============================================================
My.cnf
# Example mysql config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# a important part and systems up to 128M very MySQL is used together with
# other programs (like a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/var) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.
# The following options will be passed to all MySQL clients
[client]
#password = ***
port=***
socket=/tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
init-connect="SET NAMES cp1251"
datadir=***
#log=***
sql-mode=NO_UNSIGNED_SUBTRACTION
#set-variable=query_cache_size=524288
set-variable=query_cache_size=1024000
set-variable=bdb_cache_size=0
#set-variable=bulk_insert_buffer_size=524288
set-variable=bulk_insert_buffer_size=1024000
set-variable=key_buffer=16M
set-variable=max_allowed_packet=2M
set-variable=table_cache=64
#set-variable=sort_buffer_size=512K
set-variable=sort_buffer_size=1M
set-variable=net_buffer_length=8K
set-variable=myisam_sort_buffer_size=8M
skip-innodb
skip-bdb
port=***
socket=/tmp/mysql.sock
skip-external-locking
default-character-set=cp1251
set-variable=interactive_timeout=120
set-variable=wait_timeout=120
set-variable=delayed_insert_limit=0
set-variable=delayed_insert_timeout=0
set-variable=delayed_queue_size=0
set-variable=max_delayed_threads=0
#log-bin
#server-id = 1
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=4M
#set-variable = bdb_max_lock=10000
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/var/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/var/
#innodb_log_arch_dir = /usr/local/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
# Set .._log_file_size to 25 % of buffer pool size
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#set-variable = innodb_lock_wait_timeout=50
[mysqldump]
quick
set-variable=max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable=key_buffer=20M
set-variable=sort_buffer_size=20M
set-variable=read_buffer=2M
set-variable=write_buffer=2M
[myisamchk]
set-variable=key_buffer=20M
set-variable=sort_buffer_size=20M
set-variable=read_buffer=2M
set-variable=write_buffer=2M
[mysqlhotcopy]
interactive-timeout
[mysql.server]
user=***
log-error=/dev/null
User ***
Group ***
ServerAdmin ***
ServerName ***
KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 15
StartServers 1
MinSpareServers 5
MaxSpareServers 10
MaxClients 80
MaxRequestsPerChild 10000
===============================================================
Заранее спасибо.
С наилучшими пожеланиями,
Владимир.
Здравствуйте.
Есть SQL-запрос, который выглядит так:
===============================================================
select u.*,'1' as perm_hit,h.hit,u.descrRUS as descr,u.region as region,u.descrFRUS as descrF,u.bannercode as bannercode,users.name as user_name,users.login as user_login,'0' as order_set,0 as rid from url u left join users on u.uid=users.id left join hit h on u.id=h.mid and h.module='url' where u.confirm order by u.id=36 desc,u.id=76 desc,
...
вырезано около 130 Кб текста
...
desc,u.id=10867 desc,u.id=10819 desc,u.id=10869 desc limit 0,10;
===============================================================
При выполнении этого запроса он обрывается с выдачей сообщения об ошибке:
"Ошибка: Out of memory; restart server and try again (needed 1048568 bytes)".
Если выполнить часть этого запроса, например:
===============================================================
select u.*,'1' as perm_hit,h.hit,u.descrRUS as descr,u.region as region,u.descrFRUS as descrF,u.bannercode as bannercode,users.name as user_name,users.login as user_login,'0' as order_set,0 as rid from url u left join users on u.uid=users.id left join hit h on u.id=h.mid and h.module='url' where u.confirm order by u.id=36 desc,u.id=76 desc limit 0,10;
===============================================================
то все отрабатывает нормально.
Все работает на веб-сервере с 2 Гб памяти.
Что надо поправить в файле my.cnf, чтобы устранить нехватку памяти? Сам файл my.cnf приводится ниже.
Некоторые строки забиты звездочками.
===============================================================
My.cnf
# Example mysql config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# a important part and systems up to 128M very MySQL is used together with
# other programs (like a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/var) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.
# The following options will be passed to all MySQL clients
[client]
#password = ***
port=***
socket=/tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
init-connect="SET NAMES cp1251"
datadir=***
#log=***
sql-mode=NO_UNSIGNED_SUBTRACTION
#set-variable=query_cache_size=524288
set-variable=query_cache_size=1024000
set-variable=bdb_cache_size=0
#set-variable=bulk_insert_buffer_size=524288
set-variable=bulk_insert_buffer_size=1024000
set-variable=key_buffer=16M
set-variable=max_allowed_packet=2M
set-variable=table_cache=64
#set-variable=sort_buffer_size=512K
set-variable=sort_buffer_size=1M
set-variable=net_buffer_length=8K
set-variable=myisam_sort_buffer_size=8M
skip-innodb
skip-bdb
port=***
socket=/tmp/mysql.sock
skip-external-locking
default-character-set=cp1251
set-variable=interactive_timeout=120
set-variable=wait_timeout=120
set-variable=delayed_insert_limit=0
set-variable=delayed_insert_timeout=0
set-variable=delayed_queue_size=0
set-variable=max_delayed_threads=0
#log-bin
#server-id = 1
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=4M
#set-variable = bdb_max_lock=10000
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/var/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/var/
#innodb_log_arch_dir = /usr/local/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
# Set .._log_file_size to 25 % of buffer pool size
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#set-variable = innodb_lock_wait_timeout=50
[mysqldump]
quick
set-variable=max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable=key_buffer=20M
set-variable=sort_buffer_size=20M
set-variable=read_buffer=2M
set-variable=write_buffer=2M
[myisamchk]
set-variable=key_buffer=20M
set-variable=sort_buffer_size=20M
set-variable=read_buffer=2M
set-variable=write_buffer=2M
[mysqlhotcopy]
interactive-timeout
[mysql.server]
user=***
log-error=/dev/null
User ***
Group ***
ServerAdmin ***
ServerName ***
KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 15
StartServers 1
MinSpareServers 5
MaxSpareServers 10
MaxClients 80
MaxRequestsPerChild 10000
===============================================================
Заранее спасибо.
С наилучшими пожеланиями,
Владимир.