| 283 | | if ($mystat{'Uptime'} < 86400) { |
|---|
| 284 | | badprint "MySQL has been recently restarted - results cannot be trusted\n"; |
|---|
| 285 | | } |
|---|
| 286 | | } |
|---|
| 287 | | |
|---|
| 288 | | sub check_memory { |
|---|
| 289 | | titleprint "------ MEMORY USAGE ------\n"; |
|---|
| 290 | | # The purpose of this section is to make sure you're not going to end up in swap or crashing the box |
|---|
| 291 | | # by having buffers that are set too large |
|---|
| 292 | | # |
|---|
| 293 | | # PER-THREAD BUFFERS: |
|---|
| 294 | | # join_buffer_size - helps joins that don't use indexes (default 128M) |
|---|
| 295 | | my $join_buffer_size = $myvar{'join_buffer_size'}; |
|---|
| 296 | | # read_buffer_size - for sequential scans (default 128K) |
|---|
| 297 | | my $read_buffer_size = $myvar{'read_buffer_size'}; |
|---|
| 298 | | # read_rnd_buffer_size - helps ORDER BY (default 256K) |
|---|
| 299 | | my $read_rnd_buffer_size = $myvar{'read_rnd_buffer_size'}; |
|---|
| 300 | | # sort_buffer_size - helps SORT/ORDER BY (default 2M) |
|---|
| 301 | | my $sort_buffer_size = $myvar{'sort_buffer_size'}; |
|---|
| 302 | | # thread_stack - stack size per thread (default 192K) [MySQL says not to adjust this] |
|---|
| 303 | | my $thread_stack = $myvar{'thread_stack'}; |
|---|
| 304 | | # |
|---|
| 305 | | # PER-THREAD BUFFER CALCULATIONS: |
|---|
| 306 | | my $thread_buffers = $read_buffer_size + $read_rnd_buffer_size + $sort_buffer_size + $thread_stack + $join_buffer_size; |
|---|
| 307 | | my $total_thread_buffers = $thread_buffers * $myvar{'max_connections'}; |
|---|
| 308 | | my $max_thread_buffers = $thread_buffers * $mystat{'Max_used_connections'}; |
|---|
| 309 | | # |
|---|
| 310 | | # GLOBAL BUFFERS: |
|---|
| 311 | | # innodb_buffer_pool_size - general buffer for InnoDB (default 8M) |
|---|
| 312 | | my $innodb_buffer_pool_size = (defined $myvar{'innodb_buffer_pool_size'}) ? $myvar{'innodb_buffer_pool_size'} : 0 ; |
|---|
| 313 | | # innodb_additional_mem_pool_size - stores internal InnoDB table data (default 1M) |
|---|
| 314 | | my $innodb_additional_mem_pool_size = (defined $myvar{'innodb_additional_mem_pool_size'}) ? $myvar{'innodb_additional_mem_pool_size'} : 0 ; |
|---|
| 315 | | # innodb_log_buffer_size - holds InnoDB log before writing to disk (default 1M) |
|---|
| 316 | | my $innodb_log_buffer_size = (defined $myvar{'innodb_log_buffer_size'}) ? $myvar{'innodb_log_buffer_size'} : 0 ; |
|---|
| 317 | | # key_buffer_size - holds all index data from MyISAM tables (default 8M) |
|---|
| 318 | | my $key_buffer_size = $myvar{'key_buffer_size'}; |
|---|
| 319 | | # query_cache_size - holds query results (default 0) [still allocated when query_cache_type = 0] |
|---|
| 320 | | my $query_cache_size = (defined $myvar{'query_cache_size'}) ? $myvar{'query_cache_size'} : 0 ; |
|---|
| 321 | | # max_heap_table_size / tmp_table_size - hold temporary table data from queries |
|---|
| 322 | | # ** The effective temporary table size is the smaller number between these two |
|---|
| 323 | | my $eff_tmp_table_size; |
|---|
| 324 | | if ($myvar{'tmp_table_size'} > $myvar{'max_heap_table_size'}) { |
|---|
| 325 | | $eff_tmp_table_size = $myvar{'max_heap_table_size'}; |
|---|
| 326 | | } else { |
|---|
| 327 | | $eff_tmp_table_size = $myvar{'tmp_table_size'}; |
|---|
| 328 | | } |
|---|
| 329 | | # |
|---|
| 330 | | # GLOBAL BUFFER CALCULATIONS: |
|---|
| 331 | | my $global_buffers = $innodb_buffer_pool_size + $innodb_additional_mem_pool_size + $innodb_log_buffer_size + $key_buffer_size + $query_cache_size + $eff_tmp_table_size; |
|---|
| 332 | | # |
|---|
| 333 | | # FINAL BUFFER/MEMORY CALCULATIONS: |
|---|
| 334 | | my $max_memory = $global_buffers + $max_thread_buffers; |
|---|
| 335 | | my $total_memory = $global_buffers + $total_thread_buffers; |
|---|
| 336 | | my $pct_physical_memory = int(($total_memory * 100) / $physical_memory); |
|---|
| 337 | | |
|---|
| 338 | | infoprint "Per-thread buffers are ".hr_bytes_rnd($thread_buffers).", total ".hr_bytes_rnd($total_thread_buffers). |
|---|
| 339 | | " ($myvar{'max_connections'} connections)\n"; |
|---|
| 340 | | infoprint "Max ever allocated is ".hr_bytes_rnd($max_memory)." (".hr_bytes_rnd($thread_buffers). |
|---|
| 341 | | " per-thread * $mystat{'Max_used_connections'} connections + ".hr_bytes_rnd($global_buffers)." global)\n"; |
|---|
| 342 | | if ($pct_physical_memory > 85) { |
|---|
| 343 | | badprint "DANGER - MySQL is configured to use $pct_physical_memory% (".hr_bytes($total_memory). |
|---|
| 344 | | ") of physical memory (".hr_bytes($physical_memory).")\n"; |
|---|
| 345 | | $exptext = "Your current memory settings are dangerous as they exceed the maximum amount of memory on your ". |
|---|
| 346 | | "server if it was running at full capacity. By reducing your current memory usage, you will decrease the ". |
|---|
| 347 | | "chances of out of memory errors and general instability."; |
|---|
| 348 | | } else { |
|---|
| 349 | | goodprint "MySQL is configured to use $pct_physical_memory% (".hr_bytes($total_memory). |
|---|
| 350 | | ") of physical memory (".hr_bytes($physical_memory).")\n"; |
|---|
| 351 | | $exptext = "Your current memory settings are reasonable as they do not exceed the maximum amount of memory on ". |
|---|
| 352 | | "your server. If you find that this script suggests increasing buffers in later tests, you may not have additional memory ". |
|---|
| 353 | | "available for the expansion of those buffers."; |
|---|
| 354 | | } |
|---|
| 355 | | explainprint "This script's calculations show MySQL's memory usage at full capacity. ". |
|---|
| 356 | | "This means that all connections are at their maximum with all buffers being fully used. ".$exptext; |
|---|
| 357 | | |
|---|
| 358 | | } |
|---|
| 359 | | |
|---|
| 360 | | sub check_slow_queries { |
|---|
| 361 | | titleprint "------ SLOW QUERIES ------\n"; |
|---|
| 362 | | # If the server hasn't received any queries, then we can't calculate a slow query percentage |
|---|
| 363 | | if ($mystat{'Questions'} > 0) { |
|---|
| 364 | | my $slowquerypct = int(($mystat{'Slow_queries'}/$mystat{'Questions'}) * 100); |
|---|
| 365 | | if ($slowquerypct > 5) { |
|---|
| 366 | | badprint "$slowquerypct% of all queries take more than ".$myvar{'long_query_time'}." sec - optimization is recommended\n"; |
|---|
| 367 | | } elsif ($slowquerypct <= 5 && $slowquerypct >= 1) { |
|---|
| 368 | | goodprint "$slowquerypct% of all queries take more than ".$myvar{'long_query_time'}." sec\n"; |
|---|
| 369 | | } else { |
|---|
| 370 | | goodprint "Less than 1% of all queries take more than ".$myvar{'long_query_time'}." sec\n"; |
|---|
| 371 | | } |
|---|
| 372 | | } |
|---|
| 373 | | # Best case scenario would be slow query log enabled with a long_query_time of 10 or less |
|---|
| 374 | | if ($myvar{'log_slow_queries'} =~ /ON/) { |
|---|
| 375 | | if ($myvar{'long_query_time'} <= 10) { |
|---|
| 376 | | goodprint "Slow query log is enabled, and long_query_time is reasonable ($myvar{'long_query_time'} sec)\n"; |
|---|
| 377 | | $exptext = "Your slow query settings are already at an optimal level."; |
|---|
| 378 | | } else { |
|---|
| 379 | | print $bad. " Slow query log is enabled, but long_query_time is too long ($myvar{'long_query_time'} sec)\n"; |
|---|
| 380 | | $exptext = "Although your slow query log is enabled, a query must exceed $myvar{'long_query_time'} seconds ". |
|---|
| 381 | | "to appear in the log which is much too long. Reduce the long_query_time to 10 or less to make your ". |
|---|
| 382 | | "slow query log more effective."; |
|---|
| 383 | | } |
|---|
| 384 | | } else { |
|---|
| 385 | | if ($myvar{'long_query_time'} <= 10) { |
|---|
| 386 | | badprint "Slow query log is disabled, but long_query_time is reasonable ($myvar{'long_query_time'} sec)\n"; |
|---|
| 387 | | $exptext = "While your long_query_time is set to a value less than 10, your slow query log is currently ". |
|---|
| 388 | | "disabled. This will prevent you from auditing your slow queries."; |
|---|
| 389 | | } else { |
|---|
| 390 | | badprint "Slow query log is disabled, and long_query_time is too long ($myvar{'long_query_time'} sec)\n"; |
|---|
| 391 | | $exptext = "To audit your server's slow queries, you should enable the slow query log and reduce your ". |
|---|
| 392 | | "long_query_time to 10 seconds or less."; |
|---|
| 393 | | } |
|---|
| 394 | | } |
|---|
| 395 | | explainprint "The slow query log will allow you to see which queries are taking too long to execute. ".$exptext; |
|---|
| 396 | | } |
|---|
| 397 | | |
|---|
| 398 | | sub check_connections { |
|---|
| 399 | | titleprint "------ CONNECTION LIMITS ------\n"; |
|---|
| 400 | | # We're looking at two things here: |
|---|
| 401 | | # How many connections have been used so far and how close is the connection limit? |
|---|
| 402 | | # How many connections can overflow into the back_log? |
|---|
| 403 | | # |
|---|
| 404 | | # If the maximum connections used is over 85% of the limit, that's a little close. |
|---|
| 405 | | # However, if the maximum connections used is less than 10%, that's just wasted memory. |
|---|
| 406 | | my $connpct = int(($mystat{'Max_used_connections'}/$myvar{'max_connections'}) * 100); |
|---|
| 407 | | if ($connpct > 85) { |
|---|
| 408 | | badprint "$connpct% of connections have been used ". |
|---|
| 409 | | "(".$mystat{'Max_used_connections'}."/".$myvar{'max_connections'}.")". |
|---|
| 410 | | " - Increase the max_connections variable\n"; |
|---|
| 411 | | $exptext = "Your historical connection usage is too high relative to your set maximum. The max_connections variable should be increased."; |
|---|
| 412 | | } elsif ($connpct < 10) { |
|---|
| 413 | | badprint "$connpct% of connections have been used ". |
|---|
| 414 | | "(".$mystat{'Max_used_connections'}."/".$myvar{'max_connections'}.")". |
|---|
| 415 | | " - Reduce max_connections\n"; |
|---|
| 416 | | $exptext = "Your historical connection usage is voo low relative to your set maximum. MySQL is wasting resources by allowing". |
|---|
| 417 | | " so many concurrent connections."; |
|---|
| 418 | | } else { |
|---|
| 419 | | goodprint "$connpct% of connections have been used ". |
|---|
| 420 | | "(".$mystat{'Max_used_connections'}."/".$myvar{'max_connections'}.")\n"; |
|---|
| 421 | | $exptext = "These settings are appropriate for your historical usage."; |
|---|
| 422 | | } |
|---|
| 423 | | # If the back_log is less than 50, there's a chance that connections will be forcefully rejected |
|---|
| 424 | | if ($myvar{'back_log'} < 50) { |
|---|
| 425 | | badprint "Your listen queue back_log is too low - you should raise this to 50-256\n"; |
|---|
| 426 | | } else { |
|---|
| 427 | | goodprint "Your listen queue back_log is set to a reasonable level (".$myvar{'back_log'}.")\n"; |
|---|
| 428 | | } |
|---|
| 429 | | explainprint "You can currently handle ".($myvar{'max_connections'}+$myvar{'back_log'})." total connections, which includes ". |
|---|
| 430 | | $myvar{'max_connections'}." active connections with ".$myvar{'back_log'}." more in queue. ".$exptext; |
|---|
| 431 | | } |
|---|
| 432 | | |
|---|
| 433 | | sub check_key_buffer { |
|---|
| 434 | | titleprint "------ KEY BUFFER ------\n"; |
|---|
| 435 | | my $myisamindexes; |
|---|
| 436 | | if ($mysqlvermajor < 5) { |
|---|
| 437 | | $myisamindexes = `find $myvar{'datadir'} -name '*.MYI' 2>&1 | xargs du $duflags '{}' 2>&1 | awk '{ s += \$1 } END { print s }'`; |
|---|
| 438 | | if ($myisamindexes =~ /^0\n$/) { |
|---|
| 439 | | badprint "Unable to complete calculations - run this script with root privileges\n"; |
|---|
| 440 | | return 0; |
|---|
| 441 | | } |
|---|
| 442 | | } else { |
|---|
| 443 | | $myisamindexes = `mysql $mysqllogin -Bse "/*!50000 SELECT SUM(INDEX_LENGTH) from information_schema.TABLES where ENGINE='MyISAM' */"`; |
|---|
| 444 | | } |
|---|
| 445 | | chomp($myisamindexes); |
|---|
| 446 | | # There's several variables that come into play with regards to key buffers |
|---|
| 447 | | # Key_blocks_unused - number of unused key blocks in the key cache (will decrease over time) |
|---|
| 448 | | # Key_blocks_used - number of used blocks in the key cache (will increase over time) |
|---|
| 449 | | # Key_read_requests - number of requests to read a key block from the cache (this should be high) |
|---|
| 450 | | # Key_reads - number of requests for a key that had to be read from a disk (this should be low) |
|---|
| 451 | | if ($mystat{'Key_reads'} == 0) { |
|---|
| 452 | | badprint "Your queries are not using any indexes - no recommendations can be made\n"; |
|---|
| 453 | | return 0; |
|---|
| 454 | | } |
|---|
| 455 | | # BUFFER CALCULATIONS: |
|---|
| 456 | | # key_buffer_use_pct - how much of the key buffer is being used |
|---|
| 457 | | # if this gets too high, and key_buffer_size is less than the total |
|---|
| 458 | | # size of the MyISAM indexes, then it should be raised |
|---|
| 459 | | # key_from_mem_pct - the percent of key requests that come from the cache (rather than the disk) |
|---|
| 460 | | # if this starts to increase, the key_buffer_size is too small and |
|---|
| 461 | | # keys are being thrown out and replaced by other keys |
|---|
| 462 | | my $key_buffer_use_pct = sprintf("%.1f",(1 - (($mystat{'Key_blocks_unused'} * $myvar{'key_cache_block_size'}) / $myvar{'key_buffer_size'})) * 100); |
|---|
| 463 | | my $key_from_mem_pct = sprintf("%.1f",(100 - (($mystat{'Key_reads'} / $mystat{'Key_read_requests'}) * 100))); |
|---|
| 464 | | my $raise_key_buffer = 0; |
|---|
| 465 | | if ($key_buffer_use_pct >= 85) { |
|---|
| 466 | | $raise_key_buffer = 1 # Key buffer is almost full - raise it |
|---|
| 467 | | } elsif ($key_buffer_use_pct < 85 && $key_buffer_use_pct >= 25) { |
|---|
| 468 | | $raise_key_buffer = 0; # Key buffer is reasonable |
|---|
| 469 | | } else { |
|---|
| 470 | | $raise_key_buffer = -1; # Key buffer is too big - lower it |
|---|
| 471 | | } |
|---|
| 472 | | if ($key_from_mem_pct >= 95) { |
|---|
| 473 | | $raise_key_buffer = 0; # Key buffer is being utilized well, no need to change a thing |
|---|
| 474 | | } elsif ($key_from_mem_pct < 95 && $key_from_mem_pct >= 80) { |
|---|
| 475 | | $raise_key_buffer += 1; # Key buffer is probably set to the default, should be raised |
|---|
| 476 | | } elsif ($key_from_mem_pct < 80) { |
|---|
| 477 | | $raise_key_buffer += 2; # This is really, really bad - raise the buffer! |
|---|
| 478 | | } |
|---|
| 479 | | infoprint "The key buffer is $key_buffer_use_pct% used, and $key_from_mem_pct% of key requests come from memory\n"; |
|---|
| 480 | | if ($myisamindexes < $myvar{'key_buffer_size'} && $raise_key_buffer >= 1) { |
|---|
| 481 | | badprint "Your key_buffer_size (".hr_bytes_rnd($myvar{'key_buffer_size'}).") is higher than your total MyISAM indexes (".hr_bytes_rnd($myisamindexes).")\n"; |
|---|
| 482 | | } else { |
|---|
| 483 | | infoprint "Total MyISAM index size is ".hr_bytes_rnd($myisamindexes). |
|---|
| 484 | | " (current key_buffer_size ".hr_bytes_rnd($myvar{'key_buffer_size'}).")\n"; |
|---|
| 485 | | } |
|---|
| 486 | | if ($raise_key_buffer >= 1) { |
|---|
| 487 | | badprint "Raise the key_buffer_size for better indexing performance in MyISAM tables\n"; |
|---|
| 488 | | } elsif ($raise_key_buffer == 0) { |
|---|
| 489 | | goodprint "Your key_buffer_size is set to a reasonable level\n"; |
|---|
| 490 | | } else { |
|---|
| 491 | | badprint "Lower the key_buffer_size to use the resources elsewhere\n"; |
|---|
| 492 | | } |
|---|
| 493 | | if ($myvar{'max_seeks_for_key'} > 100) { |
|---|
| 494 | | badprint "Reduce max_seeks_for_key to force MySQL to prefer indexes over table scans\n"; |
|---|
| 495 | | } |
|---|
| 496 | | } |
|---|
| 497 | | |
|---|
| 498 | | sub check_query_cache { |
|---|
| 499 | | titleprint "------ QUERY CACHE ------\n"; |
|---|
| 500 | | # If query cache support isn't compiled into MySQL, we fail here |
|---|
| 501 | | if (!defined $myvar{'have_query_cache'}) { |
|---|
| 502 | | badprint "Your MySQL server does not support query caching - upgrade MySQL\n"; |
|---|
| 503 | | return 0; |
|---|
| 504 | | } |
|---|
| 505 | | # If the query cache is disabled, we fail here |
|---|
| 506 | | if ($myvar{'query_cache_size'} == 0) { |
|---|
| 507 | | badprint "The query cache is disabled - set the query_cache_size > 0 to enable it\n"; |
|---|
| 508 | | return 0; |
|---|
| 509 | | } |
|---|
| 510 | | # If there haven't been any selects, then we can't do much |
|---|
| 511 | | if ($mystat{'Com_select'} == 0) { |
|---|
| 512 | | infoprint "No SELECT statements have been run - no recommendations can be made\n"; |
|---|
| 513 | | return 0; |
|---|
| 514 | | } |
|---|
| 515 | | # QUERY CACHE VARIABLES/STATUS: |
|---|
| 516 | | # Com_select - number of SELECTS executed that didn't use the query cache |
|---|
| 517 | | # Qcache_free_memory - memory available for use in the query cache |
|---|
| 518 | | # Qcache_hits - number of queries that pulled a result from the query cache |
|---|
| 519 | | # Qcache_lowmem_prunes - amount of times MySQL has had to clear a full query cache |
|---|
| 520 | | # query_cache_limit - the biggest result allowed per query |
|---|
| 521 | | # query_cache_size - the size of the query cache (if it's 0, query cache is disabled) |
|---|
| 522 | | # |
|---|
| 523 | | # QUERY CACHE CALCULATIONS: |
|---|
| 524 | | # query_cache_efficiency - % of SELECTS that pull from query cache (should be > 20%) |
|---|
| 525 | | my $query_cache_efficiency = sprintf("%.1f",($mystat{'Qcache_hits'} / ($mystat{'Com_select'} + $mystat{'Qcache_hits'})) * 100); |
|---|
| 526 | | # qcache_pct_free - % of query cache free |
|---|
| 527 | | my $qcache_pct_free = sprintf("%.1f",($mystat{'Qcache_free_memory'} / $myvar{'query_cache_size'}) * 100); |
|---|
| 528 | | my $problem = 0; |
|---|
| 529 | | infoprint "$query_cache_efficiency% of SELECTS used query cache (Stats: $qcache_pct_free% free, " |
|---|
| 530 | | .hr_bytes_rnd($myvar{'query_cache_size'})." total, $mystat{'Qcache_lowmem_prunes'} prunes)\n"; |
|---|
| 531 | | # When the prunes get to be pretty high, either too many queries are making it into the query cache |
|---|
| 532 | | # that don't belong there, or the queries are too big and the query_cache_limit needs to be reduced. |
|---|
| 533 | | if ($mystat{'Qcache_lowmem_prunes'} > 50) { |
|---|
| 534 | | badprint "Cache cleared $mystat{'Qcache_lowmem_prunes'} times due to low memory - increase query_cache_size\n"; |
|---|
| 535 | | $problem = 1; |
|---|
| 536 | | } |
|---|
| 537 | | # If the query_cache_efficiency is less than 20%, then too many selects are not being pulled from |
|---|
| 538 | | # the query_cache. This could mean that the results exceed query_cache_limit, or the queries are |
|---|
| 539 | | # using the SQL_NO_CACHE modifier (which throw these recommendations way off). |
|---|
| 540 | | if ($query_cache_efficiency <= 20) { |
|---|
| 541 | | badprint "Very few queries used query cache - increase query_cache_limit or adjust queries\n"; |
|---|
| 542 | | $problem = 1; |
|---|
| 543 | | } |
|---|
| 544 | | # If the query_cache efficiency is over 20% and the prunes are under 50, then everything should be good. |
|---|
| 545 | | if ($problem == 0) { |
|---|
| 546 | | goodprint "Your query cache is configured properly\n"; |
|---|
| 547 | | } |
|---|
| 548 | | } |
|---|
| 549 | | |
|---|
| 550 | | sub check_sort { |
|---|
| 551 | | titleprint "------ SORTING ------\n"; |
|---|
| 552 | | # SORTING VARIABLES/STATUS: |
|---|
| 553 | | # read_rnd_buffer_size - sorts are read from this buffer after the sort is complete, helps ORDER BY |
|---|
| 554 | | # sort_buffer_size - per-thread buffer used for sorting, helps ORDER/GROUP BY |
|---|
| 555 | | # Sort_merge_passes - number of merge passes that the sort algorithm has had to do (bad if high, raise sort_buffer_size) |
|---|
| 556 | | # Sort_range - sorts done using ranges |
|---|
| 557 | | # Sort_scan - sorts done by scanning table |
|---|
| 558 | | # LOGIC: |
|---|
| 559 | | # If a SELECT does a sequential table scan, then Sort_scan gets incremented |
|---|
| 560 | | # If a SELECT retrieves a range of rows, then Sort_range gets incremented |
|---|
| 561 | | # If a sort is too big for the sort_buffer_size, temporary tables are made, sorted, and then Sort_merge_passes gets incremented |
|---|
| 562 | | my $total_sorts = $mystat{'Sort_scan'} + $mystat{'Sort_range'}; |
|---|
| 563 | | if ($total_sorts == 0) { |
|---|
| 564 | | infoprint "No sorts have been performed - no recommendations can be made\n"; |
|---|
| 565 | | return 0; |
|---|
| 566 | | } |
|---|
| 567 | | my $temp_sort_table_pct = int(($mystat{'Sort_merge_passes'} / $total_sorts) * 100); |
|---|
| 568 | | infoprint "$total_sorts sorts have occurred with $temp_sort_table_pct% requiring temporary tables\n"; |
|---|
| 569 | | if ($temp_sort_table_pct > 10) { |
|---|
| 570 | | badprint "Increase sorting performance by raising sort_buffer_size and read_rnd_buffer_size\n"; |
|---|
| 571 | | } else { |
|---|
| 572 | | goodprint "Your sorting buffers are reasonable\n"; |
|---|
| 573 | | } |
|---|
| 574 | | } |
|---|
| 575 | | |
|---|
| 576 | | sub check_join { |
|---|
| 577 | | titleprint "------ JOINS ------\n"; |
|---|
| 578 | | # JOIN VARIABLES/STATUS: |
|---|
| 579 | | # join_buffer_size - buffer used to join tables when indexes can't be utilized |
|---|
| 580 | | # Select_full_join - number of joins not using indexes |
|---|
| 581 | | # Select_range_check - number of joins where MySQL was uncertain on whether it could use indexes to find a range |
|---|
| 582 | | # LOGIC: |
|---|
| 583 | | # If Select_full_join or Select_range_check are greater than 0, then the join queries need to be fixed |
|---|
| 584 | | # Technically, the performance hit of Select_range_check is as bad as Select_full_join, so they should be summed |
|---|
| 585 | | my $joins_without_indexes = $mystat{'Select_range_check'} + $mystat{'Select_range_check'}; |
|---|
| 586 | | if ($joins_without_indexes > 0) { |
|---|
| 587 | | infoprint "$joins_without_indexes joins did not use indexes, join buffer is ".hr_bytes_rnd($myvar{'join_buffer_size'})."\n"; |
|---|
| 588 | | badprint "Adjust your joins to utilize indexes (if impossible, increase join_buffer_size)\n"; |
|---|
| 589 | | badprint "NOTE: This script will always suggest raising the join_buffer_size\n"; |
|---|
| 590 | | } else { |
|---|
| 591 | | goodprint "Your joins are using indexes appropriately, join buffer is not necessary\n"; |
|---|
| 592 | | } |
|---|
| 593 | | } |
|---|
| 594 | | |
|---|
| 595 | | sub check_temporary_tables { |
|---|
| 596 | | titleprint "------ TEMPORARY TABLES ------\n"; |
|---|
| 597 | | # TEMPORARY TABLE VARIABLES/STATUS: |
|---|
| 598 | | # Created_tmp_disk_tables - number of temporary tables created on disk |
|---|
| 599 | | # Created_tmp_tables - number of temporary tables created in memory |
|---|
| 600 | | # max_heap_table_size - maximum size for MEMORY tables (also limits tmp_table_size) |
|---|
| 601 | | # tmp_table_size - max size for all temporary tables in memory (limited by max_heap_table_size) |
|---|
| 602 | | # LOGIC: |
|---|
| 603 | | # If the ratio of Created_tmp_disk_tables to Created_tmp_tables increases, this is bad |
|---|
| 604 | | # To reduce temporary tables on disk, the tmp_table_size needs to be increased (and possibly max_heap_table_size) |
|---|
| 605 | | if ($mystat{'Created_tmp_tables'} == 0) { |
|---|
| 606 | | infoprint "No temporary tables have been created since the server started\n"; |
|---|
| 607 | | return 0; |
|---|
| 608 | | } |
|---|
| 609 | | my $tmp_disk_pct = int(($mystat{'Created_tmp_disk_tables'} / $mystat{'Created_tmp_tables'}) * 100); |
|---|
| 610 | | if ($tmp_disk_pct > 25) { |
|---|
| 611 | | badprint "$tmp_disk_pct% of temp tables were created on disk\n"; |
|---|
| 612 | | badprint "Increase tmp_table_size (which is limited by max_heap_table_size)\n"; |
|---|
| 613 | | } else { |
|---|
| 614 | | goodprint "$tmp_disk_pct% of temp tables were created on disk - this is reasonable\n"; |
|---|
| 615 | | } |
|---|
| 616 | | } |
|---|
| 617 | | |
|---|
| 618 | | sub check_other_buffers { |
|---|
| 619 | | titleprint "------ OTHER BUFFERS ------\n"; |
|---|
| 620 | | # bulk_insert_buffer_size = buffer for large inserts (default: 8M) |
|---|
| 621 | | infoprint "Your bulk_insert_buffer_size is ".hr_bytes_rnd($myvar{'bulk_insert_buffer_size'}). |
|---|
| 622 | | " - increase this for large bulk inserts\n"; |
|---|
| 623 | | } |
|---|
| 624 | | |
|---|
| 625 | | sub performance_options { |
|---|
| 626 | | titleprint "------ PERFORMANCE OPTIONS ------\n"; |
|---|
| 627 | | # concurrent_insert = enables simultaneous insert/select on MyISAM tables w/o holes |
|---|
| 628 | | # It's ON/OFF in MySQL 4.1 and 0/1 in MySQL 5.x |
|---|
| 629 | | if ($myvar{'concurrent_insert'} eq "ON" || $myvar{'concurrent_insert'} > 0) { |
|---|
| 630 | | goodprint "Concurrent inserts/selects in MyISAM tables are enabled\n"; |
|---|
| 631 | | } else { |
|---|
| 632 | | badprint "Concurrent inserts/selects in MyISAM tables are disabled - enable concurrent_insert\n"; |
|---|
| | 351 | |
|---|
| | 352 | # Memory usage |
|---|
| | 353 | if ($mycalc{'pct_physical_memory'} > 85) { |
|---|
| | 354 | badprint "Maximum possible memory usage: ".hr_bytes($mycalc{'total_possible_used_memory'})." ($mycalc{'pct_physical_memory'}% of installed RAM)\n"; |
|---|
| | 355 | push(@generalrec,"Reduce your overall MySQL memory footprint for system stability"); |
|---|
| | 356 | } else { |
|---|
| | 357 | goodprint "Maximum possible memory usage: ".hr_bytes($mycalc{'total_possible_used_memory'})." ($mycalc{'pct_physical_memory'}% of installed RAM)\n"; |
|---|
| | 358 | } |
|---|
| | 359 | |
|---|
| | 360 | # Slow queries |
|---|
| | 361 | if ($mycalc{'pct_slow_queries'} > 5) { |
|---|
| | 362 | badprint "Slow queries: $mycalc{'pct_slow_queries'}%\n"; |
|---|
| | 363 | } else { |
|---|
| | 364 | goodprint "Slow queries: $mycalc{'pct_slow_queries'}%\n"; |
|---|
| | 365 | } |
|---|
| | 366 | if ($myvar{'long_query_time'} > 5) { push(@decvars,"long_query_time (<= 5)"); } |
|---|
| | 367 | |
|---|
| | 368 | # Connections |
|---|
| | 369 | if ($mycalc{'pct_connections_used'} > 85) { |
|---|
| | 370 | badprint "Highest connection usage: $mycalc{'pct_connections_used'}%\n"; |
|---|
| | 371 | push(@incvars,"max_connections (> ".$myvar{'max_connections'}.")"); |
|---|
| | 372 | push(@decvars,"wait_timeout (< ".$myvar{'wait_timeout'}.")","interactive_timeout (< ".$myvar{'interactive_timeout'}.")"); |
|---|
| | 373 | push(@generalrec,"Reduce or eliminate persistent connections to reduce connection usage") |
|---|
| | 374 | } else { |
|---|
| | 375 | goodprint "Highest usage of available connections: $mycalc{'pct_connections_used'}%\n"; |
|---|
| | 376 | } |
|---|
| | 377 | |
|---|
| | 378 | # Key buffer |
|---|
| | 379 | if ($mycalc{'total_myisam_indexes'} =~ /^fail$/) { |
|---|
| | 380 | badprint "Cannot calculate MyISAM index size - re-run script as root user\n"; |
|---|
| | 381 | } elsif ($mycalc{'total_myisam_indexes'} == "0") { |
|---|
| | 382 | badprint "None of your MyISAM tables are indexed - add indexes immediately\n"; |
|---|
| | 383 | } else { |
|---|
| | 384 | if ($myvar{'key_buffer_size'} < $mycalc{'total_myisam_indexes'}) { |
|---|
| | 385 | badprint "Key buffer size / total MyISAM indexes: ".hr_bytes($myvar{'key_buffer_size'})."/".hr_bytes($mycalc{'total_myisam_indexes'})."\n"; |
|---|
| | 386 | push(@incvars,"key_buffer_size (> ".hr_bytes($mycalc{'total_myisam_indexes'}).")"); |
|---|
| | 387 | } else { |
|---|
| | 388 | goodprint "Key buffer size / total MyISAM indexes: ".hr_bytes($myvar{'key_buffer_size'})."/".hr_bytes($mycalc{'total_myisam_indexes'})."\n"; |
|---|
| | 389 | } |
|---|
| | 390 | if ($mystat{'Key_read_requests'} > 0) { |
|---|
| | 391 | if ($mycalc{'pct_keys_from_mem'} < 95) { |
|---|
| | 392 | badprint "Key buffer hit rate: $mycalc{'pct_keys_from_mem'}%\n"; |
|---|
| | 393 | } else { |
|---|
| | 394 | goodprint "Key buffer hit rate: $mycalc{'pct_keys_from_mem'}%\n"; |
|---|
| | 395 | } |
|---|
| | 396 | } else { |
|---|
| | 397 | # For the sake of space, we will be quiet here |
|---|
| | 398 | # No queries have run that would use keys |
|---|
| | 399 | } |
|---|
| | 400 | } |
|---|
| | 401 | if ($mysqlvermajor > 3 && $myvar{'max_seeks_for_key'} > 100) { push(@decvars,"max_seeks_for_key (<= 100)"); } |
|---|
| | 402 | |
|---|
| | 403 | # Query cache |
|---|
| | 404 | if ($mysqlvermajor < 4) { |
|---|
| | 405 | # For the sake of space, we will be quiet here |
|---|
| | 406 | # MySQL versions < 4.01 don't support query caching |
|---|
| | 407 | push(@generalrec,"Upgrade MySQL to version 4+ to utilize query caching"); |
|---|
| | 408 | } elsif ($myvar{'query_cache_size'} < 1) { |
|---|
| | 409 | badprint "Query cache is disabled\n"; |
|---|
| | 410 | push(@incvars,"query_cache_size (>= 8M)"); |
|---|
| | 411 | } elsif ($mystat{'Com_select'} == 0) { |
|---|
| | 412 | badprint "Query cache cannot be analyzed - no SELECT statements executed\n"; |
|---|
| | 413 | } else { |
|---|
| | 414 | if ($mycalc{'query_cache_efficiency'} < 20) { |
|---|
| | 415 | badprint "Query cache efficiency: $mycalc{'query_cache_efficiency'}%\n"; |
|---|
| | 416 | push(@incvars,"query_cache_limit (> 1M, or use smaller result sets)"); |
|---|
| | 417 | } else { |
|---|
| | 418 | goodprint "Query cache efficiency: $mycalc{'query_cache_efficiency'}%\n"; |
|---|
| | 419 | } |
|---|
| | 420 | if ($mycalc{'query_cache_prunes_per_day'} > 98) { |
|---|
| | 421 | badprint "Query cache prunes per day: $mycalc{'query_cache_prunes_per_day'}\n"; |
|---|
| | 422 | push(@incvars,"query_cache_size (> ".hr_bytes_rnd($myvar{'query_cache_size'}).")") |
|---|
| | 423 | } else { |
|---|
| | 424 | goodprint "Query cache prunes per day: $mycalc{'query_cache_prunes_per_day'}\n"; |
|---|
| | 425 | } |
|---|
| | 426 | } |
|---|
| | 427 | |
|---|
| | 428 | # Sorting |
|---|
| | 429 | if ($mycalc{'total_sorts'} == 0) { |
|---|
| | 430 | # For the sake of space, we will be quiet here |
|---|
| | 431 | # No sorts have run yet |
|---|
| | 432 | } elsif ($mycalc{'pct_temp_sort_table'} > 10) { |
|---|
| | 433 | badprint "Sorts requiring temporary tables: $mycalc{'pct_temp_sort_table'}%\n"; |
|---|
| | 434 | push(@incvars,"sort_buffer_size (> ".hr_bytes_rnd($myvar{'sort_buffer_size'}).")"); |
|---|
| | 435 | push(@incvars,"read_rnd_buffer_size (> ".hr_bytes_rnd($myvar{'read_rnd_buffer_size'}).")"); |
|---|
| | 436 | } else { |
|---|
| | 437 | goodprint "Sorts requiring temporary tables: $mycalc{'pct_temp_sort_table'}%\n"; |
|---|
| | 438 | } |
|---|
| | 439 | |
|---|
| | 440 | # Joins |
|---|
| | 441 | if ($mycalc{'joins_without_indexes'} > 0) { |
|---|
| | 442 | badprint "Joins performed without indexes: $mycalc{'joins_without_indexes'}\n"; |
|---|
| | 443 | push(@incvars,"join_buffer_size (> ".hr_bytes($myvar{'join_buffer_size'}).", or always use indexes with joins)"); |
|---|
| | 444 | push(@generalrec,"Adjust your join queries to always utilize indexes"); |
|---|
| | 445 | } else { |
|---|
| | 446 | # For the sake of space, we will be quiet here |
|---|
| | 447 | # No joins have run without indexes |
|---|
| | 448 | } |
|---|
| | 449 | |
|---|
| | 450 | # Temporary tables |
|---|
| | 451 | if ($mystat{'Created_tmp_tables'} > 0) { |
|---|
| | 452 | if ($mycalc{'pct_temp_disk'} > 25) { |
|---|
| | 453 | badprint "Temporary tables created on disk: $mycalc{'pct_temp_disk'}%\n"; |
|---|
| | 454 | push(@incvars,"tmp_table_size (> ".hr_bytes_rnd($myvar{'tmp_table_size'}).")"); |
|---|
| | 455 | push(@incvars,"max_heap_table_size (> ".hr_bytes_rnd($myvar{'max_heap_table_size'}).")"); |
|---|
| | 456 | push(@generalrec,"Be sure that tmp_table_size/max_heap_table_size are equal"); |
|---|
| | 457 | } else { |
|---|
| | 458 | goodprint "Temporary tables created on disk: $mycalc{'pct_temp_disk'}%\n"; |
|---|
| | 459 | } |
|---|
| | 460 | } else { |
|---|
| | 461 | # For the sake of space, we will be quiet here |
|---|
| | 462 | # No temporary tables have been created |
|---|
| | 463 | } |
|---|
| | 464 | |
|---|
| | 465 | # Thread cache |
|---|
| | 466 | if ($mycalc{'thread_cache_hit_rate'} <= 50) { |
|---|
| | 467 | badprint "Thread cache hit rate: $mycalc{'thread_cache_hit_rate'}%\n"; |
|---|
| | 468 | } else { |
|---|
| | 469 | goodprint "Thread cache hit rate: $mycalc{'thread_cache_hit_rate'}%\n"; |
|---|
| | 470 | } |
|---|
| | 471 | |
|---|
| | 472 | # Table cache |
|---|
| | 473 | if ($mystat{'Open_tables'} > 0) { |
|---|
| | 474 | if ($mycalc{'table_cache_hit_rate'} < 20) { |
|---|
| | 475 | badprint "Table cache hit rate: $mycalc{'table_cache_hit_rate'}%\n"; |
|---|
| | 476 | push(@incvars,"table_cache (> ".$myvar{'table_cache'}.")"); |
|---|
| | 477 | push(@generalrec,"Increase table_cache gradually to avoid file descriptor limits"); |
|---|
| | 478 | } else { |
|---|
| | 479 | goodprint "Table cache hit rate: $mycalc{'table_cache_hit_rate'}%\n"; |
|---|
| | 480 | } |
|---|
| | 481 | } |
|---|
| | 482 | |
|---|
| | 483 | # Open files |
|---|
| | 484 | if ($myvar{'open_files_limit'} > 0) { |
|---|
| | 485 | if ($mycalc{'pct_files_open'} > 85) { |
|---|
| | 486 | badprint "Open file limit used: $mycalc{'pct_files_open'}%\n"; |
|---|
| | 487 | push(@incvars,"open_files_limit (> ".$myvar{'open_files_limit'}.")"); |
|---|
| | 488 | } else { |
|---|
| | 489 | goodprint "Open file limit used: $mycalc{'pct_files_open'}%\n"; |
|---|
| | 490 | } |
|---|
| | 491 | } |
|---|
| | 492 | |
|---|
| | 493 | # Table locks |
|---|
| | 494 | if (defined $mycalc{'pct_table_locks_immediate'}) { |
|---|
| | 495 | if ($mycalc{'pct_table_locks_immediate'} < 95) { |
|---|
| | 496 | badprint "Table locks acquired immediately: $mycalc{'pct_table_locks_immediate'}%\n"; |
|---|
| | 497 | push(@generalrec,"Optimize queries and/or use InnoDB to reduce lock wait"); |
|---|
| | 498 | } else { |
|---|
| | 499 | goodprint "Table locks acquired immediately: $mycalc{'pct_table_locks_immediate'}%\n"; |
|---|
| | 500 | } |
|---|
| | 501 | } |
|---|
| | 502 | |
|---|
| | 503 | # Performance options |
|---|
| | 504 | if ($mysqlvermajor == 3 || ($mysqlvermajor == 4 && $mysqlverminor == 0)) { |
|---|
| | 505 | push(@generalrec,"Upgrade to MySQL 4.1+ to use concurrent MyISAM inserts"); |
|---|
| | 506 | } elsif ($myvar{'concurrent_insert'} eq "OFF" || $myvar{'concurrent_insert'} < 1) { |
|---|
| | 507 | if ($mysqlvermajor == 4) { |
|---|
| | 508 | push(@generalrec,"Enable concurrent_insert by setting it to 'ON'"); |
|---|
| | 509 | } else { |
|---|
| | 510 | push(@generalrec,"Enable concurrent_insert by setting it to 1"); |
|---|
| | 511 | } |
|---|
| | 512 | } |
|---|
| | 513 | } |
|---|
| | 514 | |
|---|
| | 515 | sub make_recommendations { |
|---|
| | 516 | print "-------- Recommendations -----------------------------------------------------\n"; |
|---|
| | 517 | if (@generalrec > 0) { |
|---|
| | 518 | print "General recommendations:\n"; |
|---|
| | 519 | foreach (@generalrec) { print " ".$_."\n"; } |
|---|
| | 520 | } |
|---|
| | 521 | if (@incvars > 0) { |
|---|
| | 522 | print "Variables to increase:\n"; |
|---|
| | 523 | if ($mycalc{'pct_physical_memory'} > 85) { |
|---|
| | 524 | print " *** MySQL's maximum memory usage exceeds your installed memory ***\n". |
|---|
| | 525 | " *** Add more RAM before increasing any MySQL buffer variables ***\n"; |
|---|
| | 526 | } |
|---|
| | 527 | foreach (@incvars) { print " ".$_."\n"; } |
|---|
| | 528 | } |
|---|
| | 529 | if (@decvars > 0) { |
|---|
| | 530 | print "Variables to decrease:\n"; |
|---|
| | 531 | foreach (@decvars) { print " ".$_."\n"; } |
|---|
