I´ll start from the beginning:
I have a small database of my own with something like 1000 records. From an external source I have become the xml-file, which contains data I need. So I want to pick the information in the tag-of-interest (see below) for my 1000 records.
The xml-file has a lot more information (every record has an average of 60 tags) and many more rows than I need. But I have no idea of how to handle xml files, and at least a tiny idea of mysql-queries, so I thought it would be easiest to
- load the xml into a table (actually into 2 tables)
- sort out the items that belong to the years i´m interested in, and throw away the rest (because I guess the queries will be a lot faster that way)
- fetch the data I´m interested in
(By now I know it would have been faster to search the xml-file by hand, record for record…)
Since your last answer I tried to split the file into smaller parts, to see where it no longer works.
100 items 6000 rows 237 kB takes no time at all
500 items 32000 rows 1063 kB takes no time at all
5000 items 360 000 rows 12904 kB takes 80 seconds
10 000 items 600 000 rows 23375 kB takes 3000 seconds
50 000 items 3,2 million rows 111898 kB takes forever (stopped it after 2 hrs).
So it seems like things are getting heavy with >10M file sizes
Sorry, I don’t want to show the actual xml-file, but it is built like this:
<item>
<id>123</id>
<name>xxx</name>
<company>yyy</company>
<date>2010-06-21</date>
….(here comes like 50 other tags)
<tag-of-interest>zz1</tag-of-interest>
<tag-of-interest>zz2</tag-of-interest>
…(onother couple of tags)
</item>
<item>
<id>456</id>
<name>uuu</name>
<company>ddd</company>
<date>2009-06-10</date>
….(here comes like 50 other tags)
<tag-of-interest>zz2</tag-of-interest>
<tag-of-interest>zz4</tag-of-interest>
…(onother couple of tags)
</item>
The first table I´ve created to load the file into looks like this:
CREATE TABLE ‘table1’ (
‘article_id’ char(20) NOT NULL,
‘name’ varchar(200),
‘company’ varchar(100),
‘date’ DATE,
PRIMARY KEY(‘Article_id’));
I skip loading the tags I don´t need.
The tag-of-interest is loaded into another table:
CREATE TABLE ‘table2’ (
‘table2_id’ char(20) NOT NULL AUTO_INCREMENT,
‘article_id’ char(20),
‘information-of-interest’ varchar(30),
PRIMARY KEY(‘table2_id’));
Rick James Wrote:
-------------------------------------------------------
> SHOW VARIABLES LIKE '%packet%';
This gave max_allowed_packet 1 048 576
> SHOW VARIABLES LIKE 'read_b%';
This gave read_buffer_size 65 536
> Those might be worth increasing.
In the my.ini file the read_buffer_size only seems to be connected to MyISAM-tables, I use InnoDB. Should I increase it anyway?
I increased the max_allowed_packet to 16M (which is mensioned in
http://dev.mysql.com/doc/refman/5.5/en/program-variables.html)
Then tried files sized
5000 items 315 000 rows 10784 kB takes 72 seconds
7000 items 440 000 rows 15254 kB takes 190 seconds
So this didn´t seem to do much good. But the execution of the query definitely makes a non-linear increase in time needed already at these file sizes...
I´ll be glad for any ideas on how to get further with this!
> Is the XML in PRIMARY KEY order?
The columns of my table is in the same order as the tags in the xml file. Though many tags in the xml doesn´t have any column in my file. Only one column is primary key.
>
> 256M is better than the default for
> innodb_buffer_pool_size on a 3GB machine; don't
> know why it would not let you go bigger; never
> heard of that.
I succeeded to set it to 1G but not to 1,5G. I guess it is because the virtual memory is set to 1500 MB…