Showing entries 41 to 50 of 104
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: Tech (reset)
Keeping track of database table size changes in MySQL

I don’t know how common is this problem, but it is good to know from time to time about which tables how many storage space needed in certain time. For example, you can catch an amok running software part which writes your database full. Or, – as you will see soo – you can catch up some code what doesn’t work as excepted.

So, lets start at the beginning. You wanna to know how big are your tables, and you need to know how many data gets there day-by-day (or minute-by-minute. or whatever).

You can query information_schema.tables for table sizes, this is good, but you won’t be happy just with these results, because you won’t find any time based changes, so you have to store the data.

So first, we have to create a table to store this historical data:

CREATE TABLE `test`.`table_sizes` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE …
[Read more]
MySQL connections listing

Currently at Kinja we are in a middle of big architectural change on database servers, so I have run into a problem regarding this. Sometimes I have to check current connections on database servers, to see what schemas are in use, what servers using a given db server, or even which users are connected to database server.

Previously when I had to determine connected hosts, I just used a one-liner script in bash, what parsed through the output of netstat and listed the number of connections from given servers like this:

[root@sql-slave1 banyek]# netstat | grep mysql | awk '{print $5}' | awk -F: '{print $1}' | sort | uniq -c
      1 app01.bfc.kinja-ops.c
     83 app05.bfc.kinja-ops.c
     84 app09.bfc.kinja-ops.c
      9 dbcacti.bfc.kinja-ops
      1 nagios.bfc.kinja-ops.
      1 sql-master1.bfc.kinja

This was enough to quickly see the connected hosts, but the output wasn’t too chatty, and there are a lot of …

[Read more]
Grep trick for locating processes

Yesterday Dominis mentioned shell-foo and one cool thing came to my mind what one of my ex colleagues showed me a few years ago. (Hi Pali!)

So let’s pretend you have to locate pid of mysql process. You run the good old ps command, and grep for mysql:

[banyek@sql-master1.bfc ~]$ ps -ef | grep mysql
root      1751     1  0 Jul01 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/sql-master1.bfc.kinja.com.pid
mysql     2424  1751 88 Jul01 ?        21:59:18 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysql/mysql-error.log --open-files-limit=65535 --pid-file=/var/lib/mysql/sql-master1.bfc.kinja.com.pid --socket=/var/lib/mysql/mysql.sock
banyek    4630  4360  0 11:31 pts/0    00:00:00 grep mysql
[banyek@sql-master1.bfc ~]$

There is one small problem: the grep will find itself too, so if you want …

[Read more]
MySQL replication module for ansible

We maintain a lot of servers under Kinja, so we have to use some orchestrator software to perform some tasks on a lot of servers. The Ansbile software is used by us, because it is cool.

We have also a lot of MySQL servers (and counting!) under Kinja, so we have some tasks to perform on them, such as managing replication. Of course there are some ways to do this, for example using multiplexed terminals, or run ansibile shell commands what performs mysql queries (e.g. ansible mysql-master1 -m shell -a “mysql -e “SOME SQL QUERY HERE”) but it is not too comfy, and needs a lot of manual work.

So, there is a way to make it easier for us, and that’s why I made a mysql_replication module for ansible. (And I made a pull request for that on GitHub, so I hope it will be merged soon to ‘official’ branch)

The mysql_replication module helps you to

[Read more]
Free up space on mysql servers

First, I have to tell you, that the “fragmentation” is not the best word what I should use, but that was the closest to that what I wanted to say. The base of the fragmentation checker script is Peter Zaitchev’s 2008 article about Information_schema, I used that query to get that results what I needed.

Some background: a few weeks ago we got some free space related problems (well, they weren’t a real problem, but they could lead onto one, and we had to act fast.) The innodb is working on a bit strange way, it is not like to free up space what is deleted before, and because of this, the datafiles will grow to the end of the world. The first thing what we could do to reclaim free space is to use

innodb_file_per_table

configuration option, what will split up the database to a lot …

[Read more]
From MySQL to MailChimp via CSV

Don't you hate disclaimers? I do, but before I do anything else, I must ask that you don't use the techniques below unless you are emailing responsibly.

Today I needed to pull email addresses for people who had signed up to a thing out of MySQL and into MailChimp so that I could actually email them about the thing. MySQL actually has a very cute feature for exporting the results of an SQL query as a CSV file, which I had to look up to remember how to do it. It goes something like this:

My original query*

select u.username, u.email, a.account_name
from users u
inner join accounts a on a.user_id = u.id
where a.status_id = 2;

To write this to a file, we use MySQL's SELECT ... INTO OUTFILE feature, so my query suddenly looks like this:

select u.username, u.email, a.account_name
into outfile '/tmp/users-extract.csv'
from users u
inner join …
[Read more]
From MySQL to MailChimp via CSV

Don't you hate disclaimers? I do, but before I do anything else, I must ask that you don't use the techniques below unless you are emailing responsibly.

Today I needed to pull email addresses for people who had signed up to a thing out of MySQL and into MailChimp so that I could actually email them about the thing. MySQL actually has a very cute feature for exporting the results of an SQL query as a CSV file, which I had to look up to remember how to do it. It goes something like this:

My original query*

select u.username, u.email, a.account_name
from users u
inner join accounts a on a.user_id = u.id
where a.status_id = 2;

To write this to a file, we use MySQL's SELECT ... INTO OUTFILE feature, so my query suddenly looks like this:

select u.username, u.email, a.account_name
into outfile '/tmp/users-extract.csv'
from users u
inner join …
[Read more]
Handling Database Failover at Craigslist

There has been some interesting discussion on-line recently about how to handle database (meaning MySQL, but really it applies to other systems too) failover. The discussion that I’ve followed so far, in order, is:

[Read more]
SQL JOINing a Table to Itself

Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn't matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you've seen it, it's really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I'd capture it here in case it's helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here's the staff table from the database (today's imaginary data isn't particularly imaginative, sorry):

mysql> select * from staff;
+----+------------+-----------+------------+
| id | first_name | last_name | manager_id |
+----+------------+-----------+------------+
|  1 | Hattie     | Hopkins   |          4 |
|  2 | Henry      | Hopkins   |          4 |
|  3 | Harry      | …
[Read more]
SQL JOINing a Table to Itself

Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn't matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you've seen it, it's really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I'd capture it here in case it's helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here's the staff table from the database (today's imaginary data isn't particularly imaginative, sorry):

mysql> select * from staff;
+----+------------+-----------+------------+
| id | first_name | last_name | manager_id |
+----+------------+-----------+------------+
|  1 | Hattie     | Hopkins   |          4 |
|  2 | Henry      | Hopkins   |          4 |
|  3 | Harry      | …
[Read more]
Showing entries 41 to 50 of 104
« 10 Newer Entries | 10 Older Entries »