Mysql 로그예요 어찌해야될지 여쭙습니다...

gugupalsib1의 이미지

2013-06-23 12:22:00 2896 [Note] Plugin 'FEDERATED' is disabled.
2013-06-23 12:22:00 2896 [Warning] option 'innodb-autoextend-increment': unsigned value 67108864 adjusted to 1000
2013-06-23 12:22:00 a78 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-06-23 12:22:00 2896 [Note] InnoDB: The InnoDB memory heap is disabled
2013-06-23 12:22:00 2896 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-06-23 12:22:00 2896 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-06-23 12:22:00 2896 [Note] InnoDB: Not using CPU crc32 instructions
2013-06-23 12:22:00 2896 [Note] InnoDB: Initializing buffer pool, size = 2.0G
2013-06-23 12:22:00 2896 [Note] InnoDB: Completed initialization of buffer pool
2013-06-23 12:22:00 2896 [Note] InnoDB: Highest supported file format is Barracuda.
2013-06-23 12:22:00 2896 [Note] InnoDB: 128 rollback segment(s) are active.
2013-06-23 12:22:00 2896 [Note] InnoDB: Waiting for purge to start
2013-06-23 12:22:01 2896 [Note] InnoDB: 5.6.12 started; log sequence number 163416219
2013-06-23 12:22:01 2896 [Note] Server hostname (bind-address): '*'; port: 3306
2013-06-23 12:22:01 2896 [Note] IPv6 is available.
2013-06-23 12:22:01 2896 [Note] - '::' resolves to '::';
2013-06-23 12:22:01 2896 [Note] Server socket created on IP: '::'.
2013-06-23 12:22:01 2896 [Note] Event Scheduler: Loaded 0 events
2013-06-23 12:22:01 2896 [Note] C:/Program Files/MySQL/MySQL Server 5.6/bin\mysqld: ready for connections.
Version: '5.6.12-log' socket: '' port: 3306 MySQL Community Server (GPL)
2013-06-23 12:22:01 2896 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: update tbcalculater set charged=(select sum(money) from tbaccount where type=1 and status=1 and uid in (select sid from tbuser where status not in (0,1,97,98,99)) and date='20130623'),exchanged=(select sum(money) from tbaccount where type=2 and status=1 and uid in (select sid from tbuser where status not in (0,1,97,98,99)) and date='20130623'),branch_exchanged=(select sum(money) from tbaccount where type=2 and status=1 and uid in (select sid from tbuser where status = 1) and date='20130623'),branch_money=(select sum(money)+sum(bonus)+sum(event_money) from tbuser where status=1),member_money=(select sum(money)+sum(event_money) from tbus

현재 사용중인 서버는 window 2008 64bit이구요
사양은 inter(R)Xeon(R)CPU E3-1230V2 3.30GHz 3.30GHz
메모리는 8Gb
제가 구글링하면서 세팅한값입니다..

[client]
no-beep

# pipe
# socket=mysql
port=3306

default-character-set=utf8
 
 
# SERVER SECTION
# ----------------------------------------------------------------------
 
[mysqld]
 
 
# The TCP/IP Port the MySQL Server will listen on
port=3306
 
skip-name-resolve 
 
# Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.6/"
 
# Path to the database root
datadir="C:/ProgramData/MySQL/MySQL Server 5.6/data\"
 
# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=utf8
 
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB
 
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
 
 
 
log-output=NONE
general-log=1
general_log_file="WIN-SFCDZFVZMU9.log"
slow-query-log=1
slow_query_log_file="WIN-SFCDZFVZMU9-slow.log"
long_query_time=3
 
log-bin=mysql-bin
 
 
 
log-error="WIN-SFCDZFVZMU9.err"
 
 
max_connections=1024
 
 
query_cache_size=0
 
 
table_open_cache=2000
 
 
tmp_table_size=56M
 
thread_cache_size=9
 
myisam_max_sort_file_size=100G
 
 
myisam_sort_buffer_size=110M
 
key_buffer_size=8M
 
read_buffer_size=64K
read_rnd_buffer_size=256K
 
sort_buffer_size=256K
 
 
 
 
innodb_additional_mem_pool_size=64M
 
innodb_flush_log_at_trx_commit=1
 
 
innodb_log_buffer_size=8M
 
 
innodb_buffer_pool_size=2048M
 
 
innodb_log_file_size=500M
 
 
innodb_thread_concurrency=16
 
 
innodb_autoextend_increment=64M
innodb_buffer_pool_instances=8
 
 
innodb_concurrency_tickets=5000
 
 
innodb_old_blocks_time=1000
 
 
innodb_open_files=300
 
 
innodb_stats_on_metadata=0
 
 
innodb_file_per_table=1
 
 
innodb_checksum_algorithm=0
 
 
back_log=70
 
 
flush_time=0
 
 
 
join_buffer_size=256K
 
 
max_allowed_packet=256M
 
 
max_connect_errors=1000
 
open_files_limit=4110
 
query_cache_type=0
 
 
 
sort_buffer_size=256K
 
 
table_definition_cache=1400
 
 
 
binlog_row_event_max_size=8K
 
sync_master_info=10000
 
sync_relay_log=10000
 
sync_relay_log_info=10000
 
wait_timeout = 3600
interactive_timeout=3600

academic의 이미지

뭐가 문제란 말씀이신지요?

----
academic은 제 고등학교 때 동아리 이름입니다.
academic, 아주 가끔은 저도 이랬으면 좋겠습니다.

hothead의 이미지

딱봐도 문제가 있는 쿼리로 보입니다

왜 이렇게 만드셨나요?

쿼리를 좀 더 나누고 트랜젝션에 대해 연구를 좀 해보시는게 좋겠네요

update
tbcalculater
set
charged=(
select sum(money)
from tbaccount
where type=1 and status=1
and uid in (
select sid
from
tbuser
where status not in (0,1,97,98,99)
)
and date='20130623')
,exchanged=(
select sum(money)
from tbaccount
where type=2 and status=1
and uid in (
select sid from tbuser where status not in (0,1,97,98,99)
) and date='20130623')
,branch_exchanged=(
select sum(money) from tbaccount where type=2 and status=1
and uid in (
select sid from tbuser where status = 1
) and date='20130623'
),branch_money=(
select sum(money)+sum(bonus)+sum(event_money)
from tbuser where status=1)
,member_money=(
select sum(money)+sum(event_money) from tbus