We have all done it in the past, and probably most people that read this (will admit| or lie) to still doing it, but everybody must start making an effort to improving MySQL security in the usage on your MySQL Installation, including just on your laptop, and in presentations that people read.

I spotted a reference article on Planet MySQL this evening and without looking at the details the syntax presented typifies two basic and fundamental 101 MySQL security issues.

1. Always, always, always have a password for a MySQL account, especially for the ‘root’ user.
2. Don’t use the ‘root’ user unless you really have to. The SUPER privilege is just that SUPER, there are many things you really don’t want every person accessing to have. In a larger environment …

In case you live in the dark ages (that is, before RSS) and haven’t heard, MySQL Camp II is next week at Polytechnic University in Brooklyn, NY. Sign up and head over there, slackers!

I will be there to talk about Proven Scaling, HiveDB, DorsalSource, and much more! Send me a note if you’d like to meet up or talk about something specific. I will also have ample Proven Scaling bottle openers …

One of my favourite topics in MySQL performance talks is the ambiguous description of what size of what your transactions should be. The basic advice is:

Running InnoDB in autocommit, or with short transactions will cause many more fsync()'s which will reduce your write performance.

It seems that if I run entirely transaction-less the import speed of a test I wrote is:

real    0m31.222s
user    0m2.111s
sys     0m1.070s

real    0m30.318s
user    0m2.111s
sys     0m1.070s

real    0m31.744s
user    0m2.108s
sys     0m1.078s

If I run in transactional, committing after approx 10 queries, the time is awesomely better:

real    0m12.154s
user    0m1.771s
sys     0m0.869s

real    0m11.976s
user    0m1.773s
sys     0m0.874s

real    0m12.827s
user    0m1.768s
sys     0m0.872s

I tried hacking my code to commit even less frequently, and I can get …

I took the same table as I used for MySQL Group by Performance Tests to see how much MySQL can sort 1.000.000 rows, or rather return top 10 rows from sorted result set which is the most typical way sorting is used in practice.

I tested full table scan of the table completes in 0.22 seconds giving us about 4.5 Million of rows/sec. Obviously we can't get sorted result set faster than that.

I placed temporary sort files on tmpfs (/dev/shm) to avoid disk IO as a variable as my data set fits in memory anyway and decided to experiment with sort_buffer_size variable.

The minimum value for sort_buffer_size is 32K which gives us the following speed:

PLAIN TEXT SQL:

1. mysql> SELECT * FROM gt ORDER BY i DESC LIMIT 10;
…

In response to brad's post and daveman692's post, about Thoughts on the Social Graph.

I have posted the following:

I came home from FOOcamp with my mind buzzing with something similar. It was in the zeitgeist, I guess.

Between being annoyed at some of 6As recent actions, and annoyed that if I left LJ, I would lose a lot of valuable social network information. And then the sessions on distributed social networks, openid, …

So, like I mentioned in the Log Buffer #58 this afternoon, the MySQL blog world was abuzz during these last two weeks — as were various IRC channels — with debates and discussions about the latest news Kaj blogged about with regards to further efforts by MySQL to differentiate.

At the risk of sounding like a piece of milk toast, I would like to emphasize the point that the collective "we" — MySQL AB, the MySQL user community, and MySQL paying customers — are all in this thing together. If one piece of the collective is hurt, it affects all the others in both direct and indirect ways.

The collective "we" is currently amidst an evolutionary process: the path of …

MySQL has a unique feature whereby you can select the storage engine for each table. If you don’t know what a storage engine is, it is responsible for managing the physical data on disk - from data files and indexes to the memory buffers used to access the data and the locking needed to prevent multiple people from clobbering each other’s changes. This is both a boon and a bane for Systems Administrators and DBAs.

It’s a great feature because developers, Sys Admins and DBAs can select the engine with the properties they require. For example, the InnoDB engine supports ACID-compliant transactions, foreign keys and row-level locking. The MyISAM engine, while being lightning fast on reads and supporting concurrent inserts, does not have ACID-compliant transactions or foreign keys and implements table-level locking. Other engines are available, such as memory-based tables, clustered tables, federated tables (reference a table on server B …

Jan has a good article about finding the row matching some value in the group:

This is one illustration of group by limitations in SQL language which is not offset by any MySQL specific extensions,yet
As you can see if you want to get one row from the group which is sorted some way you have to use ugly hacks. This is because SQL does not have a notion of sorting the data within the groups and in fact ANSI SQL even forbids you to select columns which are not aggregates or part of group by because result in this case is not defined.

What would be quite helpful is to have GROUPORDER (pick the name) clause which defines which element is selected for non aggregate columns, something like

PLAIN TEXT SQL:

1. SELECT MAX(Population),  City, Country  FROM City GROUP BY Country GROUPORDER …

The 58th edition of Log Buffer, the weekly review of database blogs, has been published by Jay Pipes (a.k.a J.Pipes) of MySQL AB on Design, Develop, Discover, Define. Chen Shapira is standing by for the next edition. Publishing a Log Buffer on your own blog is a great way to introduce yourself and your blog [...]

Dave Edwards once again has given me the privilege of writing this week's Log Buffer, and oooooh it's a jam-packed one. I think there's something for everyone in this week's issue.

Buzz in the MySQL Blogosphere...

The MySQL blogosphere was alight this past week — and not just with technical articles. The recent announcement by Kaj Arnö that MySQL was making some adjustments to its Enterprise and Community Server release schedules and removing source tarballs for the Enterprise Server from its public FTP site made waves in a number of circles. On PlanetMySQL, bloggers such as Jonathan Cheyer, Jeremy Cole, Kevin …