"create table" speed

[cainlevy]

I'm wondering if there's any way to speed up create table queries?
Besides upgrading hardware, that is. The very simplest table creation
query, "create table table1 ( field1 INT(10))" is taking about .03
seconds, which compared to other queries (large inserts at .01 seconds)
and previous experience appears inordinately long. Further, it appears
that most of that .03 seconds is some kind of overhead, since the
complexity of the create table query appears to be irrelevant.

Any ideas?

Running: Debian Woody with dotdeb packages.

[Mike A.]

Maybe it's disk access that's slowing you down. Have you tried using the
memory (HEAP) engine? It may not be acceptable for your application to
have the database created in RAM, but it may at least help you track down
the bottleneck.

--
Mike Argy
Custom Office solutions
and Windows/UNIX/OSX programs

[cainlevy]

Good idea. I just tried "CREATE TABLE t (i INT) ENGINE = MEMORY" a few
times, and it took about the same as MyISAM.

[jerry gitomer]

cainlevy wrote:

I'm wondering if there's any way to speed up create table queries?
Besides upgrading hardware, that is. The very simplest table creation
query, "create table table1 ( field1 INT(10))" is taking about .03
seconds, which compared to other queries (large inserts at .01 seconds)
and previous experience appears inordinately long. Further, it appears
that most of that .03 seconds is some kind of overhead, since the
complexity of the create table query appears to be irrelevant.

Any ideas?

Running: Debian Woody with dotdeb packages.

Why are you concerned with how long the create table takes?
This is an operation that you shouldn't be performing very
often. The lack of performance you see is probably because the
developers weren't concerned with the performance.

The overhead you are seeing is due to factors unrelated to the
size and complexity of the table. These include inserting the
table information in an internal dictionary, allocating disk
space, doing the same for any indexes and in some cases even
negotiating with the operating system and creating one file for
the table and one file for each index.

In any event if creating tables is being done often enough to
impact performance modifications to your application(s) and
perhaps even a complete redesign may be in order.

Jerry

[cainlevy]

In our application, we need to deal with a subset of our main database
as a persistent snapshot over the course of days. Creating tables,
populating them with the relevant subset of data, then after a period
of inactivity archiving those tables for later use seemed (and still
seems) like the way to go.

I was not clear in my initial post, though: this was all happening much
faster earlier. Before, the entire process (create and populate tables,
run calculations) was taking about .5 seconds. Now, just the table
creation part is taking .5 seconds.

I have some questions about the overhead you mentioned. There aren't
any indices in this example table I'm creating, and after testing a
HEAP table (based on Mike's suggestion) and discovering that the query
time didn't change at all, it appears that disk access isn't the
problem. So would that mean the overhead is all internal to MySQL?
Integrating a new table into the internal dictionary (w/o indices)?
What buffers or configuration variables should I play with?

[Bill Karwin]

cainlevy wrote:

In our application, we need to deal with a subset of our main database
as a persistent snapshot over the course of days. Creating tables,
populating them with the relevant subset of data, then after a period
of inactivity archiving those tables for later use seemed (and still
seems) like the way to go.

Another solution might be to use existing tables, populate them with
additional datasets, and after a period of inactivity, set a value in an
"archive" field to true. Or set it to some integer to indicate the
snapshot to which the subset of data belongs. Then you can access any
present or historical subset of the data by referencing the snapshot id.
e.g. "SELECT * FROM myTable WHERE snapshot_id = 327".

Remember to define your primary key for that table to a compound key,
using the snapshot_id in combination with whatever unique key you
already had.

Regards,
Bill K.

[cainlevy]

Intriguing, hadn't thought of that. We need to be able to handle
probably up to ten thousand active snapshots, with the largest table in
each snapshot at around 750 entries. Rather than use an archive flag,
we could actually save SQL inserts to a file ... that would help cut
down the database size.

The performance tradeoffs I see, then, would be the consistent overhead
of opening new tables vs. the increasing overhead of using the
snapshot_id. I'd test it, but I just can't yet justify the time
expenditure to set up an accurate performance comparison. Especially
when creating tables was so quick and painless before.

[Mike A.]

On Thu, 28 Apr 2005 16:03:33 -0700, cainlevy wrote:

Good idea. I just tried "CREATE TABLE t (i INT) ENGINE = MEMORY" a few
times, and it took about the same as MyISAM.

In the other thread, you mention that it was faster 'before.' Before
what? Did you upgrade MySQL, or move it to a different machine? I have
noticed that the 2.6.x Linux kernel is a bit slower than 2.4.x, especially
the distribution builds. You could probably compile a faster one of your
own, or switch back to 2.4.
--
Mike Argy
Custom Office solutions
and Windows/UNIX/OSX programs

[cainlevy]

Sorry I haven't responded earlier. Application speed is a problem I'm
trying to deal with in the spare moments. And actually, therein lies
the answer to your question: I don't know what the turning point was,
because I was looking elsewhere when it happened. This instance of the
application has been on the same Debian installation on the same
server, with the same 2.4 kernel. As far as upgrades go, we have been
upgrading both MySQL and PHP4 from the backports available from
dotdeb.org.

So it sounds like the best hope I have of figuring this out is to
install some older MySQL/PHP packages and run comparisons.