Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Location
    GA, USA
    Posts
    77

    Minimizing mysql memory usage.

    Now, for starters I know this can be a bottleneck. Fact is, my site is beyond small, I'm talking miniscule. I'm currently running on a 256mb VPS and java and mysql will not play nicely with each other . The main problem is that my VPS provider does not have swap enabled, so all virtual memory is taken from the real/resident memory. Mysql uses 16mb resident and 125mb virtual, totalling ~150mb of memory usage. Ideally, I'd like to chop this virtual memory off, but I don't know what else to do as I've already modifed my config file to the best of my knowledge.

    So I have this small java app which uses mysql, so mysql has to be started first, but then mysql has already chomped such a gaping hole in the ram that the JRE will not boot. If I run the java app without mysql started, the JRE will actually startup, but it's useless then because the database is mysql .

    Config file:
    Code:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1
    skip-locking
    key_buffer = 16K
    max_allowed_packet = 1M
    table_cache = 4
    sort_buffer_size = 64K
    read_buffer_size = 256K
    read_rnd_buffer_size = 256K
    net_buffer_length = 2K
    thread_stack = 64K
    server-id = 1
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
    # The following options will be passed to all MySQL clients
    [client]
    port        = 3306
    socket      = /var/lib/mysql/mysql.sock
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    
    [isamchk]
    key_buffer = 8M
    sort_buffer_size = 8M
    
    [myisamchk]
    key_buffer = 8M
    sort_buffer_size = 8M
    
    [mysqlhotcopy]
    interactive-timeout

  2. #2
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,294
    Cut down on the max connections. Stock it is 100 I believe.
    Steven Ciaburri | Industry's Best Server Management - Rack911.com
    Software Auditing - 400+ Vulnerabilities Found - Quote @ https://www.RACK911Labs.com
    Fully Managed Dedicated Servers (Las Vegas, New York City, & Amsterdam) (AS62710)
    FreeBSD & Linux Server Management, Security Auditing, Server Optimization, PCI Compliance

  3. #3
    Quote Originally Posted by daz07 View Post
    Now, for starters I know this can be a bottleneck. Fact is, my site is beyond small, I'm talking miniscule. I'm currently running on a 256mb VPS and java and mysql will not play nicely with each other . The main problem is that my VPS provider does not have swap enabled, so all virtual memory is taken from the real/resident memory. Mysql uses 16mb resident and 125mb virtual, totalling ~150mb of memory usage. Ideally, I'd like to chop this virtual memory off, but I don't know what else to do as I've already modifed my config file to the best of my knowledge.
    Two steps:

    • skip-innodb in mysql config
    • "ulimit -s 128" in MySQL / Java startup script (in init.d)

    Your virtual memory usage will drop drastically.

  4. #4
    Join Date
    Jul 2009
    Posts
    178
    Best way is to enable Mysql caching.. This can even reduced load on servers and increase speed.

  5. #5
    Join Date
    Jun 2007
    Location
    GA, USA
    Posts
    77
    Quote Originally Posted by cieslakd View Post
    Two steps:

    • skip-innodb in mysql config
    • "ulimit -s 128" in MySQL / Java startup script (in init.d)

    Your virtual memory usage will drop drastically.
    That worked wonders on MySQL. Thanks a ton.

    However, java appears to be the bottleneck, sucking up all available ram with and without setting the ulimit. Server's got about 180mb free with mysql running, and I even tried tightening Java's belt further by setting the ulimit to 64, but ulimit doesn't seem to have an affect at all.

    @eth10 : Well no need now, really, but yes I think MySQL caching would use more ram (possibly moving it off the virtual), but I won't bother just yet

    Well, I suppose I might have to go poke around sun's forums for tips on shrinking java's mem usage..

Similar Threads

  1. CPU/Memory/MySQL Usage like in whm?
    By linuxfan in forum Hosting Software and Control Panels
    Replies: 2
    Last Post: 06-02-2009, 11:33 AM
  2. Mysql memory usage on my VPS
    By kaon in forum VPS Hosting
    Replies: 9
    Last Post: 07-22-2008, 09:37 AM
  3. MySQL memory usage
    By balidragon in forum VPS Hosting
    Replies: 7
    Last Post: 08-26-2007, 02:12 AM
  4. MySQL Cpu / Memory usage
    By alphix in forum Hosting Security and Technology
    Replies: 3
    Last Post: 03-10-2007, 10:15 AM
  5. CPU/Memory/MySQL Usage?
    By blue__wind in forum Dedicated Server
    Replies: 1
    Last Post: 05-30-2004, 12:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •