Mysqld.exe Process Taking Too Much RAM in WAMP?

If you find that mysqld.exe in Windows with WAMPSERVER is using a lot of RAM, this may help.

I have WAMPSERVER 2.4 installed with MySQL 5.6.12 as you can see here:

wamp-mysql

 

The mysqld.exe process is taking almost 500MB of RAM!

mysql-ram

 

This has to do with a high default on the table_definition_cache variable of MySQL. Note that this variable may not have an entry in your my.cnf or my.ini file. Mine did not have an entry for this variable. However, when I checked the active variables using Navicat, it shows a value of 1400 as you can see here:

table-cache

 

According to dev.mysql, the default value of MySQL 5.6 is 400. You can see here that it is actually a calculated value: http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_definition_cache

Notice that it says the value is based on a formula:

400 + (table_open_cache / 2)

So my table_open_cache is also not set in my.ini. My value happened to be at 2000. That answers that question. So why is table_open_cache so high? According to mysql, for MySQL version 5.6.12 or greater the default is 2000. There is the answer! I just happen to have installed MySQL 5.6.12 which set a table_open_cache of 2000 which, when using the calculation above set the table_definition_cache to 1400, which then used up about 500MB of RAM.

For my fix, I just added table_definition_cache = 400 to my.ini and restarted MySQL. Now it takes about 95MB of RAM.

My use of WAMP is fairly light, however, if you use yours heavily or keep a ton of data, you might fine tune these values to be more appropriate to your needs. Now I have a small table_definition_cache but still have a high table_open_cache. This may or may not cause issues in the future, but for now it’s just fine for a single user like me.

Hope that helps!

Repairing a Corrupt MYISAM Table with MySQL

It’s just one of those days! I began receiving emails like this:

Database error in vBulletin myversion:

Invalid SQL:
INSERT INTO searchcore_text (searchcoreid, keywordtext)
VALUES ( 1584876, ‘ SOME TEXT WAS HERE.’ )
ON DUPLICATE KEY UPDATE searchcoreid = VALUES(searchcoreid), keywordtext = VALUES(keywordtext);

MySQL Error : Table ‘./mydatabase/searchcore_text’ is marked as crashed and last (automatic?) repair failed
Error Number : 144
Request Date : Wednesday, January 29th 2014 @ 07:19:29 AM
Error Date : Wednesday, January 29th 2014 @ 07:19:29 AM
IP Address : xxxxx
Username : someuser
Classname : vB_Database
MySQL Version :

As you can see, no fun indeed. My vBulletin searchcore_text table corrupted. Because our forum is large with around 1000 people on at any given time, hundreds of these emails were coming in each day. I should have repaired this sooner but needed questions answered, and I was in the middle of a systemic server crash. But anyway.

The accepted advice from vBulletin is to run a repair on the table. I could not turn off MySQL because the forum needed to stay up, this ruled out using myisamchk as you are supposed to shut down mysqld for that tool. This means I had to use REPAIR TABLE from inside mysql itself. It is thought that this is slower, but oh well. I have a lot of data, but not THAT much (about 1.2 million rows in this table). So be it.

The first step in a MYISAM repair is make a backup. If your repair fails, it can cause worse damage, so back it up, and if the first repair doesn’t work, you can try another type. To get my backup, I couldn’t run mysqlhotcopy because it simply says the table is corrupt and quits. So I was left with the only option and that is to manually copy the 3 data files to a backup location. Any MYISAM table is made up of 3 files with extensions “frm”, “MYD”, and “MYI”.

To back them up, I first made a directory to store the backup, I put it in the home directory of the account with the corrupt table:

[bash light=”true”]cp searchcore_text.* /home/myaccount/safetybackup[/bash]

My files were about 1.5GB so it took a few seconds.

Next, I wanted to run the actual repair command from its own window session using the screen command. Since I am on Windows and use Putty to connect to my server, if my session is lost and Putty closes, I don’t want to lose the process or to be able to monitor it. So type this to create a screen for running the repair:

[bash light=”true”]screen –S repair[/bash]

Note that is a capital “S” and the word “repair” is just a name I’m choosing for the screen, it can be whatever name you want. Now, even if Putty crashes or times out, if I log back in to the server, this screen will still be there with any commands I’ve run still going.
With this screen attached (verify with ‘screen –ls’, also try ‘echo $STY’), I run the repair:

mysql
use your_forum_database;
repair table searchcore_text;

After that command I get a blank cursor just sitting there, the repair is now doing its thing.

repair table

I won’t go in to all the options of using screen, but at this point I opened a new Putty terminal and logged in to the server. You can also switch screens in the same terminal if you like.

In the second terminal window I have a fresh command line, and my mysql repair is happily going along in the other screen. You can see with “screen –ls” that your repair screen is attached.

Now I type this to see the process:

mysql
SHOW FULL PROCESSLIST;

This gives me a small table where I see the “State” of the repair. It should say “Repair by sorting” for example. If it says anything else like using “keycache”, you may need to adjust your MySQL settings and try again. Keycache method could take forever, there are horror stories.

process list

In my case, I ran in to an issue where the /tmp folder filled up to capacity causing MySQL to just stop repairing. No errors, no messages, it just stops, you have to monitor your temp folder!

To fix that, I created a folder in the mysql data directory to use as a temp folder, set my.cnf variable, set “mysql” as the owner of the folder, and restarted MySQL. After that, I ran the repair again. Note that my /tmp folder was only 500MB, the repair easily needed over 1.2GB to run. With the new temp folder set, the repair operation took all of 5 minutes.

I verified the repaired table was good, then deleted my earlier backup. I also had to reset the mysql temp folder and restart the service again. I do that with this command:

/etc/init.d/mysqld restart

In a few seconds, it is restarted and rocking away! With the table repaired, there was a couple days of activity on the forum not indexed, so I did have to tell vBulletin to rebuild the search index. That itself is no small operation.

References:
http://dev.mysql.com/doc/refman/5.5/en/repair-table.html
http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_tmpdir
http://www.linux.com/learn/tutorials/285795-taking-command-of-the-terminal-with-gnu-screen-

Proper Social Network Graphic and Image Sizes

Every wonder the precise pixel dimensions of a Facebook banner or Youtube background? This infographic covers everything from Facebook profile and cover photo to app images, video previews, to Twitter profile picture to Youtube cover art. Graphic sizes in Linkedin, Pinterest, Google+ and more.

 

Visit http://www.mediabistro.com/alltwitter/social-media-image-dimensions_b44957

 

Save for reference!

Update Avast Internet Security 2014 and Can’t Access Network Shares

The latest update of Avast made a problem for me at work when it updated to the latest 2014 version. Suddenly networked computers were not able to communicate, could not ping them by IP or netBIOS name, host unreachable, timeouts etc.

After spending a couple hours changing every setting I could find in the Avast firewall, nothing would allow communications. Resetting winsock, flushing caches, DNS, ARP table, recycled the router even, nothing worked!

Finally found a solution that worked for us. The only way I could get communications back was by uninstalling and reinstalling via the latest Avast installer (not going through update channel again). After uninstalling, do turn OFF the Windows firewall as it’s possible that having it on during install triggers the problem as well. I cannot confirm this but it did happen on one workstation the reinstall did not work. After uninstalling the firewall module, shutting off Windows firewall, then reinstalling Avast firewall module the second time, it worked.

I hope this helps. Again, just try uninstalling the firewall module, turn off Windows firewall, reinstall Avast firewall module, and see if that doesn’t fix it. Let me know!

Turn a Website Into a Desktop App with Chrome

Here is a quick tip for those of you with Chrome who often use webpages and wish there was a Desktop version. Or basically, if you are annoyed with having to run a website in a tab all day, or whatever!

Anyway, Chrome has a feature to create an “Application Shortcut”. That doesn’t sound so special, until you realize what it actually does. When you create this shortcut and then open it, the website is in a special Chrome window with no menus or tabs, it’s a clean view of just the one website.

To use this feature, first open the website you want into a tab. Next click the Chrome menu, go to Tools, then “Create Application Shortcuts…”. Choose the shortcuts you want to save, and you’re done.

Create application shortcut in chrome

 

Now, find the shortcut you created and open it, you get a clean window with just the website you need.

I use this for running a Hootsuite window, or Facebook, etc.

Chrome Application Shortcut Clean

 

Hope that’s useful!

Pay Amazon Store Card with your Bank’s Bill Pay

Maybe this will help someone. I wanted to pay my Amazon Store Card through Bank of America’s bill pay system and it wanted the card’s zip code. Anyway, if you call the phone number on your card, you can say “talk to representative” during the automated system to get to a real person. I’ll save you a couple minutes.

When inserting the pay to account in your bank, name the account “Amazon Store Card-gecrb”. The rep told me to name the account with the suffix, I guess it could be important. Put in your account number and alias as usual, and the address is as follows, though you may only need the zip code:

PO Box 960013
Orlando, FL 32896-0013

 

I hope that works for you!

Use Your Bookmarks!

All websites that you use which contain sensitive or personal information, always use a trusted bookmark to navigate to them. The reason being, if you search Google or a search engine for mybank dot com, it’s possible a cyber attacker has somehow gained search engine prominence through sponsored links or other hacking, and put their own website at the top of the search engine, pretending to be mybank. A high-profile example of this was an attack against the Bitcoin exchange site mtgox.com in June 2013. The hijacker got his fake site at the top of Bing and Yahoo, so people thought they were going to mtgox but were really entering a phishing site which stole their user/pass.

Another common error is fat-fingering the URL, if you try to type common URLs and accidentally misspell it, hijackers often own misspelled domain names, like facbook or whatever. So without knowing you misspelled the domain, you enter a fake website that looks like the real deal, and they steal your user/pass when you try to log in.

Again, the safest way to browse to sensitive sites, is to have a proper bookmarks saved for them.

As a bonus tip, most browsers let you save a shortcut to the domain, so for example I have the shortcut "boa" which takes me to Bank of America. I don’t have to open my bookmarks or try to find it, I just type "boa" in the address bar and it inserts the domain. I have shortcuts like this for a couple dozen websites.

Microsoft Office Alternatives

I’ll make this quick, if you are getting tired of the licensing and cost issues of Microsoft Office, there are other fish in the sea. And these fish are free!

The latest and greatest MS Office is 2013 and then Office 365. They may well be wonderful pieces of software with excellent features, but they aren’t your only choice. In fact, despite being called “Office”, Office 365 with their permission to install on 5 PCs, is not actually for “office” people. So forget installing it in your “office” for all your employees, you can’t take advantage of those features, which also means, say goodbye to the Live Office feature, unless you buy a subscription for every one of your employees. I guess Microsoft assumes people want their massive expanse of Office software just for making recipe books and writing letters? Most people do “work” with their Office suite, so it doesn’t make sense.

There is also the regular Office 2013 version, which is just fine, and ranges in price depending on which functions you need.

Then there are the “others”, three other office suites which provide 96.72% of all the features you’ll ever need. You’ve probably heard of them. There are Apache OpenOffice, LibreOffice, and Kingsoft Office that I’ll quickly discuss.

LibreOffice

LibreOffice has been around a long time, available worldwide, in more than 30 languages, for every major operating system. It is often preloaded on Linux-based OSes, and has quite a following. With their primary install you get a writer, spreadsheet, presentation, drawing, database, and formula apps. Similar to a pro version of MS Office.

You also have the ability to install extensions which can enhance or add abilities to the entire suite, or to an individual application.

The main download is 183MB, but you can even find a 98MB portable version (requires no installation) from portableapps.com and can run the Office from a USB drive.

Visit their Features page to browse the features of each component and view screenshots: http://www.libreoffice.org/features/

 

OpenOffice

Next up we have Apache OpenOffice. It is over 20 years old and is used by millions of people. They claim they are the first software package in the world to use OASIS OpenDocument Format as its native file format. They are standards based and have few, if any, licensing limits. You can use it for personal or business, copy it, pass it to friends, anything you want.

The installer gives you a writer, spreadsheet, presentation, drawing, database, and a math app. The installer is 130MB.

Each individual program has the same look and feel, even matching your desktop operating system, and sharing the same exact tools on the toolbar between programs. For example, the same tools you might use to work on graphics are also in the writer. This makes it very easy to work in each program as you get used to the tools quickly. The programs will share the spellchecker, and even share program options so if you change a certain option in one app it will change in the rest too.

 

Kingsoft Office

This one is relatively new to me, I had never heard of it until recently. It is primarily a for-pay suite that also includes mobile versions for Android, and soon, iOS. They focus on the big three primary tools, a writer, spreadsheet, and presentation app. Their Office Suite Professional 2012 runs for $69.95 in the USA, but they also sell each individual application separately. They sell a “Standard” version as well. It seems the Pro version adds VBA scripting to Spreadsheets, and it has an interchangeable interface between a ribbon-style toolbar and a standard “old-school” toolbar.

Beside the Standard and Professional versions, they offer a free version which comes with the same three applications. There is a feature matrix which shows the free edition does not support VBA scripts, dual interfaces, online updates, macro functions, or presentation templates. Obviously if you need scripting and macros, Kingsoft may not be for you, unless you buy the Pro version.

They claim to have been around for 20 years and have a lot of experience in these applications. They claim very high compatibility with MS Office, able to read and write documents all the way back to version 97!

This comes in as the smallest suite (though it is only the three tools remember) at 39.1MB. I had to pull a screenshot from softpedia because Kingsoft only had images of their Pro ribbon interface, this is the standard toolbar interface.

 

Conclusion

This is not the place to do a side-by-side compare or try and build a compare features matrix. Everything depends on your needs. If you are moving from Office 97 or 2000, 2002, then a move to ANY version of Office, even Microsofts, is going to be a learning curve. Ask yourself if you need those drawing, database, math tools, or other options. Do you want the portable version of LibreOffice? Do you need the macros and VBA scripting?

File versions and compatibility are almost a non-issue, as Microsoft thankfully moved to open formats long ago and so sharing documents between different versions, and entirely different software suites, are not that big of a deal. And in fact you can also save documents to PDF, or whatever you need. In the very rare chance somebody can’t open your document, save a new copy for them in a different one. Using a non-Microsoft office suite does not mean you are now isolated from the rest of the world!

However you use your office suite now, ask yourself what features you’ve used in the last year, how “fancy” you’ve really ever been beyond the basic needs. Chances are good you’ve done little more than write documents and perhaps change a few fonts and colors. Or create spreadsheets with little more than a few basic math functions or tallies or bar charts. You may have never even used a slideshow, or drawn a flowchart.

It may be time to have a little fun and try something not cooked in Microsoft’s kitchen.

Do not take this article as a MS Office bash, I have nothing bad to say and their suite is certain very polished and well worn. But before you drop $99/yr on Office 365, or drop yet another $400 on Office Pro, perhaps check out the free alternatives, they may work just perfect for you, and open all your old documents with ease.

Convert Meters to Feet in Your Head

This is one of those "too much time on your hands" things, but can be helpful sometimes.

In the various IT fields, you’ll often come across measurements in meters, and some of us Yanks would rather see feet.

Your first solution is to Google it, if you type "xyz meters to feet" Google will open it’s calculator at the top of the search results.

The second solution is a quick bit of head math that really isn’t too difficult, just a few rules apply. This calc can be off by as much as a half a foot high or low, it it only supposed to get you close, not perfect!

Firstly, 1 meter = 3.28084 feet.

Rule 1:  Multiple the meters by three.

Rule 2: Add one quarter of your original meters onto the product. Because it’s .28, we are rounding down to the easier .25. We do have to account for the missing .03 though, that’s rule 4.

Rule 3: If you cannot divide evenly by four, just add another 1 if there is a remainder. For example take 19. 19 * 3 = 57. A quarter of 19 is 4 with remainder 3, just add one more because of the high remainder. So 57 + 4 + 1 = 62. The real number is 62.336. Works for me. Sometimes this does work if the remainder is small. Take 17 as an example. 4 goes in to 17 with remainder 1, but if I add another 1, I go over. 17 * 3 + 4 + 1 = 56, real number 55.7743. So we are close in a round-up sort of way, but if you’d rather round down, don’t add the extra 1 for a remainder of just 1. In fact, if you want to always be rounded down OR UP for safety’s sake, add or don’t add this remainder as needed.

Let’s see some examples before we move on to rule 4.

If you see 12 meters, you first multiple by 3 to get 36. Then you quickly estimate a quarter of 12, which happens to be 3, and add 3 on to 36, making 39. Thus 12 meters is roughly 39 feet. In fact it’s 39.3701.

If you see 15 meters, that is 15 * 3 + 3 + 1 = 49. Real answer 49.2126.

Rule 4: The last rule is this, at about 33 meters, the unaccounted for .03 adds up to 1. Remember, 1 meter is 3.28084 and we are rounding to one quarter which is .25. That missing .03 is adding up. If we take 1 / .03 we get 33.33333. In other words, at about 33 or 34 meters we’ll need to account for and extra 1 foot.

When calculating 33, 34, and 35, we have a remainder and thus are adding 1 already and this works, but at 36, we can divide evenly again by 9. 36 * 3 + 9 = 117, but the real number is actually 118.11. This is because we’ve left out too many .03s and they have now added up to an extra 1 foot. The basic fourth rule is, at about every interval of 33, throw one more on there to catch up unless there is a remainder. If you have a remainder and are already adding 1, that is one less to add with this rule.

 

The full calc is something like this: "Take meter times 3 plus meter divided by 4. If remainder then +1 (or don’t add if remainder is only 1). For every multiple of 33, +1 again except when adding 1 for remainder."

That sounds convoluted! It’s really not that hard, these rules are only to get a guestimate of the real number to within half a foot or so, it works pretty well actually. Let’s see a big number in action with rule 4:

Take 100 meters. That is 100 * 3 + 25 + 3 = 328. The extra 3 are because we’re roughly in the third multiple of 33. The real number is 328.084. That was a good guess!

However, if we had 101 meters, there will be a remainder, so instead of adding 3 and another 1 for the remainder, we’ll let the remainder cancel out one of the rule 4 additions thus we will still add just 3 instead of 4. 101 * 3 + 25 + 3 = 331. The real number is 331.365.

The next highest number that we have an even division by four is 104. This would be 104 * 3 + 26 + 3 = 341. Real answer 341.207.

 

There you have it. Play around with the concept and you’ll see it’s not too hard to multiple by three, find a quarter of your original number, and throw a quick extra 1 or 2 or 3 in there depending on remainder or multiples of 33, you’ll get the hang of it and can rely less on Google. Then again, if you are a math genius, you’d just multiple the meter by 3.28084 and be done with it. The rest of us are stuck with this.