Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a MySQL DB with mostly InnoDB tables that I'm fairly certain isn't tuned properly based on what's happening, but I don't have enough know how to pinpoint what to change. The server is a 8G 32b Linux system with the following in my.cnf:

[mysql]
port                           = 3306
socket                         = /var/run/mysqld/mysqld.sock
[mysqld]
user                           = mysql
default_storage_engine         = InnoDB
socket                         = /var/run/mysqld/mysqld.sock
pid_file                       = /var/run/mysqld/mysqld.pid
key_buffer_size                = 64M
myisam_recover                 = FORCE,BACKUP
max_allowed_packet             = 16M
max_connect_errors             = 1000000
datadir                        = /var/lib/mysql/
tmpdir                         = /tmp
tmp_table_size                 = 64M
max_heap_table_size            = 64M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 200
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 8192
table_open_cache               = 8192
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 128M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 2G
log_error                      = /var/log/mysql/mysql-error.log
log_queries_not_using_indexes  = 0
slow_query_log_file            = /var/log/mysql/mysql-slow.log
slow_query_log                 = 1
long_query_time                = 2
general_log                    = 0
general_log_file               = /var/log/mysql/general.log
[isamchk]
key_buffer                     = 16M
[mysqldump]
quick
quote-names
max_allowed_packet             = 16M

Currently, here are the non-zero global status stats:

Aborted_clients 28
Aborted_connects    667
Bytes_received  283596894
Bytes_sent  3709581404
Com_admin_commands  24456
Com_change_db   10267
Com_delete  167
Com_insert  1355
Com_kill    1
Com_select  1018481
Com_set_option  19563
Com_show_processlist    74
Com_show_status 30
Com_show_table_status   1
Com_show_tables 22
Com_show_variables  5
Com_update  2208
Connections 11157
Created_tmp_disk_tables 5131
Created_tmp_files   6
Created_tmp_tables  11044
Flush_commands  1
Handler_commit  1019009
Handler_delete  160
Handler_read_first  29551
Handler_read_key    3051320
Handler_read_last   3
Handler_read_next   5038745
Handler_read_prev   251210
Handler_read_rnd    685831
Handler_read_rnd_next   22756239
Handler_rollback    38
Handler_update  1166988
Handler_write   557183
Innodb_adaptive_hash_cells  8850419
Innodb_adaptive_hash_heap_buffers   1630
Innodb_adaptive_hash_hash_searches  4990439
Innodb_adaptive_hash_non_hash_searches  4315600
Innodb_background_log_sync  4145
Innodb_buffer_pool_pages_data   129440
Innodb_buffer_pool_pages_dirty  4
Innodb_buffer_pool_pages_flushed    9952
Innodb_buffer_pool_pages_LRU_flushed    237
Innodb_buffer_pool_pages_made_young 273289
Innodb_buffer_pool_pages_misc   1631
Innodb_buffer_pool_pages_old    47761
Innodb_buffer_pool_pages_total  131071
Innodb_buffer_pool_read_ahead   607
Innodb_buffer_pool_read_ahead_evicted   1325
Innodb_buffer_pool_read_requests    35806735
Innodb_buffer_pool_reads    373297
Innodb_buffer_pool_write_requests   30891
Innodb_checkpoint_age   365
Innodb_checkpoint_max_age   216721613
Innodb_checkpoint_target_age    209949063
Innodb_data_fsyncs  5575
Innodb_data_read    1834913792
Innodb_data_reads   401613
Innodb_data_writes  17424
Innodb_data_written 332080128
Innodb_dblwr_pages_written  9952
Innodb_dblwr_writes 431
Innodb_dict_tables  27606
Innodb_history_list_length  1979
Innodb_ibuf_free_list   9
Innodb_ibuf_merged_delete_marks 13
Innodb_ibuf_merged_deletes  3
Innodb_ibuf_merged_inserts  201
Innodb_ibuf_merges  144
Innodb_ibuf_segment_size    11
Innodb_ibuf_size    1
Innodb_log_write_requests   5819
Innodb_log_writes   6591
Innodb_lsn_current  77982531271
Innodb_lsn_flushed  77982531271
Innodb_lsn_last_checkpoint  77982530906
Innodb_master_thread_1_second_loops 4131
Innodb_master_thread_10_second_loops    411
Innodb_master_thread_background_loops   15
Innodb_master_thread_main_flush_loops   15
Innodb_master_thread_sleeps 4130
Innodb_max_trx_id   576268483
Innodb_mem_adaptive_hash    62128140
Innodb_mem_dictionary   109012014
Innodb_mem_total    2179465216
Innodb_mutex_os_waits   779
Innodb_mutex_spin_rounds    36022
Innodb_mutex_spin_waits 5369
Innodb_oldest_view_low_limit_trx_id 576268482
Innodb_os_log_fsyncs    3498
Innodb_os_log_written   5761024
Innodb_page_size    16384
Innodb_pages_created    94
Innodb_pages_read   374004
Innodb_pages_written    9952
Innodb_purge_trx_id 576267172
Innodb_rows_deleted 160
Innodb_rows_inserted    1323
Innodb_rows_read    28554644
Innodb_rows_updated 2078
Innodb_s_lock_os_waits  1278
Innodb_s_lock_spin_rounds   40952
Innodb_s_lock_spin_waits    2563
Innodb_x_lock_os_waits  132
Innodb_x_lock_spin_rounds   4893
Innodb_x_lock_spin_waits    176
Key_blocks_unused   57983
Key_blocks_used 10
Key_read_requests   6724
Key_reads   7
Key_write_requests  1441
Max_used_connections    25
Open_files  11
Open_table_definitions  8192
Open_tables 8192
Opened_files    138868
Opened_table_definitions    117810
Opened_tables   126475
Queries 1062631
Questions   1062631
Select_full_join    1211
Select_range    17271
Select_scan 27709
Slow_queries    4
Sort_range  215835
Sort_rows   723648
Sort_scan   20726
Table_locks_immediate   1055875
Threads_cached  21
Threads_connected   4
Threads_created 25
Threads_running 1
Uptime  4148
Uptime_since_flush_status   4148

And finally, ulimit -a:

ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 64613
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 64613
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

Does anything pop out that is clearly mis-configured?

share|improve this question

closed as too localized by RolandoMySQLDBA, Mark Storey-Smith, Max Vernon, Derek Downey, StanleyJohns Apr 22 '13 at 15:17

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Is the server dedicated only to MySQL, or are you running other services (such as Apache) on it? –  Michael - sqlbot Mar 23 '13 at 0:56
    
Unfortunately the whole stack is on this system, which includes Apache. –  user21649 Mar 23 '13 at 1:10
    
I also just updated the original post to reflect the fact that it's an 8G system (not 4G like I originally posted). –  user21649 Mar 23 '13 at 1:13
    
Where are you seeing OOM messages? Is this syslog messages from the kernel (e.g. in /var/log/messages or /var/log/syslog), or messages in the MySQL error log itself? –  Michael - sqlbot Mar 23 '13 at 1:59
    
Both the segfault (error 11) and OOM errors are from MySQL directly. –  user21649 Mar 23 '13 at 2:05
add comment

1 Answer 1

There's nothing really standing out as being poorly-tuned. The innodb_buffer_pool_size is, quite possibly, the single most important configuration parameter for optimum performance, and the general rule is that you want it as large as possible, as much as 80% of system memory on a dedicated server (unless, of course, you have a very small data set)... but on a system where you're sharing resources with anything, and especially Apache, it has to be much, much smaller.

In your case, 2G or 25% of system memory, seems like it should be perfectly reasonable in theory, but it might not be true in your case.

The memory for the InnoDB buffer pool is requested from the operating system in a single block when MySQL starts, and this block never grows or shrinks... so changing this parameter will, without exception, change the total baseline amount of memory MySQL requires, with no connection at all to the size of your data set or your load. Of course, reducing the size of the InnoDB buffer pool will free up some memory but will also negatively impact performance.

Many of MySQL's other memory needs are handled by allocating and freeing memory from the OS as needed, and the bottom line is that it will request exactly the amount of information from the operating system that it needs, in order to handle its workload:

"The truth is that you can’t put an upper bound on MySQL’s memory consumption. It is not a tightly regulated database server that controls memory allocation."

Schwartz, Baron; Zaitsev, Peter; Tkachenko, Vadim (2012-03-05). High Performance MySQL: Optimization, Backups, and Replication (Kindle Locations 12193-12194). OReilly Media - A. Kindle Edition.

Of course, the exact same thing is true about Apache.

You can, and should, control the MaxRequestWorkers setting so that your server does not spawn so many children it starts swapping. This procedure for doing this is simple: determine the size of your average Apache process, by looking at your process list via a tool such as top, and divide this into your total available memory, leaving some room for other processes.

http://httpd.apache.org/docs/2.4/misc/perf-tuning.html

Well, "simple" seems like a bit of an overstatement, since if you don't allow sufficient Apache worker threads to meet your application's demands, your performance will deteriorate here, too.

It is, of course, very bad for a server running MySQL or Apache to start swapping; however, this doesn't mean you don't need swap space... you should have it for emergencies (light swapping is much better than crashing, after all)... so if you don't have it, you should... and if you do -- and that's getting maxed too -- then you actually already had a significant problem on your hands before the crashes began.

I recently wrote a simple stored procedure intended to crash MySQL by maxing out the system's memory, only to find that "merely" running out of memory is not actually enough to crash MySQL. Review of the source revealed that many out of memory events are handled quite gracefully, with only (for example) a query failing, not a complete meltdown... but the fact that many such events are handled well does not by any means suggest that all such events are, or even necessarily could be.

The bottom line is that unless you can find some cruft, you may simply be trying to do too much with too little... but activating some swap space (if you haven't already), then trying to tame the Apache children, and then reducing the buffer pool size would be the places I would start.

share|improve this answer
    
Thanks for the informative comment! I did add swap already as you suggested, and Apache children were already under control. But reducing the buffer pool, while after thinking about it makes total sense, is not something I considered. I'll pursue that as well, thanks! –  user21649 Mar 23 '13 at 8:01
add comment

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