October 29, 2011
by eric
0 comments

how to monitor database usage

How To Monitor Database Usage

I recently did some mysql queries that took many many hours to complete, so I had to break out the man pages and figure out how to see what is going on inside… At least, I had to see what queries were being made and figure out how to monitor database usage. So, I figured out that mysqladmin has a command called processlist which displays a table of user, host, db, command, running time, state (very helpful) and info (very very helpful). This is what the output looked like from the command…

<br />
+------+------+-----------------+----------------+---------+-------+-------------------+--------------------------------------------------------------------+<br />
| Id   | User | Host            | db             | Command | Time  | State             | Info                                                               |<br />
+------+------+-----------------+----------------+---------+-------+-------------------+--------------------------------------------------------------------+<br />
| 2140 | user1  | localhost       | housesdb            | Sleep   | 27496 |                   |                                                                    |<br />
| 2143 | rt   | localhost:36931 | mydb | Sleep   | 27086 |                   |                                                                    |<br />
| 2158 | rt   | localhost:42456 | mydb | Sleep   | 3136  |                   |                                                                    |<br />
| 2179 | rt   | localhost:42569 | mydb | Sleep   | 19662 |                   |                                                                    |<br />
| 2188 | rt   | localhost:34695 | mydb | Sleep   | 17232 |                   |                                                                    |<br />
| 2249 | rt   | localhost:47419 | mydb | Sleep   | 1826  |                   |                                                                    |<br />
| 2250 | rt   | localhost:47420 | mydb | Sleep   | 7781  |                   |                                                                    |<br />
| 2286 | user1  | localhost       | housesdb            | Sleep   | 5574  |                   |                                                                    |<br />
| 2332 | rt   | localhost:45332 | mydb | Sleep   | 4071  |                   |                                                                    |<br />
| 2346 | root | localhost       | musicapi       | Sleep   | 3317  |                   |                                                                    |<br />
| 2348 | root | localhost       | musicapi       | Query   | 121   | copy to tmp table | alter table tracks add column (total_votes int not null default 0) |<br />
| 3180 | root | localhost       |                | Query   | 0     |                   | show processlist                                                   |<br />
+------+------+-----------------+----------------+---------+-------+-------------------+--------------------------------------------------------------------+</p>
<p>

How to monitor database usage continuously

This was super helpful for me, I think that it is annoying have to repeatedly run the command to monitor database usage, so I decided to tie it together with unix’s watch command. This worked really well.

Here is the command
mysqladmin -u root –password=”xxxxxx” processlist

Here is the command within watch
watch –interval=5 ‘mysqladmin -u root –password=”xxxxxx” processlist’

Note: I am interested in other ways for monitoring the database usage, and if you have any methods, please post them below.

How to monitor database usage remotely across a network

If you are working remotely, then you can just run this watch command in a screen. If you aren’t familiar with screen, it is like a shell program that can keep running while you are away, and you can log in and check from anywhere. The way that I use it for something like this is to ssh in to the remote server and type ‘screen’. I like to use a couple of windows, so then I type ‘^ac’ and now I can see my windows by typing ‘^a”‘. In one screen window I start my mysql command. In the other I start my watch command. Now I can disconnect ‘^ad’ and then log in later and check progress. The way to log in later is to type ‘screen -R’.

Hopefully this can help you get started on how to monitor database usage.

October 29, 2011
by eric
1 Comment

how to do a fast mysql update

I don’t know if I have mentioned, but for me, when I am looking for a way to do something technical, I am most interested in looking at examples of other people’s code. I am not so much into getting full explanations of how and why ( I can figure that part out on my own ), but I really just need to see some code. So, here is some code for doing a fast update to a large mysql table.

I have a table to update that has over 22 million rows in it. When I added a new int field to that table it took 4 hours to complete. However, when I had to do an update to that table to set the values for some of the rows, I ran the following query and it only took like 30 or 40 minutes…

This is an interesting update, as it joins 2 big tables together, and then updates one of the fields from one of those tables with an aggregate that came from a third very big table.


update bigtable as t1 join anotherbigtable as t2 on (t1.common_key_id=t2.common_key_id)
   set t1.field_to_set = (
      select sum(t3.field_to_aggregate)
        from athirdbigtable t3
       where t3.another_common_key_id=t1.another_common_key_id
)
 where t2.valid_source_id in (6,4,1)
;

Obviously I was trimming out rows by using the valid_source_id check, but that is the point. Doing an update like this can be pretty fast, if you aren’t updating each row.

Big win for me.

October 26, 2011
by eric
0 comments

Dealing with: netbeans unrecognized project / missing plug-in

I have a project using the netbeans matissee / gui builder plugin. I recently reinstalled netbeans and had to reopen my cvs (yes, old school rcs I know) netbeans project. After struggling through trying to figure out how to get netbeans to open the project, I thought that I would quickly drop this post.

For a long time, the only thing that I could get netbeans to do was to say “unrecognized project; missing plug-in?” when trying to open my project. I finally figured out how to get rid of that annoying (and scary) message.

I “think” all that I did was open some of the files from my project including the matissee / gui builder files. Then I closed the files and then tried to open the project.

This time the project tool recognized my project!

Thankfully I don’t have to re-add all the files to a new project, which was my last resort.

Crawlicious

tools for web business