MySQL Performance Tuning

[knight-of-ni]

This is for those who would be interested to know about some simple changes one can make to increase the performance of your MySQL server.

While I did not take the time to produce any quantitative data for you, adding the following lines to my /etc/my.cnf seemed to make my system run better overall:

max_allowed_packet=32M
key_buffer=32M
table_cache=512
query_cache_size=128M
sort_buffer_size=8M
read_buffer_size=1M
innodb_buffer_pool_size=64M
innodb_additional_mem_pool_size=16M
innodb_log_buffer_size=64M
innodb_flush_log_at_trx_commit=2
thread_cache_size=16
skip-bdb
tmpdir=/dev/shm

I recommend you at least skim through the following article to get a basic idea of what these settings do before you make any changes:

http://www.mysqlperformanceblog.com/200 ... tallation/

Pay special attention when changing "innodb_flush_log_at_trx_commit" as there is a greater chance of data loss if you do not have a UPS.

Note that my goal here was focused on getting three MySQL databases running faster, not just Zonemidner. For example, increasing the query cache probably won't have much impact on the recording side of Zoneminder, but in my case I did seem to notice an improvement on the other two databases... I guess what I'm saying is your mileage may vary depending on your machine's hardware and what you are running on it.

Oh, you can ignore max_allowed_packet if you are only running Zoneminder. That was to fix a problem with my Drupal database.

** added 11/19/09 **
I added the line tmpdir=/dev/shm above after discovering its usefulness. As you may already know, /dev/shm is a ram disk that exists on most modern Linux distributions. Verify it exists before you add that line to yoru config.

[knight-of-ni]

Today I discovered that Zoenminder uses "myisam" tables instead of "innodb" tables. Consequently, the innodb parameters previously mentioned do not apply. For a machine with Zoneminder as the only only application that uses mysql, your my.cnf file might look something like this:

max_allowed_packet=32M
key_buffer=32M
table_cache=512
query_cache_size=128M
sort_buffer_size=8M
read_buffer_size=1M
thread_cache_size=16
myisam_sort_buffer_size=64M
skip-innodb
skip-bdb
tmpdir=/dev/shm

Notice that, not only did I omit the innodb parameters, but I turned off support for this type of table with "skip-innodb". Obviously, you should not do this if you are running other applications on your machine that requires this type of mysql table.

[knight-of-ni]

Here is a php script that, when run, will check all the tables in all your databases for fragmentation. Upon finding a table with fragmentation, it will use the mysql OPTIMIZE TABLE command to make the fragmentation go away. I've got this script scheduled to run on my system once a week.

Code: Select all

<?php

set_time_limit( 100 );

$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$start = $time;

//Connection variables :
$h = 'localhost';
$u = 'root';
$p = '{enter your password}';

$dummy_db = 'mysql';
$db_link = mysql_connect($h,$u,$p);

$res = mysql_db_query($dummy_db, 'SHOW DATABASES', $db_link) or die('Could not connect: ' . mysql_error());
echo 'Found '. mysql_num_rows( $res ) . ' databases.' . "\n";
$dbs = array();
while ( $rec = mysql_fetch_array($res) )
{
$dbs [] = $rec [0];
}

foreach ( $dbs as $db_name )
{
$res = mysql_db_query($dummy_db, "SHOW TABLE STATUS FROM `" . $db_name . "`", $db_link) or die('Query : ' . mysql_error());
$to_optimize = array();
while ( $rec = mysql_fetch_array($res) )
{
if ( $rec['Data_free'] > 0 )
{
$to_optimize [] = $rec['Name'];
echo $rec['Name'] . ' need to be optimized' . "\n";
}
}
if ( count ( $to_optimize ) > 0 )
{
foreach ( $to_optimize as $tbl )
{
mysql_db_query($db_name, "OPTIMIZE TABLE `" . $tbl ."`", $db_link );
}
}
}

$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 6);
echo 'Optimization completed.'."\n". 'Total run time: ' . $total_time . ' seconds.' . "\n";

?>

The original script can be found here:
http://www.softwareprojects.com/resourc ... -1424.html

[kingofkya]

i dont know if you has similar probs but does this fix the seemingly random corrupted events

[knight-of-ni]

Technically no, it didn't, but it's interesting you mention that because something along the lines of corrupted events is the reason why I started looking under the hood so the speak in first place. While the SQL mods previously posted didn't fix the random corrupted event issue I was trying to solve, it did improve playback performance... in the sense that playback is noticeably smoother and I can now playback many clips back-to-back w/o the playback freezing. For me anyway, getting as much mysql info cached into ram was the key.

But back to your comment about the random corrupted events, the following provides more detail into what I was looking to (and ultimately did) solve...

After compiling and installing the latest zm, I soon noticed that my two network cameras would randomly go offline. When the cameras went offline an event in the database would be generated with a date from 1969 and a duration of some God awful length of time. This would cause my event time-line to turn solid red. Some days this would occur once an hour, sometimes I could go all day w/o this happening. Even though this appeared to be a network problem, I knew it was not because I could sit and ping my cameras all day long w/o dropping a single packet. My resolution and frame rate were reasonable as well.

This got me looking into a lot of things. Ultimately, what resolved the issue for me was to simply increase the HTTP timeout under Options from 2500 milliseconds to 5000 milliseconds (I also increased the AJAX timeouts under each user to 15000 just to be safe). I have not experienced a single corrupted event since. It seems I was making the problem out to be harder than it was. I learned a lot in the process though.

Sorry for the long post. I've provided all this detail so you/others can determine whether or not your symptoms are indicative of the same problem. Seems like a lot of people have symptoms similar to this, but I am not convinced the root cause is the same for everyone.

Let me know if this is beneficial in some way.... maybe I ought to post this info into its own thread.

[kingofkya]

interesting yeah i notice that it reduced corruption it was still there just not as bad also thanks for the tip it seams to help even more

also i just made a filter to remove any event before 1990

its not the greatest fix but it works

[nirv199]

Today I discovered that Zoenminder uses "myisam" tables instead of "innodb" tables. Consequently, the innodb parameters previously mentioned do not apply. For a machine with Zoneminder as the only only application that uses mysql, your my.cnf file might look something like this:

max_allowed_packet=32M
key_buffer=32M
table_cache=512
query_cache_size=128M
sort_buffer_size=8M
read_buffer_size=1M
thread_cache_size=16
myisam_sort_buffer_size=64M
skip-innodb
skip-bdb
tmpdir=/dev/shm

Notice that, not only did I omit the innodb parameters, but I turned off support for this type of table with "skip-innodb". Obviously, you should not do this if you are running other applications on your machine that requires this type of mysql table.

Is this my.cnf above still accurate and relevant for zoneminder 1.36?

[knight-of-ni]

No, this thread from 2009 is ancient. We replaced all the myisam tables with innodb years ago.

Simplest thing to do, to tune your database, is to run mysqltuner after your dB has been running at least 24 hours. Just do what mysqltuner suggests.

[iconnor]

zmupdate.pl still contains code to automatically upgrade them to innodb...