0

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              | + -><()~*:""&|
| ft_min_word_len               | 4 
| ft_max_word_len              | 254
| ft_max_word_len_for_sort       | 20
| ft_stopword_file               | (built-in)
| have_bdb                     | NO 
| have_crypt                   | NO 
| have_innodb                  | YES
| have_isam                    | YES 
| have_raid                     | NO 
| have_symlink                  | YES
| have_openssl                 | NO
| have_query_cache             | YES
| init_file                       | 
| innodb_additional_mem_pool_size | 1048576 
| innodb_buffer_pool_size         | 8388608 
| innodb_data_file_path           | ibdata1:10M:autoextend 
| innodb_data_home_dir          |
| innodb_file_io_threads          | 4
| innodb_force_recovery          | 0
| innodb_thread_concurrency      | 8
| innodb_flush_log_at_trx_commit   | 1
| innodb_fast_shutdown          | ON
| innodb_flush_method           |
| innodb_lock_wait_timeout        | 50
| innodb_log_arch_dir            | .\
| innodb_log_archive             | OFF
| innodb_log_buffer_size          | 1048576
| innodb_log_file_size            | 5242880
| innodb_log_files_in_group        | 2
| innodb_log_group_home_dir      | .\
| innodb_mirrored_log_groups      | 1
| innodb_max_dirty_pages_pct     | 90
| interactive_timeout              | 28800
| join_buffer_size                | 131072
| key_buffer_size               | 8388600
| language                     | E:\ZFSERVER\MYSQL\share\english\
| large_files_support             | ON
| local_infile                    | ON
| log                          | OFF
| log_update                    | OFF
| log_bin                       | OFF
| log_slave_updates             | OFF
| log_slow_queries              | OFF
| log_warnings                  | OFF
| long_query_time               | 10
| low_priority_updates            | OFF
| lower_case_table_names        | ON
| max_allowed_packet            | 1048576
| max_binlog_cache_size         | 4294967295
| max_binlog_size               | 1073741824                                                                                                                                                                       |
| max_connections              | 100                                                                                                                                                                              |
| max_connect_errors            | 10                                                                                                                                                                               |
| max_delayed_threads           | 20                                                                                                                                                                               |
| max_heap_table_size           | 16777216                                                                                                                                                                         |
| max_join_size                 | 4294967295                                                                                                                                                                       |
| max_relay_log_size             | 0                                                                                                                                                                                |
| max_seeks_for_key            | 4294967295                                                                                                                                                                       |
| max_sort_length               | 1024                                                                                                                                                                             |
| max_user_connections          | 0                                                                                                                                                                                |
| max_tmp_tables                | 32                                                                                                                                                                               |
| max_write_lock_count          | 4294967295                                                                                                                                                                       |
| myisam_max_extra_sort_file_size | 268435456                                                                                                                                                                        |
| myisam_max_sort_file_size      | 2147483647                                                                                                                                                                       |
| myisam_repair_threads          | 1                                                                                                                                                                                |
| myisam_recover_options        | OFF                                                                                                                                                                             |
| myisam_sort_buffer_size        | 8388608                                                                                                                                                                          |
| named_pipe                   | OFF                                                                                                                                                                             |
| net_buffer_length              | 16384                                                                                                                                                                            |
| net_read_timeout               | 30                                                                                                                                                                               |
| net_retry_count                | 10                                                                                                                                                                               |
| net_write_timeout              | 60                                                                                                                                                                               |
| new                         | OFF                                                                                                                                                                             |
| open_files_limit                | 0                                                                                                                                                                                |
| pid_file                       | E:\ZFSERVER\MYSQL\data\orientxp.pid                                                                                                                                                 |
| log_error                     | .\orientxp.err                                                                                                                                                                      |
| port                         | 3306                                                                                                                                                                             |
| protocol_version               | 10                                                                                                                                                                               |
| query_alloc_block_size          | 8192                                                                                                                                                                             |
| query_cache_limit              | 1048576                                                                                                                                                                          |
| query_cache_size              | 0                                                                                                                                                                                |
| query_cache_type             | ON                                                                                                                                                                              |
| query_prealloc_size            | 8192                                                                                                                                                                             |
| range_alloc_block_size          | 2048                                                                                                                                                                             |
| read_buffer_size               | 131072                                                                                                                                                                           |
| read_only                     | OFF                                                                                                                                                                             |
| read_rnd_buffer_size           | 262144                                                                                                                                                                           |
| rpl_recovery_rank              | 0                                                                                                                                                                                |
| server_id                     | 0                                                                                                                                                                                |
| slave_net_timeout              | 3600                                                                                                                                                                             |
| skip_external_locking           | ON                                                                                                                                                                              |
| skip_networking               | OFF                                                                                                                                                                             |
| skip_show_database           | OFF                                                                                                                                                                             |
| slow_launch_time              | 2                                                                                                                                                                                |
| sort_buffer_size               | 2097144                                                                                                                                                                          |
| sql_mode                     | 0                                                                                                                                                                                |
| table_cache                   | 64                                                                                                                                                                               |
| table_type                    | MYISAM                                                                                                                                                                          |
| thread_cache_size             | 0                                                                                                                                                                                |
| thread_stack                  | 196608                                                                                                                                                                           |
| tx_isolation                    | REPEATABLE-READ                                                                                                                                                                |
| timezone                     | Egypt Standard Time                                                                                                                                                                |
| tmp_table_size                 | 33554432                                                                                                                                                                         |
| tmpdir                        | C:\WINDOWS\TEMP\                                                                                                                                                                 |
| transaction_alloc_block_size     | 8192                                                                                                                                                                             |
| transaction_prealloc_size        | 4096                                                                                                                                                                             |
| version                       | 4.0.17-nt                                                                                                                                                                         |
| version_comment              | Source distribution                                                                                                                                                                  |
| wait_timeout                  | 28800                                                                                                                                                                            |

mysql> 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 ,,

  • Upgrade. Change to InnoDB. – Rick James Jul 19 '16 at 3:57
  • @RickJames thanks from replying :) can u tell me what benefits will i miss if i make my tables innodb ? – JORDAN MI Jul 19 '16 at 4:35
  • @RickJames converted all tables to innodb but the same problem occur :( – JORDAN MI Jul 19 '16 at 7:08
  • How much RAM? Client an server on same machine? 32-bit version? – Rick James Jul 19 '16 at 19:18
  • @RickJames thnx from your time.. ram is 4gb .. op system windows xp sp3 32 bit .. i tried windows server 2008 r2 64 bit it's better bit but the same problem occur on windows server too ..... btw 4gb is good for the gameserver .. there's similar gameserver works on 2 gb ram only .. thnx again for replyin – JORDAN MI Jul 19 '16 at 19:31
0

Alas, not much that would explain your issue.

Observations:

Version: 4.0.17-nt
2 GB of RAM
Uptime = 05:57:03; some GLOBAL STATUS values may not be meaningful yet.
You are running on Windows.
You appear to be running entirely (or mostly) MyISAM.

The More Important Issues

When the tables are all MyISAM, key_buffer_size should be about 20% of available RAM. Perhaps 100M in your case. (It is currently 8M)

Sounds like some of the tables are missing indexes that would benefit the queries. This also impacts how much RAM appears to be in use. Show us some of the queries; we may be able to help. (Also include SHOW CREATE TABLE.)

Details and other observations

( innodb_buffer_pool_size / _ram ) = 8M / 2048M = 0.39% -- % of RAM used for InnoDB buffer_pool

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (8388600 / 0.20 + 8M / 0.70) / 2048M = 2.5% -- Most of available ram should be made available for caching. -- http://mysql.rjweb.org/doc.php/memory

( open_files_limit ) = 0 -- ulimit -n -- To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent)

( table_cache ) = 64 -- Number of table descriptors to cache. The name changed to table_open_cache in 5.1.3. -- Several hundred is usually good. (If you don't have many tables, then 64 may be fine.)

( Open_tables / table_cache ) = 0 / 64 = 0 -- Cache usage (open tables + tmp tables) -- Optionally lower table_cache

( Select_scan / Com_select ) = 2,691 / 15736 = 17.1% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

|improve this answer|||||
  • thanks alot from this reply i'll add some query n tables above . – JORDAN MI Jul 19 '16 at 23:02
  • i added some query n table designs – JORDAN MI Jul 19 '16 at 23:09
  • Hmmm... Those are simple queries on simple tables, with good indexes. How many rows in the tables? Any other clues? – Rick James Jul 20 '16 at 0:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.