mysql5.5 use 7 times memory after 2 days

  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql5.5 use 7 times memory after 2 days

    here are the key memory variables i set for the my.cnf

    innodb_buffer_pool_size = 2G
    innodb_additional_mem_pool_size = 64M
    innodb_log_file_size = 256M
    innodb_log_buffer_size = 32M
    innodb_file_per_table = 1
    innodb_thread_concurrency = 200
    read_buffer_size = 128k
    sort_buffer_size = 128k
    read_rnd_buffer_size = 512k

    The mysql server use about 2.7g at startup and after 2 day's of running, the memory go up to 14g.

    It's a heavy cpu load game server database, always have 40% cpu usage. there are hundreds of innodb table in the database and only 2 memory table which is writen to in 2 time per second. there are simultanous 374 long connections connection to one database.

    I have read many article on percona about memory usage about mysql and haven't figure out what's wrong with my server.

  • #2
    The memory table could become fragmented. Could you try ALTER TABLE ... ENGINE=Memory; on the memory tables? This should not drop any data, but recreates the table.


    • #3
      Some extra information:
      1. i have just switched from mysql 5.1.52 to mysql 5.5.14.
      2. i have just turn on per table table space and move some table to another hard disk on the server to scatter the io load.
      3. this situation happen on Percona Server 5.5.14 too
      4. the server has a middle load of sort query on average of 2000 result rows.
      5. the server has totally 2368900 prepared statements for all connections.
      6. 1 months ago, the server use 5g memory steadly for months.


      • #4

        i have before try alter the engine of all memory table to innodb and change it back to the memory engine . things doesn't change.


        • #5

          and the two memory table only have no more than 15 rows which stored cpu usage of all core in the game cluster.


          • #6
            If you don't know how to profile the memory usage of the server, I'd hire someone who does You can look at pmap -x as a start. Measure, don't guess. Otherwise you could spend forever tweaking things, causing more harm.