0

I have a series of tasks I run in MYSQL 8 every day. Actually these tasks are run from a vb.net application that interfaces with MYSQL using 8 connections at a time. These tasks are a series of complicated joins, selects and table creation. These computers are exclusively designated for its duration. I have been running them on my laptop for about 6 months.

Laptop specs: 16GB RAM DDR3, Intel i5-835OU Processor 1.7 GHZ, 4 Cores, SSD Hard Drive - Time to complete tasks 7981 seconds - average usages during task 75% CPU, 43% Mem, 100% Disk

Just recently, I purchased a cheap mini pc

Mini PC 1 specs: 16GB RAM DDR4, N100 Processor 3.4 GHZ, 4 Cores, SSD Hard Drive - Time to complete tasks 5532 seconds - average usages during task 100% CPU, 50% Mem, 81% Disk

I was so pleasantly surprised by the speed upgrade that I decided to buy their upgraded mini pc.

Mini PC 2 specs: 32GB RAM DDR5, Ryzen_7 Processor 4.75 GHZ, 8 Cores, SSD Hard Drive - Time to complete tasks 9178 seconds - average usages during task 14% CPU, 19% Mem, 46% Disk

As you can see, there was a major drop in speed with the upgraded pc. And it seems that the upgraded pc isn't really even working that hard.

My question then for you is this: Are there settings/ways that I can devote more horsepower to MySQL itself? My computer appears to have all the resources to speed up these tasks/queries, but it is not utilizing them. The whole purpose of purchasing this pc was to run these tasks more quickly. Is there a way to devote all/most of the computer's resources to MYSQL in order to boost speed?

Thanks.

9
  • Those upgrades are not important if you have slow queries because of bad indexing , bad design etc. Maybe you have not configured the variables based on your queries. We can not help you without knowing execution plan, table description , queries used Commented Apr 10 at 20:40
  • Additional DB information request, please. Need 2 SETS of data - PC 1 and PC 2 after completing the EVERY DAY TASKS OS, Version? RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post TEXT data on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions. Commented Apr 11 at 4:23
  • Post TEXT data on justpaste.it and share the links. Additional very helpful OS information from PC 1 and PC 2 includes - please, htop 1st page, if available, TERMINATE, top -b -n 1 for most active apps, top -b -n 1 -H for details on your mysql threads memory and cpu usage, ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device & core/cpu count, df -h for Used - Free space by device, df -i for inode info by device, free -h for Used - Free Mem: and Swap:, for server workload tuning analysis to provide suggestions. Commented Apr 11 at 4:27
  • Thank you for your help. Here is the Link for PC 1 jpst.it/3FZK0. Here is the link for PC 2 jpst.it/3FZIA. I am sorry but I am not sure what to do with your second set of instructions. I am running on windows.
    – Luke Krell
    Commented Apr 12 at 19:36
  • Luke, Did not know you were on Windows, would not have asked for the OS info. Analysis in process. Thank you. Will try to have suggestions for you posted in less than 48 hours. Commented Apr 13 at 13:58

3 Answers 3

0

Suggestions to consider for your PC2 my.ini [mysqld] section

log_bin=0  # from ON (1) since you are NOT replicating anywhere
innodb_io_capacity=500  # from 200 to use more available SSD IOPS
table_open_cache_instances=6  # from 16 because only 8 cores are available.
net_buffer_length=96K  # from 16K to reduce packet count in/out
innodb_buffer_pool_instances=4  # from 20 to mgmt reduce overhead

There are many more opportunities to improve performance.

1

My best answer was not in system optimization but in query optimization. I found that by restructuring my queries, I could get them to utilize more ram and less hard drive. This took advantage of the speed upgrades of PC2. In the end PC2 now completes the queries in 649 seconds; about 9 times faster than PC1 had been completing them. During the query, PC2 now runs 100% CPU, 77% Mem and 21% Disk.

Originally, I had found that creating a series of temporary tables created the best performance on my laptop. From these temporary tables I would then select the data I wanted from these temporary tables. But once I had more ram available with PC2 it greatly improved speed to simply extract the data directly from the main tables through joins.

Original Example - My structure looked like this:

CREATE TABLE temp1 AS SELECT * FROM table1 WHERE x > a AND y > b AND z > c;

CREATE TABLE temp2 AS SELECT * FROM table2 WHERE x > a AND y > b AND z > c;

CREATE TABLE temp3 AS SELECT * FROM table3 WHERE x > a AND y > b AND z > c;

INSERT INTO tableresults SELECT * FROM temp1 JOIN temp2 JOIN temp3 
WHERE temp1.x = x AND temp2.y = y AND temp3.z = z;

INSERT INTO tableresults SELECT * FROM temp1 JOIN temp2 JOIN temp3 
WHERE temp1.x = y AND temp2.y = z AND temp3.z = x;

INSERT INTO tableresults SELECT * FROM temp1 JOIN temp2 JOIN temp3 
WHERE temp1.x = z AND temp2.y = x AND temp3.z = y;

On my laptop and PC1, this was the right balance of RAM and ROM usage.

New Example - Changed my structure to look like this:

INSERT INTO tableresults SELECT * FROM table1 JOIN table2 JOIN table3 
WHERE table1.x = x AND table2.y = y AND table3.z = z;

INSERT INTO tableresults SELECT * FROM table1 JOIN table2 JOIN table3 
WHERE table1.x = y AND table2.y = z AND table3.z = x;

INSERT INTO tableresults SELECT * FROM table1 JOIN table2 JOIN table3 
WHERE table1.x = z AND table2.y = x AND table3.z = y;

With PC2 there was no good reason to write temporary tables to the hard drive when there was plenty of RAM to do the job.

So my summary of what I learned is this: breaking tables into smaller temporary tables can be very helpful when you are low on RAM. But if you have the RAM to avoid those extra steps, it is much faster to cut them out. But its a balancing act. Sometimes you want more intermediate steps and other times it is better to go direct.

0
0

Suggestions to consider for your PC1 my.ini [mysqld] section

log_bin=0  # since you are not replicating anywhere.
innodb_io_capacity=500  # from 200 to use more of available SSD IOPS 
innodb_buffer_pool_size=8G  # from 256M for about 50% available RAM
table_open_cache_instances=3  # from 16 because only 4 cores are available.
innodb_thread_concurrency=0  # from 9 to allow OS to manage limit

Many more performance tuning opportunities are available.

Please visit our website to download our free Utility Script to findfragtables.sql script to reduce data_free (free_space) in tables that are overprovisioned in this space. OPTIMIZE TABLE tbl_name; for the identified tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.