5

I have a dedicated 16 Cores 12 GB RAM Mysql server which is performing quite badly since I configure it for a 16 GB server and I guess that explains why in the world the site now takes more than 500 seconds to render a single page. Im using memcached for caching the quires and using InnoDB as database engine.

#8 core/16GB config stolen from http://groups.drupal.org/node/28830

back_log                        = 50
max_connect_errors              = 9999999
table_cache                     = 5000
binlog_cache_size               = 1M
max_heap_table_size             = 128M
sort_buffer_size                = 500K
join_buffer_size                = 500K
thread_cache_size               = 100
thread_concurrency              = 16
query_cache_size                = 512M
query_cache_limit               = 8M
query_cache_min_res_unit        = 2K
thread_stack                    = 192K
tmp_table_size                  = 384M
long_query_time                 = 2

# *** INNODB Specific options ***

innodb_additional_mem_pool_size = 80M
innodb_buffer_pool_size         = 12G
innodb_file_io_threads          = 4
innodb_thread_concurrency       = 16
innodb_log_buffer_size          = 20M
innodb_log_file_size            = 800M
innodb_log_files_in_group       = 3
innodb_max_dirty_pages_pct      = 90
innodb_lock_wait_timeout        = 120
innodb_file_per_table           = 1

Any help on optimizing the my.cnf config, or even purposing a new one would be really appreciated.

kornesh
  • 163
  • 1
  • 1
  • 8
  • Have you narrowed it down to MySQL, or are you just guessing at the cause? – ceejayoz Aug 14 '11 at 00:54
  • You mean optimizing the queries? – kornesh Aug 14 '11 at 01:05
  • 5
    No, I mean things like failing disks, a runaway or malware process on the server, etc. Are you certain MySQL is what's being slow? – ceejayoz Aug 14 '11 at 01:07
  • 1
    And, for that matter, have you determined whether there are any bottlenecks, such as excessive swap usage, that would point to *which* aspect of the configuration might be at fault? – womble Aug 14 '11 at 01:10
  • the server is not begin used for anything else but it connects to another web server via a private network, it has 50GB /tmp partitioning mounted. I couldnt find anything suspicious in WHM Processes Manager – kornesh Aug 14 '11 at 01:12
  • @womble umm, I dont even really understand what you're asking..my bad – kornesh Aug 14 '11 at 01:14
  • @kornesh This is where you either settle in for a few weeks' (or months/years) reading, or fork out a bit of cash to someone who knows what they're doing, can fix the problem and teach you a few things along the way. – womble Aug 14 '11 at 01:17
  • I have been reading alots of articles and such, and googling didnt help me greatly except for some minor issues. I got to handle the traffic, so this really a tough learning curve for me. – kornesh Aug 14 '11 at 01:24
  • How big are the database(s)? and how big are the tables in the databases? are all the tables indexed? – pablo Aug 14 '11 at 04:56
  • there is 2 tables with around 1-2 Million rows each, the problem starts when I try to join them..Most of the numbers are indexed, like userid, appid and other values which are used for sorting in WHERE statement of mysql – kornesh Aug 14 '11 at 06:06
  • 1
    Cough, cough. Mines are about 63 Millions rows on a way smaller (non dedicated) machine. And pages render under less than a second. So first I would look at the database design and the real bottleneck. – mailq Aug 14 '11 at 08:45
  • @mailq: absolutely. `EXPLAIN` is your friend – Javier Aug 14 '11 at 11:56
  • What version of MySQL are you using ??? – RolandoMySQLDBA Dec 27 '11 at 22:16

1 Answers1

3

The only way that a config for 16GB on a 12GB box would be an issue is if MySQL is trying to use more memory resources than the system has. Most MySQL servers are configured to use too much memory, but that doesn't cause them to run poorly from day 1. If the box isn't swapping and MySQL getting OOM killed, it's not the fact that it's a config for a 16GB box on a 12GB box.

If the issue is on the MySQL side, you can start by checking the process list with SHOW PROCESSLIST;. If it's taking 500 seconds, you'll definitely see some hanging queries. If the queries being executed are being truncated, you can run SHOW FULL PROCESSLIST;.

I would also suggest turning on slow query logging. Once you identify any problem queries, you can use the EXPLAIN statement to see if your queries are using the indexes you have.

If you isolate the issue to the database, there are still a lot of variables. The first step is finding out what queries are going slowly and go from there. You will probably find that improving the queries will give the biggest improvement over any server-side config changes.

DandyPandy
  • 81
  • 5