Thanks for the feedback Steve, In ref to auto inc, actually, I have! :) In the code on the blog, it shows that the tables uses a BIGINT auto increment for each row, and the performance was still good. However, using this approach introduces an unnecessary JOIN and uses more index and table space - unless I've missed something? Could you elaborate more on the UUIDs that may not be 128-bit? My google-fu failed me :/ Cal On Sun, Feb 17, 2013 at 9:11 PM, Steven Ayre <steveayre@gmail.com> wrote:

Another possibility you haven't looked at is having a table of UUIDs with a BIGINT auto_increment PK and using that as keys in other takes with a foreign key relationship (whether defined or implied). That would give you a smaller integer (hence faster select/insert/join/etc) that's still guaranteed to be unique. Though the auto_increment INSERT could then become a bottleneck (hint run a recent 5.5/5.6 MySQL version at the very least since that bottleneck while still present was very much reduced).

-Steve

On 17 February 2013 20:55, Cal Leeming [Simplicity Media Ltd] < cal.leeming@simplicitymedialtd.co.uk> wrote:

...

Hi all,

Just spent a little while comparing the performance of heavy INSERT/SELECT performance against UUID fields in a MySQL database.

Full performance breakdown and write up can be found here; http://blog.simplicitymedialtd.co.uk/?p=437

So far, the best option seems to be storing the UUID as a BINARY(16) - anyone have any thoughts on this?

Thanks

Cal

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

+1 Also as an example the 'inbound-use-callid-as-uuid' parameter I mentioned before is a Sofia profile parameter that uses the Call-ID header value from the INVITE sent by the caller as the UUID for the channel. That could be useful for example in matching up channels against SIP traces as you only need to store 1 ID not 2. I frequently see ones for example of $UUID@$ SERVER.COM - far longer than 128bits, and using characters other than 0-9A-F so that it can't be folded into bits. In this case a CHAR column becomes most flexible and the BIGINT column gives you the speed advantage of the BINARY. -Steve On 17 February 2013 22:39, Seven Du <dujinfang@gmail.com> wrote:

you can use any arbitrary string as uuid, say

originate {origination_uuid=my-uuid-that-is-not-128-bit}user/1000 ….

On Monday, February 18, 2013 at 6:13 AM, Cal Leeming [Simplicity Media Ltd] wrote:

Thanks for the feedback Steve,

In ref to auto inc, actually, I have! :) In the code on the blog, it shows that the tables uses a BIGINT auto increment for each row, and the performance was still good. However, using this approach introduces an unnecessary JOIN and uses more index and table space - unless I've missed something?

Could you elaborate more on the UUIDs that may not be 128-bit? My google-fu failed me :/

Cal

On Sun, Feb 17, 2013 at 9:11 PM, Steven Ayre <steveayre@gmail.com> wrote:

Another possibility you haven't looked at is having a table of UUIDs with a BIGINT auto_increment PK and using that as keys in other takes with a foreign key relationship (whether defined or implied). That would give you a smaller integer (hence faster select/insert/join/etc) that's still guaranteed to be unique. Though the auto_increment INSERT could then become a bottleneck (hint run a recent 5.5/5.6 MySQL version at the very least since that bottleneck while still present was very much reduced).

-Steve

On 17 February 2013 20:55, Cal Leeming [Simplicity Media Ltd] < cal.leeming@simplicitymedialtd.co.uk> wrote:

Hi all,

Just spent a little while comparing the performance of heavy INSERT/SELECT performance against UUID fields in a MySQL database.

Full performance breakdown and write up can be found here; http://blog.simplicitymedialtd.co.uk/?p=437

So far, the best option seems to be storing the UUID as a BINARY(16) - anyone have any thoughts on this?

Thanks

Cal

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

You could take a SHA1, but then might get collisions. That can happen with UUIDs too, but since it includes the MAC address and time stamp is less likely and would be more predictable. The originate_uuid may get collisions depending on what you pick, and the callid puts it outside your control entirely - in those situations you might need to handle collisions anyway. Sent from my iPad On 17 Feb 2013, at 23:47, "Cal Leeming [Simplicity Media Ltd]" <cal.leeming@simplicitymedialtd.co.uk> wrote:

Ah, yeah that changes the playing field for sure.

The problem with CHAR is the INSERT speed though, and I've seen similar issues on other builds too.. after you push past a few million rows, it really starts to slow down.. If the database was in memory, it might not be such an issue though.

In some ways it 'feels cleaner' using a separate table and then JOIN on a BIGINT autoinc column, as the thought of JOIN'ing and GROUP BY on a BINARY seems wrong.

Another option would be to take an SHA1 hash of the UUID, then convert that from hex to bin, and put that in the BINARY field.. this way you can guarantee each UUID will be the same size, not worry about changes in data schema, and get the INSERT speed benefits of the BINARY too - thoughts?

Cal

On Sun, Feb 17, 2013 at 11:15 PM, Steven Ayre <steveayre@gmail.com> wrote:

...

+1

Also as an example the 'inbound-use-callid-as-uuid' parameter I mentioned before is a Sofia profile parameter that uses the Call-ID header value from the INVITE sent by the caller as the UUID for the channel. That could be useful for example in matching up channels against SIP traces as you only need to store 1 ID not 2. I frequently see ones for example of $UUID@$SERVER.COM - far longer than 128bits, and using characters other than 0-9A-F so that it can't be folded into bits.

In this case a CHAR column becomes most flexible and the BIGINT column gives you the speed advantage of the BINARY.

-Steve

On 17 February 2013 22:39, Seven Du <dujinfang@gmail.com> wrote:

...

you can use any arbitrary string as uuid, say

originate {origination_uuid=my-uuid-that-is-not-128-bit}user/1000 ….

On Monday, February 18, 2013 at 6:13 AM, Cal Leeming [Simplicity Media Ltd] wrote:

...

Thanks for the feedback Steve,

In ref to auto inc, actually, I have! :) In the code on the blog, it shows that the tables uses a BIGINT auto increment for each row, and the performance was still good. However, using this approach introduces an unnecessary JOIN and uses more index and table space - unless I've missed something?

Could you elaborate more on the UUIDs that may not be 128-bit? My google-fu failed me :/

Cal

On Sun, Feb 17, 2013 at 9:11 PM, Steven Ayre <steveayre@gmail.com> wrote:

...

Another possibility you haven't looked at is having a table of UUIDs with a BIGINT auto_increment PK and using that as keys in other takes with a foreign key relationship (whether defined or implied). That would give you a smaller integer (hence faster select/insert/join/etc) that's still guaranteed to be unique. Though the auto_increment INSERT could then become a bottleneck (hint run a recent 5.5/5.6 MySQL version at the very least since that bottleneck while still present was very much reduced).

-Steve

On 17 February 2013 20:55, Cal Leeming [Simplicity Media Ltd] <cal.leeming@simplicitymedialtd.co.uk> wrote:

...

Hi all,

Just spent a little while comparing the performance of heavy INSERT/SELECT performance against UUID fields in a MySQL database.

Full performance breakdown and write up can be found here; http://blog.simplicitymedialtd.co.uk/?p=437

So far, the best option seems to be storing the UUID as a BINARY(16) - anyone have any thoughts on this?

Thanks

Cal

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

your very right Ken ... . however Mysql has partitions with InnoDB backend. I do the exact same thing and it helps a heap ! On 18 February 2013 11:45, Ken Rice <krice@freeswitch.org> wrote:

Why don’t you do something like look at something other than mysql like PostgreSQL, we insert millions of CDRs per day there and don’t have performance issues due to Table Partitioning, this allows us to cram well over 100million new rows/month into a table, partitioned daily along something like a call_date field (derived by dropping the time from a full time/date stamp). This also allows us to do really speedy reports without having to deal with what happens if you do something like select count(*) from cdr where call_date = “2013-02-15”; this would be a monster of a query on a table with say 90 days of CDRs at 4million CDRs/day (360million CDRs total) but by using the table partitions with a date constraint, the query planer eliminates partitions that do not match the date constraint the speeds along the query.

The caveat to this is if you don’t give it some sort of date constraint you end up looking across many partitions for the data...

On 2/17/13 6:48 PM, "Steven Ayre" <steveayre@gmail.com> wrote:

You could take a SHA1, but then might get collisions. That can happen with UUIDs too, but since it includes the MAC address and time stamp is less likely and would be more predictable.

The originate_uuid may get collisions depending on what you pick, and the callid puts it outside your control entirely - in those situations you might need to handle collisions anyway.

Sent from my iPad

On 17 Feb 2013, at 23:47, "Cal Leeming [Simplicity Media Ltd]" <cal.leeming@simplicitymedialtd.co.uk> wrote:

Ah, yeah that changes the playing field for sure.

The problem with CHAR is the INSERT speed though, and I've seen similar issues on other builds too.. after you push past a few million rows, it really starts to slow down.. If the database was in memory, it might not be such an issue though.

In some ways it 'feels cleaner' using a separate table and then JOIN on a BIGINT autoinc column, as the thought of JOIN'ing and GROUP BY on a BINARY seems wrong.

Another option would be to take an SHA1 hash of the UUID, then convert that from hex to bin, and put that in the BINARY field.. this way you can guarantee each UUID will be the same size, not worry about changes in data schema, and get the INSERT speed benefits of the BINARY too - thoughts?

Cal

On Sun, Feb 17, 2013 at 11:15 PM, Steven Ayre <steveayre@gmail.com> wrote:

+1

Also as an example the 'inbound-use-callid-as-uuid' parameter I mentioned before is a Sofia profile parameter that uses the Call-ID header value from the INVITE sent by the caller as the UUID for the channel. That could be useful for example in matching up channels against SIP traces as you only need to store 1 ID not 2. I frequently see ones for example of $UUID@$SERVER.COM <http://SERVER.COM> - far longer than 128bits, and using characters other than 0-9A-F so that it can't be folded into bits.

In this case a CHAR column becomes most flexible and the BIGINT column gives you the speed advantage of the BINARY.

-Steve

On 17 February 2013 22:39, Seven Du <dujinfang@gmail.com> wrote:

you can use any arbitrary string as uuid, say

originate {origination_uuid=my-uuid-that-is-not-128-bit}user/1000 ….

On Monday, February 18, 2013 at 6:13 AM, Cal Leeming [Simplicity Media Ltd] wrote:

Thanks for the feedback Steve,

In ref to auto inc, actually, I have! :) In the code on the blog, it shows that the tables uses a BIGINT auto increment for each row, and the performance was still good. However, using this approach introduces an unnecessary JOIN and uses more index and table space - unless I've missed something?

Could you elaborate more on the UUIDs that may not be 128-bit? My google-fu failed me :/

Cal

On Sun, Feb 17, 2013 at 9:11 PM, Steven Ayre <steveayre@gmail.com> wrote:

Another possibility you haven't looked at is having a table of UUIDs with a BIGINT auto_increment PK and using that as keys in other takes with a foreign key relationship (whether defined or implied). That would give you a smaller integer (hence faster select/insert/join/etc) that's still guaranteed to be unique. Though the auto_increment INSERT could then become a bottleneck (hint run a recent 5.5/5.6 MySQL version at the very least since that bottleneck while still present was very much reduced).

-Steve

On 17 February 2013 20:55, Cal Leeming [Simplicity Media Ltd] <cal.leeming@simplicitymedialtd.co.uk> wrote:

Hi all,

Just spent a little while comparing the performance of heavy INSERT/SELECT performance against UUID fields in a MySQL database.

Full performance breakdown and write up can be found here; http://blog.simplicitymedialtd.co.uk/?p=437

So far, the best option seems to be storing the UUID as a BINARY(16) - anyone have any thoughts on this?

Thanks

Cal

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users <http://lists.freeswitch.org/mailman/options/freeswitch-users> http://www.freeswitch.org

________________________________ _________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

-- Ken http://www.FreeSWITCH.org http://www.ClueCon.com http://www.OSTAG.org irc.freenode.net #freeswitch

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

-- Sincerely Jay

Hey Ken, Thanks for the feedback. Having used MySQL/Percona for most of my days it's quite a culture change to switch over. Percona are doing amazing things with MySQL, or as my colleague says, "dragging it kicking and screaming into the 21st century". At the same time, MariaDB is really starting to gain momentum, I had a chance to meet Monty (creator of MySQL) at a conference and listen to his talk on MariaDB vs MySQL.. he openly states that no one should be continuing to use MySQL now. MariaDB looks amazing, but I just can't convince myself to put an alpha release into production, as much as I love Monty! PostgreSQL is one of the things on my todo list, it comes heavily recommended by the Django community, and I have suffered first hand at some of the problems in MySQL that apparently wouldn't have happened in PostgreSQL. http://markmail.org/thread/oy3siq3wikhdjld5 https://code.djangoproject.com/ticket/18557 https://groups.google.com/forum/#!topic/django-developers/wvpysb-fXtc/discus... The other problem is that none of our engineers are trained in PostgreSQL (including myself), and if something catastrophic were to happen then we wouldn't be able to deal with it in-house, and this scares me. So to summarize, for us at least - I think Percona MySQL still has a lot of fight left in it, MariaDB will hopefully take over one day, and PostgreSQL is on the list of "nice to haves". Cal On Mon, Feb 18, 2013 at 1:45 AM, Ken Rice <krice@freeswitch.org> wrote:

Why don’t you do something like look at something other than mysql like PostgreSQL, we insert millions of CDRs per day there and don’t have performance issues due to Table Partitioning, this allows us to cram well over 100million new rows/month into a table, partitioned daily along something like a call_date field (derived by dropping the time from a full time/date stamp). This also allows us to do really speedy reports without having to deal with what happens if you do something like select count(*) from cdr where call_date = “2013-02-15”; this would be a monster of a query on a table with say 90 days of CDRs at 4million CDRs/day (360million CDRs total) but by using the table partitions with a date constraint, the query planer eliminates partitions that do not match the date constraint the speeds along the query.

The caveat to this is if you don’t give it some sort of date constraint you end up looking across many partitions for the data...

On 2/17/13 6:48 PM, "Steven Ayre" <steveayre@gmail.com> wrote:

You could take a SHA1, but then might get collisions. That can happen with UUIDs too, but since it includes the MAC address and time stamp is less likely and would be more predictable.

The originate_uuid may get collisions depending on what you pick, and the callid puts it outside your control entirely - in those situations you might need to handle collisions anyway.

Sent from my iPad

On 17 Feb 2013, at 23:47, "Cal Leeming [Simplicity Media Ltd]" < cal.leeming@simplicitymedialtd.co.uk> wrote:

Ah, yeah that changes the playing field for sure.

The problem with CHAR is the INSERT speed though, and I've seen similar issues on other builds too.. after you push past a few million rows, it really starts to slow down.. If the database was in memory, it might not be such an issue though.

In some ways it 'feels cleaner' using a separate table and then JOIN on a BIGINT autoinc column, as the thought of JOIN'ing and GROUP BY on a BINARY seems wrong.

Another option would be to take an SHA1 hash of the UUID, then convert that from hex to bin, and put that in the BINARY field.. this way you can guarantee each UUID will be the same size, not worry about changes in data schema, and get the INSERT speed benefits of the BINARY too - thoughts?

Cal

On Sun, Feb 17, 2013 at 11:15 PM, Steven Ayre <steveayre@gmail.com> wrote:

+1

Also as an example the 'inbound-use-callid-as-uuid' parameter I mentioned before is a Sofia profile parameter that uses the Call-ID header value from the INVITE sent by the caller as the UUID for the channel. That could be useful for example in matching up channels against SIP traces as you only need to store 1 ID not 2. I frequently see ones for example of $UUID@$SERVER.COM <http://SERVER.COM> - far longer than 128bits, and using characters other than 0-9A-F so that it can't be folded into bits.

In this case a CHAR column becomes most flexible and the BIGINT column gives you the speed advantage of the BINARY.

-Steve

On 17 February 2013 22:39, Seven Du <dujinfang@gmail.com> wrote:

you can use any arbitrary string as uuid, say

originate {origination_uuid=my-uuid-that-is-not-128-bit}user/1000 ….

On Monday, February 18, 2013 at 6:13 AM, Cal Leeming [Simplicity Media Ltd] wrote:

Thanks for the feedback Steve,

In ref to auto inc, actually, I have! :) In the code on the blog, it shows that the tables uses a BIGINT auto increment for each row, and the performance was still good. However, using this approach introduces an unnecessary JOIN and uses more index and table space - unless I've missed something?

Could you elaborate more on the UUIDs that may not be 128-bit? My google-fu failed me :/

Cal

On Sun, Feb 17, 2013 at 9:11 PM, Steven Ayre <steveayre@gmail.com> wrote:

Another possibility you haven't looked at is having a table of UUIDs with a BIGINT auto_increment PK and using that as keys in other takes with a foreign key relationship (whether defined or implied). That would give you a smaller integer (hence faster select/insert/join/etc) that's still guaranteed to be unique. Though the auto_increment INSERT could then become a bottleneck (hint run a recent 5.5/5.6 MySQL version at the very least since that bottleneck while still present was very much reduced).

-Steve

On 17 February 2013 20:55, Cal Leeming [Simplicity Media Ltd] < cal.leeming@simplicitymedialtd.co.uk> wrote:

Hi all,

Just spent a little while comparing the performance of heavy INSERT/SELECT performance against UUID fields in a MySQL database.

Full performance breakdown and write up can be found here; http://blog.simplicitymedialtd.co.uk/?p=437

So far, the best option seems to be storing the UUID as a BINARY(16) - anyone have any thoughts on this?

Thanks

Cal

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users < http://lists.freeswitch.org/mailman/options/freeswitch-users> http://www.freeswitch.org

------------------------------ _________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

-- Ken *http://www.FreeSWITCH.org http://www.ClueCon.com http://www.OSTAG.org *irc.freenode.net #freeswitch

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

In ref to auto inc, actually, I have! :) In the code on the blog, it shows that the tables uses a BIGINT auto increment for each row, and the performance was still good. However, using this approach introduces an unnecessary JOIN and uses more index and table space - unless I've missed something?

Ah, sorry - missed that somehow. :o) In that case it might be simpler to store the UUID in a single field. Comparing multiple fields I imagine would have a larger overhead than a single comparison. The extra table does uses more space, but you're shrinking the key size enough that you're going to save that much in the other tables. It'd shrink the corresponding indexes too, and that'll speed up index lookups (which'll affect both INSERT and SELECT) - both because the comparison is smaller and because you read more index entries in a single disk access. That may be enough of a benefit that the queries are faster despite the additional JOIN. That doesn't appear to be the case in your benchmarks, but it may be worth repeating with the UUID in a single field. You don't mention many details about your test setup - CPU, RAM, Disks, MySQL configuration etc. All of which could have a large impact. The version of MySQL / InnoDB will also have a large impact - there have been many improvements between versions. 5.6 has just become GA, although few people will be running it yet. The main optimization for Innodb would be to set the buffer pool to be as large as possible to minimize disk I/O. In an ideal world as large as your entire dataset (or at least what you actively use) Though of course that's not realistic in many cases. -Steve On 17 February 2013 22:13, Cal Leeming [Simplicity Media Ltd] < cal.leeming@simplicitymedialtd.co.uk> wrote:

Thanks for the feedback Steve,

In ref to auto inc, actually, I have! :) In the code on the blog, it shows that the tables uses a BIGINT auto increment for each row, and the performance was still good. However, using this approach introduces an unnecessary JOIN and uses more index and table space - unless I've missed something?

Could you elaborate more on the UUIDs that may not be 128-bit? My google-fu failed me :/

Cal

On Sun, Feb 17, 2013 at 9:11 PM, Steven Ayre <steveayre@gmail.com> wrote:

...

Another possibility you haven't looked at is having a table of UUIDs with a BIGINT auto_increment PK and using that as keys in other takes with a foreign key relationship (whether defined or implied). That would give you a smaller integer (hence faster select/insert/join/etc) that's still guaranteed to be unique. Though the auto_increment INSERT could then become a bottleneck (hint run a recent 5.5/5.6 MySQL version at the very least since that bottleneck while still present was very much reduced).

-Steve

On 17 February 2013 20:55, Cal Leeming [Simplicity Media Ltd] < cal.leeming@simplicitymedialtd.co.uk> wrote:

...

Hi all,

Just spent a little while comparing the performance of heavy INSERT/SELECT performance against UUID fields in a MySQL database.

Full performance breakdown and write up can be found here; http://blog.simplicitymedialtd.co.uk/?p=437

So far, the best option seems to be storing the UUID as a BINARY(16) - anyone have any thoughts on this?

Thanks

Cal

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

On Sun, Feb 17, 2013 at 11:02 PM, Steven Ayre <steveayre@gmail.com> wrote:

In ref to auto inc, actually, I have! :) In the code on the blog, it shows

...

that the tables uses a BIGINT auto increment for each row, and the performance was still good. However, using this approach introduces an unnecessary JOIN and uses more index and table space - unless I've missed something?

Ah, sorry - missed that somehow. :o)

I *really* suck at writing these blog articles, and they aren't always a clean/easy read. I'd planned to have some really nice graphs and styling on the page, but time was my enemy!

In that case it might be simpler to store the UUID in a single field. Comparing multiple fields I imagine would have a larger overhead than a single comparison.

The extra table does uses more space, but you're shrinking the key size enough that you're going to save that much in the other tables. It'd shrink the corresponding indexes too, and that'll speed up index lookups (which'll affect both INSERT and SELECT) - both because the comparison is smaller and because you read more index entries in a single disk access. That may be enough of a benefit that the queries are faster despite the additional JOIN.

That doesn't appear to be the case in your benchmarks, but it may be worth

repeating with the UUID in a single field.

Initially I'd planned to put all the UUIDs in a separate table and LEFT JOIN on them (hence the auto inc in the tests) I guess it also depends on how often you're referencing the UUID multiple times, the more times it's used, the more suitable a second table with a JOIN would become for saving index/table space. I agree these tests were not as extensive as they should have been, and probably should have included various JOIN statements, as well as inserting into table tables. I also hadn't tested doing a JOIN on a BINARY either - so might be worth adding this in.

You don't mention many details about your test setup - CPU, RAM, Disks, MySQL configuration etc. All of which could have a large impact. The version of MySQL / InnoDB will also have a large impact - there have been many improvements between versions. 5.6 has just become GA, although few people will be running it yet.

I was considering posting this too, but I was more focusing on the ratio difference of the numbers, rather than the numbers themselves. In this particular test though, MySQL was Percona 5.5 and, and configured with a very low innodb space / heap size, so essentially everything was being forced to disk to give a 'worst case' scenario. It would be interesting to see the difference in these numbers using different MySQL configurations for sure

The main optimization for Innodb would be to set the buffer pool to be as large as possible to minimize disk I/O. In an ideal world as large as your entire dataset (or at least what you actively use) Though of course that's not realistic in many cases.

I did a separate post about this too lol :D http://blog.simplicitymedialtd.co.uk/?p=225 These days RAM is so cheap that we tend to just throw 128GB+ RAM into the server and scale up rather than out, the speed improvement you get from having your entire InnoDB database in memory is massive. And if your chassis won't support enough RAM, then SSDs offers a considerable improvement too.

-Steve

On 17 February 2013 22:13, Cal Leeming [Simplicity Media Ltd] < cal.leeming@simplicitymedialtd.co.uk> wrote:

...

Thanks for the feedback Steve,

In ref to auto inc, actually, I have! :) In the code on the blog, it shows that the tables uses a BIGINT auto increment for each row, and the performance was still good. However, using this approach introduces an unnecessary JOIN and uses more index and table space - unless I've missed something?

Could you elaborate more on the UUIDs that may not be 128-bit? My google-fu failed me :/

Cal

On Sun, Feb 17, 2013 at 9:11 PM, Steven Ayre <steveayre@gmail.com> wrote:

...

Another possibility you haven't looked at is having a table of UUIDs with a BIGINT auto_increment PK and using that as keys in other takes with a foreign key relationship (whether defined or implied). That would give you a smaller integer (hence faster select/insert/join/etc) that's still guaranteed to be unique. Though the auto_increment INSERT could then become a bottleneck (hint run a recent 5.5/5.6 MySQL version at the very least since that bottleneck while still present was very much reduced).

-Steve

On 17 February 2013 20:55, Cal Leeming [Simplicity Media Ltd] < cal.leeming@simplicitymedialtd.co.uk> wrote:

...

Hi all,

Just spent a little while comparing the performance of heavy INSERT/SELECT performance against UUID fields in a MySQL database.

Full performance breakdown and write up can be found here; http://blog.simplicitymedialtd.co.uk/?p=437

So far, the best option seems to be storing the UUID as a BINARY(16) - anyone have any thoughts on this?

Thanks

Cal

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE: http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org

_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com

Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com

FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org