Hi, After speaking to my web server's technical support when MySQL fell over, they pointed out to me that MySQL was draining a lot of my CPU power. Here's a clip from the UNIX top command: Mem: 524288k total, 171104k used, 353184k free, 0k buffers Swap: 0k total, 0k used, 0k free, 0k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 5282 mysql 15 0 226m 23m 5360 S 99.9 4.6 6:38.58 mysqld 1 root 18 0 10344 728 608 S 0.0 0.1 0:01.48 init 1332 root 18 0 96552 3736 2852 S 0.0 0.7 0:00.05 sshd 1671 root 15 0 12056 1596 1256 S 0.0 0.3 0:00.01 bash 5164 root 21 0 9832 1336 1108 S 0.0 0.3 0:00.00 mysqld_safe Now, it's not at 90% all the time - a lot of the time it's between 0% and 1%. Can anyone give me any pointers on good things to look into here to reduce the load? Thanks.
Turn on the slow query log and look at it. It's often a single, poorly optimized query that causes load spikes like this. Otherwise can you explain the site's database usage, visitors per day/hour, and how database intensive the site is? Also, what sort of hardware is the server on, and how is the database designed, MyISAM, Innodb, number of tables, etc.
Thanks for the reply. I should probably start by saying I'm by no means a MySQL expert, so apologies if I get terminology wrong in places. I've enabled the slow query log in my.cnf and restarted MySQL, but it hasn't generated anything in the logfile yet. I'm running MySQL 5.0.77 with results retrieved from a PHP/XHTML front-end under Linux 2.6. CPU is Intel(R) Xeon(R)CPU E5420 @ 2.50GHz on a VPS. My share of the VPS only runs this domain. There are 35 tables, ranging in size from 2KB to 22MB. Total database size is 34.3MB. After optimization, there is no overhead displayed in DBWebAdmin. The largest table by number of rows contains 142,000 records. One table (the 22MB one) contains a text field (as opposed to varchar) due to the length of data that's in it. In this table, row size ø is 486 bytes. All tables are MyISAM. Each has a primary key upon which inter-table relationships are based. Almost all pages on the site are used to retrieve information from the database. 2,500 unique visitors per month, averaging 10 page views each. The largest page size I've managed to generate is approx 623KB and takes about 50 seconds on the 2MBit ADSL connection I'm using at the moment. (This is an extreme example, including a display of 1,004 rows from the large 22MB table. Default query size is just 50 rows.) Hope this helps. Thanks.
512Mb dedicated RAM. Re: the slow query log, I didn't realise until just now that I had to manually create the slow query log file and chown it to the mysql user and group. Now I've done that, I expect some queries will be added to it, as some have shown up in the runtime information since then. I should add that I found a couple of missing indexes. With them now added, this may improve things also. I'll let this run for a bit and drop you an update when the results are clearer. Cheers.
This is the way to go. Also examine the slow queries for indexes. Also consider adjusting key_buffer_size in my.cnf (MySQL configuration file).
Hi, This is all I am getting in the slow queries log: /usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with: Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock Time Id Command Argument /usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with: Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock Time Id Command Argument I would have expected more, but unfortunately I don't have a frame of reference! I've tried switching on log-queries-not-using-indexes as below. Should I be specifying a location, or does it write it to the same place as the slowqueries.log? Here's the contents of my.cnf currently: [mysqld] set-variable=local-infile=0 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql log-slow-queries=/etc/slowqueries.log log-queries-not-using-indexes=/etc/notindexed.log # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 skip-bdb set-variable = innodb_buffer_pool_size=2M set-variable = innodb_additional_mem_pool_size=500K set-variable = innodb_log_buffer_size=500K set-variable = innodb_thread_concurrency=2 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid skip-bdb set-variable = innodb_buffer_pool_size=2M set-variable = innodb_additional_mem_pool_size=500K set-variable = innodb_log_buffer_size=500K set-variable = innodb_thread_concurrency=2 Thanks.
Is this a php site? If so you should definitely use a front end caching system. APC is one that works well. There's a number of others like memcached as well.
Such high cpu usage doesn't indicate an IO problem, so tweaking memory won't really help. Some queries are just very CPU intensive. Try posting a few that you figure are most troublesome and we can have a look. jestep has a great suggestion, caching can be extremely helpful. Aside from those he mentioned you can simply add a few lines of code to the head and foot of your php pages to do a local file based cache. The only thing to work out is how often the cache should be cleared. I use a modified version of this code on some very high demand sites. I have a setup where if any of the information used to display the site is changed it changes a variable in another very small (fast) DB. At the top of this code I check for that variable and update the cache if found. A trick like that for you can be very helpful. $cachefile = 'cache/index-cached.html'; $cachetime = 5 * 60; // Serve from the cache if it is younger than $cachetime if (file_exists($cachefile) && time() - $cachetime < filemtime($cachefile)) { include($cachefile); echo "<!-- Cached copy, generated ".date('H:i', filemtime($cachefile))." -->\n"; exit; } ob_start(); // Start the output buffer /* The code to dynamically generate the page goes here */ // Cache the output to a file $fp = fopen($cachefile, 'w'); fwrite($fp, ob_get_contents()); fclose($fp); ob_end_flush(); // Send the output to the browser PHP: You'll need to change the cache file location. One trick I did to make it universal is name the cache file the full location of the page. EG: $pageURL = $_SERVER["SERVER_NAME"].$_SERVER["REQUEST_URI"].$_SERVER['QUERY_STRING']; $pageURL = preg_replace(“/[^a-zA-Z0-9\s]/â€, “â€, $pageURL); $pagefile.=".html"; Use that, along with the folder location, for your cache file name. It should change: http://www.mysite.com/page.php?cars to httpwwwmysitecompagephpcars.html, ready for saving and serving.