-
mysqlslap howto
I noticed that people were hitting the site for information on how to run mysqlslap.To help out those searchers, here is a quick mysqlslap howtoMake sure you have mysql 5.1.4 or higher. Download MySQL from the MySQL websiteMake sure your MySQL database is running.Run mysqlslap, using progressively more concurrent threads: mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \-
-
Storing MySQL Binary logs on NFS Volume
There is a lot of discussions whenever running MySQL storing data on NFS is a good idea. There is a lot of things for and against this and this post is not about them.
The fact is number of people run their databases on NetApp and other forms of NFS storage and this post is about one of discoveries in such setup.
There are good reasons to have binary logs on NFS volume - binary logs is exactly the thing you want to survive the server crash - using them you can do point in time recovery from backup.
I was testing high volume replication today using Sysbench:
PLAIN TEXT
SQL:
sysbench --test=oltp --oltp-table-size=10000000 --db-driver=mysql --mysql-user=root --mysql-db=sbsmall --init-rng=1 --max-requests=100000000 --max-time=600 --oltp-test-mode=nontrx --oltp-nontrx-mode=update_nokey --num-threads=8 run
On this box I got around 12.000 of updates/sec which is not the perfect number, though it mainly was because of contention issues in MySQL 5.0 rather than any NAS issues.
This number was reachable even with binary log stored on NFS volume. This number is for sync_binlog=0 and innodb_flush_log_at_trx_commit=2
I noted however if I enable replication - connect the slave to this box the throughput on the Master drops to about 2800 updates sec.... which is very close to the magic number how many network roundtrips per second I can get over 1Gb link. It was even more interesting when that. If I would pause replication for prolonged period of time and let few GB of binary logs to accumulate the performance on Master will be high even with replication running, but it will slow down as soon as IO thread on the slave is caught up with master.
When I moved the Binary logs to the local storage I got very similar performance but there have been no degradation when replication is enabled.
I have not checked in details why this could be the case but I guess there is something which requires a network roundtrip when the binary log is written at the same time as slave-feeding thread is reading it.
I'd be curious to know if someone else can observe such behavior and if there is an NFS tuning which can be done to avoid it or if we need to fix MySQL
Entry posted by peter |
No comment
Add to: | | | |
-
Alternative PHP Cache (APC) and Drupal Performance
The Alternative PHP Cache (APC) is a free and open opcode cache for PHP. Its goal is to provide a free, open, and robust framework for caching and optimizing PHP intermediate code. So what does this mean for Drupal?
An opcode cache is used to keep compiled PHP code in memory. In a nutshell, when there's a request for a Drupal node, there are all kinds of PHP code that need to be compiled before PHP can execute that code to query the database, create the webpage, and send it to the browser. The more complex your Drupal site, IE the more modules you have installed, the more PHP code that needs to be compiled. This compilation process occurs on every request to your Drupal site. By installing and enabling an opcode cache, your server will compile all the PHP code once, then store it in memory for later use. When a request happens again for the same PHP code, the opcode cache is checked to see if that PHP code is already cached and if it is, the compilation process is skipped and the PHP code is executed straight away. Normally, this will improve the performance of your Drupal site, but there is a condition you should be aware of that can actually slow down your site. It's called memory fragmentation and this condition is worse than having no opcode cache at all.
Symptoms of APC Memory Fragmentation
The symptoms of APC memory fragmentation are somewhat subtle. Over time your site starts slowing to a crawl, but you've not changed anything configuration wise with your site. There's nothing in any of the logs to indicate any problems, but restarting your webserver brings the performance back. Eventually though your site starts running slow again. APC uses shared memory for it's cache. The Ubuntu server systems I use have a default shared memory size of 32MB. You'll need to copy to your site the file apc.php, which can be found with the source to APC itself. This file needs the same owner/group as your Drupal files.
APC Fragmented Memory
APC memory is fragmented when the output of apc.php looks like this:
At the bottom of this section of the apc.php page you'll see Fragmentation: 100.00%. Not good. What's happening with APC is that it's running out of shared memory and having to discard cached PHP code before it can insert newly compiled PHP code. Keeping track of all these memory segments, their sizes, and figuring out which compiled PHP code can fit into an available memory segment is what's slowing down your Drupal site.
Configuring APC Shared Memory
In order to give APC more shared memory, first determine the existing size of your shared memory segment:
sysctl -a | grep shmmax
kernel.shmmax = 33554432
This server has 32MB for it's shared memory. It's possible your server may have more shared memory available than what APC is configured to use. To configure your server to use more, use the following command:
sysctl -w kernel.shmmax=50331648
This configures APC shared memory for 48MB. The number above is in bytes, so to convert 48MB to bytes, just multiply 48 by 1,048,576 so 48x1,048,576=50331648 bytes.
You'll want to make this a permanent setting so it'll hold after a reboot. You'll need to add the following line to /etc/sysctl.conf file:
kernel.shmmax=50331648
You'll also need to modify your APC settings as well. I keep my APC settings in it's own configuration file - apc.conf:
apc.shm_segments="1"
apc.shm_size="48"
apc.shm_size is already in MB.
When you've made all the changes to APC, you'll need to restart your webserver.
Correct APC Memory
A Drupal site with a correctly configured APC shared memory size will look like this:
Notice Fragmentation: 0%, and there's a few MB of shared memory still available. You can view the APC configuration and performance of this blog at http://blog.thetajoin.com/apc.php
Hope you'll find this post helpful. If you have any questions, please comment!
-- Mark
-
Alternative PHP Cache (APC) and Drupal Performance
The Alternative PHP Cache (APC) is a free and open opcode cache for PHP. Its goal is to provide a free, open, and robust framework for caching and optimizing PHP intermediate code. So what does this mean for Drupal?
An opcode cache is used to keep compiled PHP code in memory. In a nutshell, when there's a request for a Drupal node, there are all kinds of PHP code that need to be compiled before PHP can execute that code to query the database, create the webpage, and send it to the browser. The more complex your Drupal site, IE the more modules you have installed, the more PHP code that needs to be compiled. This compilation process occurs on every request to your Drupal site. By installing and enabling an opcode cache, your server will compile all the PHP code once, then store it in memory for later use. When a request happens again for the same PHP code, the opcode cache is checked to see if that PHP code is already cached and if it is, the compilation process is skipped and the PHP code is executed straight away. Normally, this will improve the performance of your Drupal site, but there is a condition you should be aware of that can actually slow down your site. It's called memory fragmentation and this condition is worse than having no opcode cache at all.
Symptoms of APC Memory Fragmentation
The symptoms of APC memory fragmentation are somewhat subtle. Over time your site starts slowing to a crawl, but you've not changed anything configuration wise with your site. There's nothing in any of the logs to indicate any problems, but restarting your webserver brings the performance back. Eventually though your site starts running slow again. APC uses shared memory for it's cache. The Ubuntu server systems I use have a default shared memory size of 32MB. You'll need to copy to your site the file apc.php, which can be found with the source to APC itself. This file needs the same owner/group as your Drupal files.
APC Fragmented Memory
APC memory is fragmented when the output of apc.php looks like this:
At the bottom of this section of the apc.php page you'll see Fragmentation: 100.00%. Not good. What's happening with APC is that it's running out of shared memory and having to discard cached PHP code before it can insert newly compiled PHP code. Keeping track of all these memory segments, their sizes, and figuring out which compiled PHP code can fit into an available memory segment is what's slowing down your Drupal site.
Configuring APC Shared Memory
In order to give APC more shared memory, first determine the existing size of your shared memory segment:
sysctl -a | grep shmmax
kernel.shmmax = 33554432
This server has 32MB for it's shared memory. It's possible your server may have more shared memory available than what APC is configured to use. To configure your server to use more, use the following command:
sysctl -w kernel.shmmax=50331648
This configures APC shared memory for 48MB. The number above is in bytes, so to convert 48MB to bytes, just multiply 48 by 1,048,576 so 48x1,048,576=50331648 bytes.
You'll want to make this a permanent setting so it'll hold after a reboot. You'll need to add the following line to /etc/sysctl.conf file:
kernel.shmmax=50331648
You'll also need to modify your APC settings as well. I keep my APC settings in it's own configuration file - apc.conf:
apc.shm_segments="1"
apc.shm_size="48"
apc.shm_size is already in MB.
When you've made all the changes to APC, you'll need to restart your webserver.
Correct APC Memory
A Drupal site with a correctly configured APC shared memory size will look like this:
Notice Fragmentation: 0%, and there's a few MB of shared memory still available. You can view the APC configuration and performance of this blog at http://blog.thetajoin.com/apc.php
Hope you'll find this post helpful. If you have any questions, please comment!
-- Mark
-
Simple Backup Server
I have not written an article in a while, I partially blame it on the World Cup and my day job. The time has come to share some of my recent experiences with a neat project to provide several teams internally with current MySQL backups.When faced with these types of challenges is my first step is to look into OSS packages and how can they be combined into an actual solution. It helps me understand the underlying technologies and challenges.ZRM BackupI have reviewed Zmanda's Recovery Manager for MySQL Community Edition in the Fall 2008 issue of MySQL magazine. It remains one of my favorite backup tools for MySQL since it greatly simplifies the task and configuration of MySQL backups taking care of most of the details. Its flexible reporting capabilities came in handy for this project as I'll explain later. Some of the key settings:We included the hostname in the ZRM backup-set to make it easier to locate. Linux shell example:export BKUP_SET=`hostname -s`-logicalFollowing ZRM conventions, generate a HTML report in the main backup directory.mysql-zrm-reporter --where backup-set=$BKUP_SET --type html \ --show backup-status-info >/dir/to/backup/$BKUP_SET/report.htmlThe actual backup files live under the subdirectories:/dir/to/backup/$BKUP_SET/<timestamp>/where /dir/to/backup could be mounted on a NFS serverPlease check the ZRM for MySQL documentation for details on its configuration and operation. Use the report format that best suits your needs, ZRM provides plenty of options and if none fits your needs exactly, you can still generate your own.lighttpd HTTP ServerAs a web server, lighty adds very little overhead so it can run on the same MySQL server and the backups can be served directly from it. If this is not acceptable and the backups are stored in an NFS volume, lighty can be installed on the NFS server, the configuration will remain very similar to the one I describe here. For this example I’ll assume that the MySQL server host is named dbhost, in which case the /etc/lighttpd/lighttpd.conf file should include:server.modules = ( "mod_alias", "mod_access", "mod_accesslog" )## Default root directory set to the main backup setserver.document-root = "/dir/to/backup/dbhost-logical/"## Set one alias per backup typealias.url = ( "/logical/" => "/dir/to/backup/dbhost-logical/")alias.url += ( "/incremental/" => "/dir/to/backup/dbhost-incremental/")## Enable logs for diagnosisserver.errorlog = "/var/log/lighttpd/error.log"accesslog.filename = "/var/log/lighttpd/access.log"server.port = 8088## virtual directory listings enableddir-listing.activate = "enable"## enable debugging to facilitate diagnosisdebug.log-request-header = "enable"debug.log-response-header = "enable"debug.log-request-handling = "enable"debug.log-file-not-found = "enable"The server.document-root and alias.url settings should match the main directories for the ZRM backup sets.Make sure that the user and / or group defined for the lighttpd process have proper permissions to access the backup set directory tree. The backups will be available as a directory listing when using the following URLs: http://dbhost:8088/logical/ or http://dbhost:8088/incremental/. Clicking on the report.html file in those directories (see the previous section in the article), the users have access to the backup reports and verify if any of them had errors. The files are also accessible using wget.If you need to enable tighter security, lighty supports https and LDAP authentication the details are in its documentation and it takes less than 10 minutes to setup.monit MonitoringWhen you need a service to be running 24/7, monit is a great tool in the Linux arsenal to achieve it. It monitors a number of system and services variables and it will start, stop and/or restart any service under a number of conditions. For this POC, the goal is to keep lighty running, restarting it after an eventual crash. We are using the following configuration:check process lighttpd with pidfile "/var/run/lighttpd.pid" alert some@yourorg.com NOT { instance, pid, ppid, action } start program = "/etc/init.d/lighttpd start" with timeout 60 seconds stop program = "/etc/init.d/lighttpd stop" with timeout 30 seconds if 2 restarts within 3 cycles then timeout if failed port 8088 protocol http with timeout 15 seconds within 3 cycles then restartIf the process crashes or port 8088 becomes unresponsive, monit will (re)start lighty automatically.Conclusion (sort of)Implement all these tools in a new server takes less than 1 hour. During the the first couple of implementations I learned a lot about how the packages interacted, security issues and users’ needs and expectations. As the users started looking into the solution, they also came up with newer use cases.While we look for a more suitable solution, these free (as in freedom and beer) packages provided us with the opportunity to learn while still achieving our goals and delivering results to our users. Now we know what to look for if we decide to evaluate an open core or commercial solution.
|
