Mysql 로그예요 어찌해야될지 여쭙습니다...
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, 아주 가끔은 저도 이랬으면 좋겠습니다.
쿼리로 보이는데 왜 이렇게 만든건가요?
딱봐도 문제가 있는 쿼리로 보입니다
왜 이렇게 만드셨나요?
쿼리를 좀 더 나누고 트랜젝션에 대해 연구를 좀 해보시는게 좋겠네요
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