Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQLyog Timing Out. How can I change the settings?

Posted on 2014-10-09
11
Medium Priority
?
564 Views
Last Modified: 2014-10-14
I'm using MySQLyog as an interface. I need to add an index to a table that's very large. I understand that in order to do this, the system is making a temporary copy of the table and that' s going to take some time given the amount of data that I've got. But regardless of how much time it takes, I'm confident this is a good and necessary step.

So, I run this command: "ALTER TABLE myable ADD INDEX ( posted_day );"

...and I get this error: "Error Code : 1878 Temporary file write failure. (524429 ms taken)"

I'm thinking this is because of the 250,000,000 rows I've got in the table.

How can I change my connection times so it doesn't timeout?

Screenshot of the interface is attached.
myqslyog.jpg
0
Comment
Question by:brucegust
  • 6
  • 4
11 Comments
 
LVL 7

Expert Comment

by:Stampel
ID: 40370666
I believe its possible that you are hiting a free space problem or a size limit.
Can you give more info on the table  like table size and number of rows ?
0
 
LVL 7

Expert Comment

by:Stampel
ID: 40370669
Oh sorry  250,000,000 rows .... definately a space problem.
Can you provide the size of the table and the space left on device ?
0
 

Author Comment

by:brucegust
ID: 40370694
Hey, Stampel!

Size of the table is 36538 and I'm not sure about the space left on the device, although I want to believe that it's pretty spacious. I'm not sure how to figure out how much space I have left on the device.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 7

Expert Comment

by:Stampel
ID: 40370709
36538 ? is that Megabytes ?
What is the Operating System ? Linux ? Windows ?
Do you have access to a shell or a console ?
0
 

Author Comment

by:brucegust
ID: 40370753
That is MB and the Operating System is Windows. As far as the shell is concerned, no. I'm a contractor and I might be able to access it, but I was hoping to be able to do what needed to be done with the tools that were immediately at my disposal.
0
 
LVL 7

Expert Comment

by:Stampel
ID: 40370779
I understand.
I believe you do not have the required 36 Gigabytes free that are required to perform this operation.
You may check with them if there is sufficient free space.
0
 

Author Comment

by:brucegust
ID: 40370793
I'll do it!

Let me ask you this: Let's say they come back and say you've got plenty of room. What are my options at that point? If this is a situation where it's simply timing out because of a setting that can be adjusted, what would that setting be and how do I access it?
0
 
LVL 7

Expert Comment

by:Stampel
ID: 40370832
Ok if asking this is difficult, i propose that we try to verify you do not have sufficient space to do this.
For exemple try to make a simple copy of this table.
Then we will try to findout how much temporary space you can use before it fails.
With this error, it is not a timeout problem :)
0
 
LVL 7

Accepted Solution

by:
Stampel earned 2000 total points
ID: 40370852
mysql> CREATE TEMPORARY TABLE foobar as (select * from liste);
Query OK, 1867635 rows affected (1 min 56.83 sec)
Records: 1867635  Duplicates: 0  Warnings: 0

Then play with "limit" to findout .. like this ....

mysql> CREATE TEMPORARY TABLE foobar2 as (select * from liste limit 1000);
Query OK, 1000 rows affected (0.42 sec)
Records: 1000  Duplicates: 0  Warnings: 0
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40371920
I'm going to make a slightly different suggestion.  Break the table up into several tables.  You may want to consult a DBA or Information Architect about the process, but the general strategy would go something like this.

1. Determine what data you will receive in the requests from your clients
2. Determine what data you will provide in response to the requests
3. Configure your table structure so that you will need to query the minimum number of tables to get from request to response.
4. Create a table organization that will let you know in advance (before starting any of the queries) which tables will need to be queried, based on the request data.

I would also add that any table with 250,000,000 rows is a different kind of beast from what we usually see in PHP + MySQL applications; this is into the scale of Facebook.  They get acceptable performance for data sets of this size by having rooms full of servers.  You might want to take this issue to the computer science department of a nearby college or university and get a grad student or teaching assistant to put this problem to their classes.  You'll probably get some innovative ideas about how to organize the data.  The reason that I think you need professional help with this is because of the data size.  If you break the big table into smaller tables with 250,000 rows each, you've got 1,000 tables to deal with.  If you break the big table into smaller tables with 1,000,000 rows each, you still have 250 tables to deal with.

So far, it seems we have been a little like the blind men with the elephant - looking at parts of the problem without seeing the big picture.  I've got a sense that the big picture may now need some serious attention in the form of creative thinking about information architecture!
0
 

Author Closing Comment

by:brucegust
ID: 40380569
Was able to increase size of tmp directory and, although it took a while, I was able to install all my indexes.

Rock on!
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month12 days, 18 hours left to enroll

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question