Solved

MySQLyog Timing Out. How can I change the settings?

Posted on 2014-10-09
11
472 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 500 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 109

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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