Database ExecuteSQL(MySQL client run out of memory)
0
votes
1
answer
2387
views
I've an online game
the game server execute more than 1M queries [ SELECT , REPLACE , UPDATE ] every 10 sec
the problem the memory usage still increasing i dun know why ??
i mean if we suppose that memory usage at the moment is 2000M and it increases to 3000M when the game server execute the queries . why don't the memory usage back again to 2000M or around value after excuting ??? .. it stills increasing and increasing
This Problem Makes The Following Error Occur
2016-7-14 16:17:52 -- ERROR: Database ExecuteSQL(MySQL client run out of memory) occur mysql error(SELECT * FROM cq_tutor WHERE tutor_id = 16090848 AND Betrayal_flag = 0).
2016-7-14 16:17:52 -- ERROR: Database ExecuteSQL(Lost connection to MySQL server during query) occur mysql error(SELECT * FROM cq_tutor WHERE Student_id = 16090848 AND Betrayal_flag = 0 LIMIT 1).
btw when i restart the game server the memory usage back to 2000M and still increasing too ..
I want to know the reason of memory usage increasing
i stopped caching of mysql but the same problem occur
mysql version is 4.0.17
the client i use is winmysqladmin 1.4
mysql> SHOW VARIABLES;
+------------------------------+-----------------------------------
| Variable_name | Value |
+------------------------------+-----------------------------------
| back_log | 50
| basedir | E:\ZFSERVER\MYSQL\
| binlog_cache_size | 32768
| bulk_insert_buffer_size | 8388608
| character_set | latin1
| character_sets | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON
| connect_timeout | 5
| convert_character_set |
| datadir | E:\ZFSERVER\MYSQL\data\
| default_week_format | 0
| delay_key_write | ON
| delayed_insert_limit | 100
| delayed_insert_timeout | 300
| delayed_queue_size | 1000
| flush | OFF
| flush_time | 1800
| ft_boolean_syntax | + -> SHOW STATUS;
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Aborted_clients | 36 |
| Aborted_connects | 0 |
| Bytes_received | 41352179 |
| Bytes_sent | 84359720 |
| Com_admin_commands | 181 |
| Com_alter_table | 2 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 64 |
| Com_change_master | 0 |
| Com_check | 1 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 252 |
| Com_delete | 70 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 276 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 292241 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 2 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 9 |
| Com_replace | 6 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 15736 |
| Com_set_option | 12 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 25 |
| Com_show_databases | 27 |
| Com_show_fields | 2760 |
| Com_show_grants | 0 |
| Com_show_keys | 9 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 2 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1934 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 57 |
| Com_show_variables | 59 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 2138 |
| Connections | 207 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 4 |
| Handler_delete | 1 |
| Handler_read_first | 3363 |
| Handler_read_key | 36141 |
| Handler_read_next | 68771 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 132 |
| Handler_read_rnd_next | 3039359 |
| Handler_rollback | 63 |
| Handler_update | 10 |
| Handler_write | 292202 |
| Key_blocks_used | 2157 |
| Key_read_requests | 459308 |
| Key_reads | 352 |
| Key_write_requests | 197090 |
| Key_writes | 197085 |
| Max_used_connections | 24 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 0 |
| Open_files | 0 |
| Open_streams | 0 |
| Opened_tables | 1906 |
| Questions | 315904 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 0 |
| Qcache_free_blocks | 0 |
| Qcache_total_blocks | 0 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 129 |
| Select_range_check | 0 |
| Select_scan | 2691 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 22 |
| Sort_rows | 132 |
| Sort_scan | 6 |
| Table_locks_immediate | 311455 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 206 |
| Threads_connected | 4 |
| Threads_running | 1 |
| Uptime | 21423 |
+-------------------------+----------+
-----------------------------------------------
select che from arenaa where mapid=81513 and status=1
select nameb from arenac where mapid=81529
DELETE FROM arenaa where mapid=81529
select nameb from arenac where mapid=81530
select che from arenaa where mapid=81501 and status=2
REPLACE INTO arenac set mapid=\"81502\",nameb=\"%user_name\"
select namea from arenaa where mapid=81505
------------------------------------------------
-- ----------------------------
-- Table structure for arenaa
-- ----------------------------
DROP TABLE IF EXISTS
arenaa;
CREATE TABLE arenaa (
mapid int(100) NOT NULL default '0',
namea varchar(100) NOT NULL default '0',
nameb varchar(100) NOT NULL default '0',
status int(100) NOT NULL default '0',
che int(100) NOT NULL default '1',
UNIQUE KEY imapid (mapid)
) TYPE=MyISAM;
-- ----------------------------
-- Records of arenaa
-- ----------------------------
#############################################
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for arenac
-- ----------------------------
DROP TABLE IF EXISTS arenac;
CREATE TABLE arenac (
mapid int(100) NOT NULL default '0',
nameb varchar(100) NOT NULL default '0',
UNIQUE KEY imapid (mapid)
) TYPE=MyISAM;
-- ----------------------------
-- Records of arenac
-- ----------------------------
thanks for reading ,,
Asked by JORDAN MI
(1 rep)
Jul 19, 2016, 01:51 AM
Last activity: Jul 25, 2025, 05:04 AM
Last activity: Jul 25, 2025, 05:04 AM