Optimizing MySQL on FreeBSD part 1

I have written a few other times at a few separate locations about tuning MySQL in the past, so I'm going to attempt and write a bit of updated material and keep it all in one place, this blog. I will be following up in the next few months concerning additional tuning steps that can be taken.

Recently while browsing the interweb, I came across a nifty little perl script written by Major Hayden of rackspace.com.

I put a copy of this perl script here for ease of downloading and use. To get it, simply download -> extract it -> make executable. Of course you need perl installed to use it...

Some examples of output that I received when I ran the script ./mysqltuner.pl on one of my higher transaction test servers:

General recommendations:
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
Variables to increase:
*** MySQL's maximum memory usage exceeds your installed memory ***
*** Add more RAM before increasing any MySQL buffer variables ***
max_connections (> 125)
key_buffer_size (> 11.1G)
query_cache_size (> 256M)
join_buffer_size (> 1024.0M, or always use indexes with joins)
Variables to decrease:
wait_timeout (<>

I modified most of the variables in question in my /etc/my.cnf and restarted mysqld and let it run for a few days. I then ran the script again and got the following output:

./mysqltuner.pl
MySQL High-Performance Tuner - Major Hayden
Bug reports, feature requests, and downloads at mysqltuner.com
Run with '--help' for additional options and output filtering
Please enter your MySQL login: root
Please enter your MySQL password:
[OK] Currently running supported MySQL version 5.0.41-log
-------- General Statistics --------------------------------------------------
[--] Up for: 6d 5h 5m 20s (8M q [16.393 qps], 139K conn, TX: 2G, RX: 4G)
[--] Reads / Writes: 65% / 35%
[!!] Maximum possible memory usage: 442.7G (1341% of installed RAM)
[OK] Slow queries: 0%
[OK] Highest usage of available connections: 49%
[OK] Key buffer size / total MyISAM indexes: 12.0G/11.1G
[OK] Key buffer hit rate: 99.8%
[OK] Query cache efficiency: 31.5%
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0%
[!!] Joins performed without indexes: 2838670
[OK] Temporary tables created on disk: 0%
[OK] Thread cache hit rate: 99%
[OK] Table cache hit rate: 78%
[OK] Open file limit used: 13%
[OK] Table locks acquired immediately: 99%
-------- Recommendations -----------------------------------------------------
General recommendations:
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Variables to increase:
*** MySQL's maximum memory usage exceeds your installed memory ***
*** Add more RAM before increasing any MySQL buffer variables ***
join_buffer_size (> 1.5G, or always use indexes with joins)

All in all, this is a highly useful script to get some quick stats and easy adjustment variables to help tune your MySQL server. I should also note that this is not specific to FreeBSD, but I happen to be a FreeBSD junkie and this this was all tested on a FreeBSD 6.2 Rel box.

Cheers,
JJC

Canonical releases Ubuntu 7.10

Canonical Ltd. released the latest version (7.10) of the Ubuntu Server, Desktop, Kubuntu and Edubuntu Editions today. You can get more information about these releases and download them at the official Ubuntu site.

The Ubuntu developers have also created an upgrade path for users that are currently on the 7.04 ("Feisty Fawn") release. As stated on their website, the migration is as simple as insuring that all updates have been applied to your Feisty Fawn installation then opening System -> Administration -> Update Manager -> Select Upgrade (you may need to check for new updates). At this point you simply follow the on-screen instructions.

I will be testing this process tonight on my HP laptop and posting my results when complete.

Cheers,
JJC

HeX Live 1.0 Release

After 6 months of heavy development and debugging I am pleased to announce the release of the HeX Live CD 1.0 Release. What is HeX Live? HeX Live is the worlds first and foremost Network Security Monitoring & Network Based Forensics liveCD. The intent is to provide a wide array of highly usable tools in a pre-packaged format that the analyst can use to investigate and monitor real-time network activity, whether security related or in the course of reviewing traffic to determine bandwidth over utilization sources and so on...

This will be the final major release of HeX LiveCD until the release of FreeBSD 7.0 Rel, this is of course pending no major bugs are located in HeX 1.0R. If there are any major bugs found, then a bug-fixed HeX will be released prior to FreeBSD 7.0 Rel.\\

For a detailed list of what applications can be found on HeX Live 1.0R check out the actual project at rawpacket.org.

I have also included in this posting the CD covers that were created by vickz, fantastic work man! You can download the HeX LiveCD 1.0R from the following locations:

1. US Server (East Coast) | MD5 | SHA256 | User Guide
   
2. Malaysia Server | MD5 | SHA256 | User Guide
   

I will try to get some decent screenshots posted soon so that everyone can see just how slick the HeX LiveCD 1.0R really is. I would also suggest that you download it and play with it. There are a good number of tools on here for packet monkeys of all ages and skill to have a good old time!

I'll leave it at that for now, and again would like to thank the community for their support and feedback throughout the development process of this tool.

Shout to Geek00l for organizing everything and kicking some a$$!
Shout to ch4flgs_ and zarul for everything!
Shout to all others involved in this project (esp for putting up with me)

Cheers,
JJC

Loose lips sink ships!

During recent interweb browsing and reading I came across the following and have to comment, it's been in the news lately but this just brought it up again for me; http://www.nysun.com/article/64163.

WASHINGTON — Al Qaeda's Internet communications system has suddenly gone dark to American intelligence after the leak of Osama bin Laden's September 11 speech inadvertently disclosed the fact that we had penetrated the enemy's system.

The intelligence blunder started with what appeared at the time as an American intelligence victory, namely that the federal government had intercepted, a full four days before it was to be aired, a video of Osama bin Laden's first appearance in three years in a video address marking the sixth anniversary of the attacks of September 11, 2001. On the morning of September 7, the Web site of ABC News posted excerpts from the speech.

But the disclosure from ABC and later other news organizations tipped off Qaeda's internal security division that the organization's Internet communications system, known among American intelligence analysts as Obelisk, was compromised. This network of Web sites serves not only as the distribution system for the videos produced by Al Qaeda's production company, As-Sahab, but also as the equivalent of a corporate intranet, dealing with such mundane matters as expense reporting and clerical memos to mid- and lower-level Qaeda operatives throughout the world.

Has the media lost all of their capability to make good discretionary decisions? Further, typically they have subject-matter experts, one would think that such experts would know better. But I suppose that it is all about the ratings and making that next buck!

While intranets are usually based on servers in a discrete physical location, Obelisk is a series of sites all over the Web, often with fake names, in some cases sites that are not even known by their proprietors to have been hacked by Al Qaeda.

Similar to a botnet etc... effectively a chain of pwned servers. This is certainly not a new concept and usage of such a concept in conjunction with services such as ToR (The Onion Router) would make tracking Obelisk users virtually impossible.

One intelligence officer who requested anonymity said in an interview last week that the intelligence community watched in real time the shutdown of the Obelisk system. America's Obelisk watchers even saw the order to shut down the system delivered from Qaeda's internal security to a team of technical workers in Malaysia. That was the last internal message America's intelligence community saw. "We saw the whole thing shut down because of this leak," the official said. "We lost an important keyhole into the enemy."

We most certainly did lose an important keyhole, ya think? If a keyhole is what you would call it. The intel received from such a source could easily help thwart future planned terrorist and military actions etc...

By Friday evening, one of the key sets of sites in the Obelisk network, the Ekhlaas forum, was back on line. The Ekhlaas forum is a password-protected message board used by Qaeda for recruitment, propaganda dissemination, and as one of the entrance ways into Obelisk for those operatives whose user names are granted permission. Many of the other Obelisk sites are now offline and presumably moved to new secret locations on the World Wide Web.

The founder of a Web site known as clandestineradio.com, Nick Grace, tracked the shutdown of Qaeda's Obelisk system in real time. "It was both unprecedented and chilling from the perspective of a Web techie. The discipline and coordination to take the entire system down involving multiple Web servers, hundreds of user names and passwords, is an astounding feat, especially that it was done within minutes," Mr. Grace said yesterday.

I agree with Mr. Grace, to an extent, it would be a feat indeed if individual personnel were involved. I think that it's also plausible to think that this network operated much like a botnet. From that perspective there could have been a simple command or series of commands that initiated the automatic shutdown or action to be taken in the event of a security breach.

The head of the SITE Intelligence Group, an organization that monitors Jihadi Web sites and provides information to subscribers, Rita Katz, said she personally provided the video on September 7 to the deputy director of the National Counterterrorism Center, Michael Leiter.

Ms. Katz yesterday said, "We shared a copy of the transcript and the video with the U.S. government, to Michael Leiter, with the request specifically that it was important to keep the subject secret. Then the video was leaked out. An investigation into who downloaded the video from our server indicated that several computers with IP addresses were registered to government agencies."

Yesterday a spokesman for the National Counterterrorism Center, Carl Kropf, denied the accusation that it was responsible for the leak. "That's just absolutely wrong. The allegation and the accusation that we did that is unfounded," he said. The spokesman for the director of national intelligence, Ross Feinstein, yesterday also denied the leak allegation. "The intelligence community and the ODNI senior leadership did not leak this video to the media," he said.

Ms. Katz said, "The government leak damaged our investigation into Al Qaeda's network. Techniques and sources that took years to develop became ineffective. As a result of the leak Al Qaeda changed their methods." Ms. Katz said she also lost potential revenue.

A former counterterrorism official, Roger Cressey, said, "If any of this was leaked for any reasons, especially political, that is just unconscionable." Mr. Cressey added that the work that was lost by burrowing into Qaeda's Internet system was far more valuable than any benefit that was gained by short-circuiting Osama bin Laden's video to the public.

I personally think that it's more than unconscionable, I dare say it's borderline treason!

While Al Qaeda still uses human couriers to move its most important messages between senior leaders and what is known as a Hawala network of lenders throughout the world to move interest-free money, more and more of the organization's communication happens in cyber space.

"While the traditional courier based networks can offer security and anonymity, the same can be had on the Internet. It is clear in recent years if you look at their information operations and explosion of Al Qaeda related Web sites and Web activities, the Internet has taken a primary role in their communications both externally and internally," Mr. Grace said.

Cheers,
JJC

HeX Live Pending Release

For all of you anxious packet monkeys out there, the HeX LiveCD 1.0R will soon be available. We are running through extensive tests and bug fixing excersizes right now, but anticipate releasing this new version within the next week. I'll post an update once released, as well as the standard US mirrors.

This project has also been gaining a good amount of momentum and continued community support. I would like to thank all involved, esp geek00l and chfl4gs_ (the core founders)!

If you want some additional information concerning this project, please check out www.rawpacket.org!

Cheers,
JJC

InProtect Wiki and Update

The project continues to gain speed and support from the community (thanks again everyone!). The core team is currently meeting every other Sunday, in the secret InProtect cave, to hash out the roadmap and future plans. Unfortunately I was not in town for the most recent meeting and away from the interweb and therefore did not make the meeting.

However I still have some updates that I can post;
The InProtect Wiki is now online and we will be working hard to keep it updated with the latest goodies, FAQ, etc...! http://inprotect.wiki.sourceforge.net, please check it out and let us know what we can do to improve it or what you would like to see added.

I continue to get visitors to #inprotect on irc.freenode.net and appreciate all of the continued feedback.

We anticipate having the CVS -to- SVN conversion done shortly and subsequently publishing an Alpha release of the new version. We will also be updating the InProtect home page with meeting notes, roadmap and so on, in the near future!

Cheers,
JJC

FIXED::[Bug 1641] NessusClient 3.0.0 Beta 4 Crash on Server Connect

I must say that I am quite pleased with Renaud Deraison of nessus.org for his rapid response and remediation of the bug that I discovered last week (NessusClient 3.0.0 Beta 4 Bug). There was an uninitialized pointer when a class was created from an XML file (rather than dynamically), which in turn created a bad memory access and therefore crashed the client.

Nessus.org has posted a fixed version, Beta 5 of the 3.0.0 NessusClient at their typical download location: http://www.nessus.org/download/.

I would also like to add to my previous posting about the feature set of the NessusClient and it's inability to export to XML (this is still true) but can be worked around (too a degree anyway). When you scan a host and if you chose to save the session, upon exiting the NessusClient, it creates a .nessus file which is pure XML (albeit it's a different XML format than the CLI xml), and which contains much more information about the scan than the other formats (it contains all the scan results, the policies, the targets associated to each scan, etc...

Thx again Renaud!

Cheers,
JJC